on 01-21-2015 10:22 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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.
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.
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
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.
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
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.
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.
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.
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.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.