[Last Call] Learn how to a build a cloud-first strategyRegister Now


Crystal selection based upon maximum date in field

Posted on 2010-01-05
Medium Priority
Last Modified: 2012-05-08
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.
Question by:pathcastor
  • 2
  • 2
LVL 101

Expert Comment

ID: 26181538
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})


Author Comment

ID: 26181847
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))?  
LVL 101

Expert Comment

ID: 26182128
You cann build that SQL in a COMMAND as the data source.

LVL 13

Accepted Solution

PCIIain earned 2000 total points
ID: 26182130
Suppress printing, but not necessarily remove from totals.

Your formula looks good for the suppress formula of the details section of the report.

Author Closing Comment

ID: 31672977
Thank you.  I put the formula in the suppress section of the details as a condition, and it works.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 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