Group Max Selection Question

Posted on 2009-02-10
Last Modified: 2012-05-06
I have a report in crystal XI where I need to calculate the payments that the company has to pay for all its employees.
If an employee is active, their {BENEFITS.STOP_DATE} = 1/1/1753. Unfortunately there are some employees that have either 1/1/1753 or 12/31/2008.  The majority of records has the end date as 1/1/1753, but for those that have both, I need to select the 12/31/2008 record. I wrote this formula:
if {BENEFITS.STOP_DATE} = 1/1/1753 then 1 else
if {BENEFITS.STOP_DATE} = 12/31/2008 then 2 else 0

I then would like to use the select expert to select the maximum value of {@Check}, but I can't seem to find the correct way to do this.

Any suggestions would be helpful

Question by:metalteck
    LVL 77

    Expert Comment

    I don't see how you can do this by record selection.
    You will have to use a conditional suppression formula on records in the report to eliminate the umnwanted records.

    Sort the records by Stop_date within Employees id; then you could do...

    If not onlastrecord and {benefits.employeeid}=next({benefits.employeeid}) then
    true   //suppress this record

    This formula does assume that you cannot have two records for the same employee both with a date of 1/1/1753.

    Author Comment

    Thanks, this helps me supress the unwanted records, but when I try to calculate the payments, the totals are all wrong.
    Ex: Before Suppression

    Name        Stop Date    Cost   Check
    Joe Smith   1/1/1753    48000    1
    Joe Smith  12/31/2008   46000    2
    GF                      46000
    Jane Smith  1/1/1753    43000    1
    Jane Smith 12/31/2008   41000    2
    GF                      41000
    Subtotal               91000

    After Suppression:
    Name        Stop Date    Cost   Check
    Joe Smith  12/31/2008   46000    2
    GF                      46000
    Jane Smith 12/31/2008   41000    2
    GF                      41000
    Subtotal               91000
    LVL 77

    Accepted Solution

    To create subtotals where you are suppressing records that contain values you don't want to include, you can use Running Totals fields.  Using normal Summary totals will include the suppressed records.
    LVL 100

    Assisted Solution


    I assume you are grouping by the person
    You could also then sort by the date and display the data in the group header.

    The running total could be set to evaluate on change of group


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    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…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now