Need to Pull ONLY Most Recent Raise

[lhendrickson]

We are on Crystal version 9.0. I am working on a report for our HR department. They have a need to see the most recent raise for each employee. Due to multiple tables being involved I had a number of duplicate records. I have narrowed my dataset down quite a bit using the following formulas:

@Concatenate Fields =
{DEPTCODE.NAME}&{EMPLOYEE.ADJ_HIRE_DATE}&{EMPLOYEE.EMPLOYEE}&{EMPLOYEE.EMP_STATUS}&{EMPLOYEE.FIRST_NAME}&{EMPLOYEE.LAST_NAME}&{EMPLOYEE.MIDDLE_INIT}&{EMPLOYEE.PAY_RATE}&{EMPLOYEE.PROCESS_LEVEL}&{EMPLOYEE.SALARY_CLASS}&{HRSUPER.DESCRIPTION}&{JOBCODE.DESCRIPTION}&{PRRATEHIST.DATE_STAMP}&{PRRATEHIST.PAY_RATE}
*****Nothing more than concatenating all the fields on the report*****

@Suppress Duplicates =
IF {PRRATEHIST.PAY_RATE}={@Current Pay Rate}then "Flag"
Else IF Previous ({@Concatenate Fields})={@Concatenate Fields}then "Flag" else "No Flag"

in conjunction with this formula in the suppress formatting area of the details section=
IF{@Suppress Duplicates}="Flag" then True

I have the report sorted on {EMPLOYEE.LAST_NAME} and {PRRATEHIST.DATE_STAMP} <--Last name sorting being a requirement of the HR department

None of the above formulas addressed the following problem: I still have some records of an employee receiving more than one raise. I researched the forum and read multiple references of the Maximum command to be used on the Date field. I also read that the suppressed records are still there, just not being printed, so I am unsure on how to incorporate the Maximum command with the formulas I already have in place --- or is there a better solution?

Any and all solutions you can provide will be appreciated. Please let me know if I have not provided enough data or if my data is unclear.

Thanks!

LHendrickson

 

[synapsevampire]

Select Database->Select Distinct Records to eliminate real dupes. Most people use that term incorrectly though.

Given very little technical information about the database, I'd suggest pursuing using a MAX in the record selection formula->group based on the max(date).

This means creating a group by employee and one by the date.

I'd do all of this on the database side though using a subquery.

-k

 

[lhendrickson]

Thank You Synapsevampire!!!

I followed your suggestion and it worked beautifully.

You are correct I was misusing the term duplicates b/c although I intended to suppress some of these lines they were not pure duplicates.

Note:
For those of you that are unfamiliar with grouping - like me - you should know that grouping does not have to change the structure of your report. You can easily suppress the Group Header and Footer and still use the group functionality in formulas. The format of your report doesn't have to change one bit.

Thanks for your assistance!

LHendrickson