Print Page | Close Window

Only display three most recent records

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20022
Printed Date: 02 May 2024 at 11:47pm


Topic: Only display three most recent records
Posted By: Firegirl
Subject: Only display three most recent records
Date Posted: 16 Sep 2013 at 11:02am

I have had a new request from a user who needs a report to only display the last three records (payments).  It is fine if the report pulls in all payments but I cannot find a way to only display the most recent three.  Has anyone encountered a situation like this or can provide feedback?  I am using Crystal X.

 
Thank you!


Replies:
Posted By: praveeng
Date Posted: 17 Sep 2013 at 1:21am

Hi,

Is there any sorting order on a field in the report?

If you want to show the last Three payments in the report then,
sort(ASC/DESc as per your requirement) the report based on Payment field..
then in Right click on the section, then select Section expert.
In the Suppress condition write the logic as below..

IF recordnumber >3 then True,

HTH.

--Praveen G



-------------
Praveen Guntuka,
praveen_guntuka@yahoo.com

Posted By: Firegirl
Date Posted: 17 Sep 2013 at 4:37am
Wow!  That was impressive.  Thank you!  It took my list of 20 and suppressed it to 3.  Now do you have any idea how I can still get it to display payments for claims with less than 3 payments but only give me a max of 3? I have tried IF recordnumber >3 then True else false but that doesn't seem to work. 
 
Thank you again! 

Posted By: praveeng
Date Posted: 17 Sep 2013 at 8:23am
Hi Firegirl,
 
Are you want to show the top 3 Payments and Payment for Claims in same report??
or Are you looking for other requirement?
If possible could you post your requirement with some examples so that I can understand very easily..
 
--Praveen G


-------------
Praveen Guntuka,
praveen_guntuka@yahoo.com

Posted By: Firegirl
Date Posted: 17 Sep 2013 at 9:25am

I have grouped the report by claim (based on prgram  prompt). 

For Claim XXXX1
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX2
 payment table. payment activityxx1
 payment table. payment activityxx2
 
For Claim XXXX3
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX4
 payment table. payment activityxx1
 
I am looking for up to, the 3 most recent payments for any and each claim with payments.  Does this make sense or did I make it worse?
 
Thank you!
 

Posted By: praveeng
Date Posted: 17 Sep 2013 at 10:20am

Hi,

You can use Group Sort Expert option to show top N values. To enable Group sort option you need atleast one summary field in the group.
Place sum/count in the Group footer.
Goto Report > Group Sort Expert
Give 3 in the Where N is box.
Then Click on OK

Or you can use the below method:


Create a Running Total based on For Claim xxx1 database field use the below options
Field to summarize : select the Cliamxx1 database field.
Type or summary : Count
Evaluate:
select On change of group : select Cliamxx1 group.

Then Place the Running total field in the Group header.
Right click on the section and select Section expert
write the below logic in Group header, Details and Group Footer suppress condtion:
 If Running_Total >3 then True.

 
--Praveen G


-------------
Praveen Guntuka,
praveen_guntuka@yahoo.com

Posted By: Firegirl
Date Posted: 17 Sep 2013 at 11:06am
This is a very interesting approach, but unfortunately it is only giving me the top three claims with the largest count of payments, not all groups (claims) with the three (or less) recent payments.  So instead of:
For Claim XXXX1
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX2
 payment table. payment activityxx1
 payment table. payment activityxx2
 
For Claim XXXX3
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX4
 payment table. payment activityxx1
 
I am getting:
 
For Claim XXXX1
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX3
 payment table. payment activityxx1
 payment table. payment activityxx2
 payment table. payment activityxx3
 
For Claim XXXX2
 payment table. payment activityxx1
 payment table. payment activityxx2
 
without any other groups. 
 
Thank you for all your help!

Posted By: praveeng
Date Posted: 19 Sep 2013 at 4:28am
Hi,
 
Did you apply second method(i.e., Running total method)?
Do you want to show
 
For Claim XXXX1
payment table. payment activityxx1
payment table. payment activityxx2
payment table. payment activityxx3
 
For Claim XXXX2
payment table. payment activityxx1
payment table. payment activityxx2
 
For Claim XXXX3
payment table. payment activityxx1
payment table. payment activityxx2
payment table. payment activityxx3
 
Or are you looking something else..
 


-------------
Praveen Guntuka,
praveen_guntuka@yahoo.com

Posted By: Firegirl
Date Posted: 23 Sep 2013 at 10:33am
Yes, I tried the second method, but then it only returned one record (the third greatest) without the first and second records and nothing if the claim had less than three payments.
I then went on to write three separate nth equations where date is n=1, n=2 and n=3 but with  this way I have no way to pull in the payment amount associated with the first, second and third greatest dates of payment.  I am completely stumped!
 
Thank You!


Print Page | Close Window