Print Page | Close Window

how to retrieve only most recent detail 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=8538
Printed Date: 01 May 2024 at 3:02pm


Topic: how to retrieve only most recent detail records
Posted By: flanman
Subject: how to retrieve only most recent detail records
Date Posted: 02 Dec 2009 at 10:24am
Hi all,
 I have a report where I am pulling  field titled inspection date. There can be numerous inspection dates for each record, and I only want to see the most recent one. What is the trick or formula for pulling on the most recent by date?

Thanks,
Flanman


Replies:
Posted By: DBlank
Date Posted: 02 Dec 2009 at 3:08pm
if this is only a matter of displaying group on a field that defines whqat you meant by 'record' sort desc by your date field and palce the date record on the group header or do conditional suppression on details if needed.
If you have to select the records you would have to write a Command in crystal or use a view or stored proc to trim your data down and then use that as your source.

Posted By: flanman
Date Posted: 03 Dec 2009 at 6:53am
Not sure if I understand. Sorry I am a bit of a newbie with CR.
I have a group or records that may be something like this.

Record ID                                 date
5                                             5/15/2009
5                                             6/16/2008
5                                             7/15/2004
6                                            10/15/2009
6                                             8/25/2006

I only want to see the most recent date for each record so my result would be:
Record ID                                 date
5                                             5/15/2009
6                                            10/15/2009

Posted By: DBlank
Date Posted: 03 Dec 2009 at 6:55am
Group on Record ID field.
Sort on Date field descending
Place date field on group header
Suppress details section (and group footer)

Posted By: flanman
Date Posted: 03 Dec 2009 at 8:51am
That worked like a charm. Thanks a bunch.

Now can I create a formula or filter on just that most recent date?

MY goal is to create a date range filter on that date which is only the most recent date for each record.

Posted By: DBlank
Date Posted: 03 Dec 2009 at 10:25am
You mean you want to see only the record ID's that have a max date in a certain range?
This can be done, although if you are continuing down this path with doing calculations it may better to approach it at the data source.

Posted By: flanman
Date Posted: 03 Dec 2009 at 12:16pm
Unfortunately I only have read access from the data source (SCL) So my filtering/selecting has to be done in CR. 

Posted By: DBlank
Date Posted: 03 Dec 2009 at 12:31pm
Do you want to select Record IDs that have a max value between 2 param dates?
Group on Record ID
Insert a Summary Function (Sigma Sign) as: 
Field to summarize=Date field
Calculate as Maximum
Summary Location=Group1 footer (record ID footer)
YOu can suppress this field but you need it for the select expert.
 
Add your 2 date params (?Start Date and ?End Date in this example)
go to your select expert
Hit show formula
Make sure "Group selection" is toggled on
Click on formula editor
Find the MAXIMUM Summary field in your Report Fields and double click it to move it to the formula window
add your date params to the formula so it will end up somehing like:
MAXIMUM({table.datefield},{table.RecordID}) in {?Start Date} to {?End Date}
 
Is that what you wanted?
NOTE if you have a group tree on you will still see IDs appear there that are now exclude in the body of the report. Group selection process occurs at a different point in the data reading than record selction process.
 

Posted By: flanman
Date Posted: 03 Dec 2009 at 1:08pm
What I have is a database with  location information in it. These locations have inspection dates that have to be renewed each year before the location can be used again. Since we have used these locations for several years they have several inspection dates and I am only interested in the most recent inspection dates. My final goal it to make look ahead 6 weeks on an event schedule and see if any locations inspection dates will be come outdated before the event. Hopefully that makes sense. 
Example - We have an event on Feb 2, 2010 and most recent inspection date was may 5, 2009. That is fie.
Example 2 - We have and event on March 17, 2010 and the last inspection date was Jan 2009. That inspection will be outdated by Jan 2010 and will need to be updated before the Event date. My goal is to create a report where locations fall into the example 2 category. I have my formula for doing the date lookup an comparison done. My stumbling point is when I run the report I am getting older inspections from say 2007 and 2006, which is throwing off the report since the locations will also have more recent inspections and and the most recent inspection date is the only one I  need. Maybe my formula might help explain.
(EventDate)>=((InspectionDate)+365)
The report also has a select criteria to look out six weeks (CurrentDate+42) so we can see upcoming events that fall into this category.

Sorry for all the details, but I have been stuck on this for two days.

Any and All help is greatly appreciated.

Posted By: lorimack
Date Posted: 03 Dec 2009 at 1:12pm
what if the information you want to show most recent is not numerical?
 
What if it is something such as this:
 
IDNumber Track Description
3 returning back to court
2 person was not home
1 Attempted service
 
I would want ID#3 to show, but ID#1 is showing instead.

Posted By: flanman
Date Posted: 03 Dec 2009 at 1:16pm
I would only want one ID to show per record based only on the most recent date.

Posted By: flanman
Date Posted: 03 Dec 2009 at 3:05pm
I am almost there. Here is what I ended up doing.  Grouped by location and then Event ID.
Then created the formula :
NthLargest (1,{SiteInspectionDetail.InspectDate} ,{DriveMaster.DriveID});
and inserted in the Event ID Group Header. This gave me the most current inspection date. I then moved the Detail fields to the same group header with the exception of the date. I then surpressed the details and header # 1 row and I now am almost there.  Now I have to figure out how to filter by that most recent inspection date and the event date with a formula.

Flanman


Print Page | Close Window