Crystal selection based upon maximum date in field

Posted on 2010-01-05
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
    LVL 100

    Expert Comment

    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

    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 100

    Expert Comment

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

    LVL 13

    Accepted Solution

    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

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now