Crystal selection based upon maximum date in field

I am trying to create a report in Crystal XI that will only print records where the date in the "end date" field is less than the most recent date for all records.  The table is a Business View pulled from a SQL database.

In other words, if I have 100 records and the most recent date for any given record in the "end date" field is 1/4/10, I only want records where the record's date for the end date field is less than 1/4/10.  I can do a summary to get the most recent date in this field, but then how do I use it as part of the selection criteria?

Thanks for any help you can provide.
pathcastorAsked:
Who is Participating?
 
PCIIainConnect With a Mentor Commented:
Suppress printing, but not necessarily remove from totals.

Your formula looks good for the suppress formula of the details section of the report.
0
 
mlmccCommented:
You can't use a summary in the selection criteria.  I think you will have to do this with SQL

Something like

Select Yourfields
From YourTable
Where YourRecordId In
    (Select YourRecordId, Max(YourDateField) as MaxDate Where MaxDate <= {?EndDate})

mlmcc
0
 
pathcastorAuthor Commented:
Is it possible to do this within the Crystal report itself?  Can I create a formula for the maximum date (i.e. (Maximum(MyDateField)) ) and somehow use the formula in selection criteria or to suppress records where MyDateField = (Maximum(MyDateField))?  
0
 
mlmccCommented:
You cann build that SQL in a COMMAND as the data source.

mlmcc
0
 
pathcastorAuthor Commented:
Thank you.  I put the formula in the suppress section of the details as a condition, and it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.