Solved

Show sum on Maxium Date

Posted on 2008-10-24
10
217 Views
Last Modified: 2011-10-19
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.


0
Comment
Question by:phle18
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 22797081
Try this idea

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
0
 

Author Comment

by:phle18
ID: 22797432
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22798323
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:phle18
ID: 22798492
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22798700
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
0
 

Author Comment

by:phle18
ID: 22798978
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22799088
You should be able to put the sum back in.

mlmcc
0
 

Accepted Solution

by:
phle18 earned 0 total points
ID: 22799350
Sorry to be be so confusing but I never took it out in the first place. I just does not appear when I run the report but the sum is still there when you view the report in design mode.

Is there another way to just show the maxium sum?

I really appreciate all your help with this.
untitled.JPG
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22799478
Is group footer 2 set for conditional suppress?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22799881
What is the solutiion?

mlmcc
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SAP Crystal Report can't save my custom paper size 12 70
Ignore parameter if no value entered 22 87
Conditional Text in Crystal 4 59
Crystal Report - Charts 4 31
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question