phle18
asked on
Show sum on Maxium Date
I am using Crystal X and I have two tables {agedar} and {client} and trying to show a sum of payments made on the latest date for a specific client.
What I did so far was create two groups based on {client.client#} and {agedar.lastpaydate} and in the detail line put in {client.client#}, {agedar.lastpaydate} and {agedar.amount} with a sum of {agedar.amount} in the {agedar.lastpaydate} group footer. So currently the reports looks something likes this.
Header Line Client # Last Pay Date Amount
Detail Line 00155 10/5/2008 100.00
Detail Line 00155 10/5/2008 90.00
Footer 10/5/2008 190.00
Detail Line 00155 10/8/2008 75.00
Detail Line 00155 10/8/2008 10.00
Footer Line 10/8/2008 85.00
Detail Line 00155 10/10/2008 99.00
Detail Line 00155 10/10/2008 10.00
Footer 10/10/2008 109.00
How do I make it so the report only shows the maxium date {agedar.lastpaydate} entries? Many thanks in advance to all you experts on Crystal X.
What I did so far was create two groups based on {client.client#} and {agedar.lastpaydate} and in the detail line put in {client.client#}, {agedar.lastpaydate} and {agedar.amount} with a sum of {agedar.amount} in the {agedar.lastpaydate} group footer. So currently the reports looks something likes this.
Header Line Client # Last Pay Date Amount
Detail Line 00155 10/5/2008 100.00
Detail Line 00155 10/5/2008 90.00
Footer 10/5/2008 190.00
Detail Line 00155 10/8/2008 75.00
Detail Line 00155 10/8/2008 10.00
Footer Line 10/8/2008 85.00
Detail Line 00155 10/10/2008 99.00
Detail Line 00155 10/10/2008 10.00
Footer 10/10/2008 109.00
How do I make it so the report only shows the maxium date {agedar.lastpaydate} entries? Many thanks in advance to all you experts on Crystal X.
ASKER
Hi ml,
Thanks for helping but when I follow your directions it looks like when I run the report it's only pulling in the first or minimum date as opposed to latest or maxium date.
Thanks for helping but when I follow your directions it looks like when I run the report it's only pulling in the first or minimum date as opposed to latest or maxium date.
Did you set the sort order for the date group to descending.
You are correct, it will only get the records for the first date it finds. By sorting descending, the latest dated record is found first.
mlmcc
You are correct, it will only get the records for the first date it finds. By sorting descending, the latest dated record is found first.
mlmcc
ASKER
When I click on "Group Sort Expert" and then select the date group, there is no option to sort by date, it will only let me sort by sum of the payment.
untitled.JPG
untitled.JPG
Not in the GROUP SORT EXPERT use the group expert
or from the report
Right click the GROUP HEAER in the left margin
Click CHANGE GROUP
Choose sort order DESCENDING
mlmcc
or from the report
Right click the GROUP HEAER in the left margin
Click CHANGE GROUP
Choose sort order DESCENDING
mlmcc
ASKER
When I changed the the sort from ascending to descending, it went from a sum of the earlest payments to, the Sum disappearing but it does list all the payments individually which are the latest payment dates.
untitled.JPG
untitled.JPG
You should be able to put the sum back in.
mlmcc
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is group footer 2 set for conditional suppress?
mlmcc
mlmcc
What is the solutiion?
mlmcc
mlmcc
In the client group header add a formula
WhilePrintingRecords;
Global BooleanVar SuppressDateGroup;
SuppressDateGroup := False;
''
Change the date grpoup to sort descending
In the Date Group Footer add a formula
WhilePrintingRecords;
Global BooleanVar SuppressDateGroup;
SuppressDateGroup := True;
''
Right click the date group header in the left margin
Click SECTIION EXPERT
Click the formula button next to SUPPRESS
WhilePrintingRecords;
Global BooleanVar SuppressDateGroup;
SuppressDateGroup
Repeat for the DETAIL and DATE GROUP FOOTER
mlmcc