cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal report 2011 - Suppress formula?

Former Member
0 Kudos

I'm not new to Crystal reports 2011, but I am new to this forum. Excuse me if my question is already answered in another discussion thread, but I've searched the forum, but could not find an answer easily.

In my report I need to total the amount per day per security. If one of the totals per day have a negative amount, my report should show all dates for that security.

To make it more clear to you I've put in two examples

Example 1:
From date      PfGrp        Por         Security      ISIN          Custody     Amount
15-1-2015      aaa           por_a      Sec01        123456      Cust01      5000
15-1-2015      aaa           por_b      Sec01        123456      Cust02      -6000
16-1-2015      aaa           por_a      Sec01        123456      Cust01       5000
16-1-2015      aaa           por_b      Sec01        123456      Cust02       1000
17-1-2015      aaa           por_a      Sec01        123456      Cust01       2000
17-1-2015      aaa           por_b      Sec01        123456      Cust02       1000

Expected output:

From date      PfGrp        Por         Security      ISIN          Custody     Amount

15-1-2015      aaa                          Sec01        123456                         -1000
16-1-2015      aaa                          Sec01        123456                          6000
17-1-2015      aaa                          Sec01        123456                          3000
Output expected because the total amount on 15-1-2015 is negative. Because of this negative total amount the other dates have to be shown as well.(although they are positive)

Example 2:
From date      PfGrp        Por         Security      ISIN          Custody     Amount
15-1-2015      aaa           por_a      Sec01        123456      Cust01       5000
15-1-2015      aaa           por_b      Sec01        123456      Cust02       6000
16-1-2015      aaa           por_a      Sec01        123456      Cust01       5000
16-1-2015      aaa           por_b      Sec01        123456      Cust02       1000
17-1-2015      aaa           por_a      Sec01        123456      Cust01       2000
17-1-2015      aaa           por_b      Sec01        123456      Cust02      -1000
No output expected because all the totals per day are positive.

Regards, Peter.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

I see a negative amount on 17-1-2015 in your second example - shouldn't the report show all dates based on your explanation from the first example?

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thanks for your quick response. That is correct, but the total on the 17-1-2015 is positive, so no records to be displayed for the second example.

Peter.

abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

Try this please:

1) Save the existing report with a new name. You should now have two copies of the same report - let's call them A and B.

2) Open Report A.

3) Insert a Group on the Security Field first and then on the Date field. Set the Date Field to 'Print for Each Day'.

4) Create a formula (@Groups) with this code and place this on Group Header #2:

WhilePrintingRecords;

stringvar groups;

If Sum ({Amount}, {Date}) < 0 then

(

    If Instr(groups,{Security}) = 0 then   

        groups := groups + {Security} + ",";   

);

groups;

5) Suppress all sections of this report except the Report Footer.

6) Go ahead and Insert a Subreport. In the Insert Subreport dialog box, browse to the Report B and place it on the Report Footer.

7) Edit the Subreport so that it has the same groups as the Main Report (or Report A)

😎 Right-click the Subreport > Change Subreport Links > Move the @Groups formula to the Pane on the right > Uncheck the option 'Select data in Subreport based on Field' > Click OK.

9) Get inside the Subreport > Click the Report Option > Selection Formulas > Record and use this code:

Not({Security} IN Split({?Pm-@Groups},","))

IF you have some existing selection formula in there, then you would need to add this condition with an AND clause.

Hope this helps.

The reason we have to add a Subreport is because you're trying to suppress a higher group based on a summary that appears on a lower group.

If you're reporting off of tables, you should be able to use a SQL Expression Field to display the summary for Group #2 and use this summary to suppress the higher section based on another logic!

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thanks for your answer and explanation. Unfortunately this does not do the trick. The outcome I get is a list with only the last date from the period for every security listed. I do not see all dates from the whole period I'm querying per security.
This is what I see:
Date                 PfGrp      Security     ISIN      Nominal
27-1-2015        aaa          Sec01       1234      25250
27-1-2015        aaa          Sec02       1235      20000
27-1-2015        aaa          Sec03       1236      -8000
27-1-2015        aaa          Sec04       1237      -8000
27-1-2015        aaa          Sec05       1238      -8000

Regards, Peter.


abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

Attached is a sample report.

I have unsuppressed the sections on the Main Report so that you can see the two Groups (Sec1 and Sec2).

Security 1 has a negative summary amount on one of the dates whereas Security 2 doesn't have a negative summary amount.

The Subreport at the bottom correctly displays records and all dates for Security 2 alone.

Hope this helps.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

I do not see your report attached to your answer. Is it somewhere else or do I look at the wrong place?

Regards, Peter.

former_member183750
Active Contributor
0 Kudos

Hi Peter

You should be seeing this in the reply from Abhilash:

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

Former Member
0 Kudos

Hi Ludek,

All I see is this:

I've checked my settings in my profile, but cannot find anything related to sharing files of pictures. I did not make any changes in my profile at all.

My default browser is Internet Explorer 10. I've also tried it in Google Chrome, this gives the same result.

Any other ideas?

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

I've rettached the report with this reply.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Briljant.

I was able to open your example, it is working very well and exactly the data I want to see in my report.

The reason I could not see your attachment in the first place was because I hit the communications button to go into this thread. Via the communications one cannot see the attachments.

Again thanks a lot for your help.

Regards, Peter.

Former Member
0 Kudos

Hello Abhilash,

is there a special reason why you have the subreport in?

if I run the report and there are no negative amounts I see only '0' values, but these values are text. Is there a way to suppress these?

Again many thanks,

Peter.


abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

The reason we need a Subreport, like I said earlier, is because "you're trying to suppress a higher group based on a summary that appears on a lower group."

As far as I remember, you only want to display records (or groups) that have a negative summary amount, correct? If there isn't a negative summary, you wanted a blank report.

-Abhilash

Former Member
0 Kudos

thanks abliash

Former Member
0 Kudos

yes that is correct. I will add the subreport to my initial report then and that will solve the wrong load of records I see right now.

Again thanks for your quick answer.

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

Glad I could help! Please don't forget to close this thread.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,


I was busy with other stuff and could not spend time on my report. Now I did have time and tried to 'reproduce' your report. I've checked everything I could think of, but whenever I tried to run the report and do preview on the screen I get an error message saying: ;There must be a group that matches this field'.

When I check the underlying formula (Groups) where it is referring to, I do see the group. I cannot find the problem. I've attached my report. Can you take a look at it?


Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

I get an error on opening the file.

Could you re-attach please.

-Abhilash

Former Member
0 Kudos

Attachment as .TXT file.

abhilash_kumar
Active Contributor
0 Kudos

I noticed that the Date field on the main report is grouped 'For each day' whereas the group inside the Subreport is set to 'For Each Week' (Group Expert > Highlight the Date Field > Options).

Make sure both these groups are setup the same way.

You would also need to modify the '@groups' formula to:

If Sum ({A_PORCALCS.BAL_NOMINAL_NUMBER}, {A_PORCALCS.FROM_DATE}, "daily") < 0 then


Notice I added the word 'daily' as the date is set to print 'For each day' (this is for the Main Report's code).


You would need to add this text to the Subreport's code as well and set the group to print 'For each day'.


-Abhilash

Former Member
0 Kudos

That helped me one step further, at least I get results on my screen.

All I need to see now is the result of the subreport, but when I suppress all group headers/footers and details in the main report and press the preview button an warning pops up: "Failed to retrieve data from the database" and after that one: "Cannot determine the queries necessary to get data for this report. Details: An invalid range operator has been encountered."

I do not see these warnings when I show one of the group headers/footers or details.

When I do the same in your report, I don't see these warnings either.

Might this have to do with the amount of data I'm querying?

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

Are the results correct when you don't suppress any sections?

Would you be able to send the report with saved data with all sections unsuppressed?

-Abhilash

Former Member
0 Kudos


Hi Abhilash,

Attached is my report including the data. Both the main report as well as the sub-report is open, so nothing is hidden yet.

When I checked the data I still see data in the report that I do not want to see.

I expect these Sec short names to appear on the report:

DBR2.5-0744

NETHER1.25-0118F

UKTI.125000-1119

The others should not appear on the report:

NETHER1.25-0118

NETHER1.25-0118A

NETHER1.25-0118B

NETHER1.25-0118C

NETHER1.25-0118D

NETHER1.25-0118E

UKT4-0916

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

Try modifying the last line of the Record Selection Formula inside the Subreport to:

and {A_PORCALCS.SEC_SHORT_NAME} IN Split({?Pm-@Groups},",")


Then, suppress all sections of the Main Report except the section that holds the Subreport.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

You have already been of great help with my question and the report works when there are 'negative' records. My problem is that when I do not have 'negative' records I get error messages (shown in the attached Word document).

Is there a possibility to suppress these error messages and display a text like: 'No records found'

Thanks in advance.

Peter.

Former Member
0 Kudos

Hi Abhilash, did you have time to look at my earlier question on this topic?

Many thanks again,

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

Hi Peter,

What is the datasource of the report?

-Abhilash

Former Member
0 Kudos

Hi Abhilash, I'm not sure if I understand what you mean with this. The data source for this report is an Oracle view.

Peter.

abhilash_kumar
Active Contributor
0 Kudos

So, the report is against an Oracle View.

Would you post this as a separate discussion please?

P.S: There isn't a way to suppress one error message and have another one pop-up.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

This is still the same report you helped me with earlier, so what is the reason to register a new question on the forum?

The point is that this report checks for 'short positions' and if the report does not show a short position, it will be empty which is fine and will mostly be the case, but the report shows these errors. When there is a short position, this  will have to be shown on the report.

Regards, Peter.

abhilash_kumar
Active Contributor
0 Kudos

The topic of discussion now vs the topic of discussion when you first posted the thread are different. Hence, a new discussion is always preferred.

-Abhilash

Answers (0)