DATES

[blairclw]

I have a report that pulls in employees and their Next Review date. Instead of pulling the lastest and greatest next review date, say one due in 2005, its pulling all next review dates that every employee has ever had. Is there a way I can tell it to only pull the most current next review date?

PERFORMANCE.NextPerformanceReviewDate

 

[lbass]

Insert a group on {table.employeeID} and then go to report->selection formula->GROUP and enter:

{PERFORMANCE.NextPerformanceReviewDate} = maximum({PERFORMANCE.NextPerformanceReviewDate},{table.employeeID})

-LB

 

[blairclw]

Sorry, not sure what you mean by insert a group. I did go in and grouped employees together and now if an employee has more than one coming up you can see it. Here is an example.

EMPLOYEE NAME NEXT REVIEW DATE
employee test 06/04/2003
employee test 06/04/2004
employee test 06/05/2004
employee test 10/01/2005

I can see all dates and I only want to see the latest and greatest.

Here is how my report is set up so far so how would I add to this formula?

{PERFORMANCE.PerformanceReason} = "Annual" and
{TMC_EmployeePos_Stat_Curr.EmployeeStatus} in ["Active", "LOA"] and
{PERFORMANCE_CODES.PerformanceCodeFromEffectDate} = DateTime (1900, 01, 02, 00, 00, 00)

 

[lbass]

When you say you grouped employees together, I'm assuming that means you inserted a group on your employee field. You are showing the employee name field, but it would be better to use an ID field in case of duplicate names. Anyway, lets' assume that you only have the name field to work with. You would use your formula above as a record selection formula. You can see this formula by going to report->selection formula->RECORD. To select the most recent date, you go to a different formula area: report->selection formula->GROUP and enter the following formula there:

{PERFORMANCE.NextPerformanceReviewDate} = maximum({PERFORMANCE.NextPerformanceReviewDate},{table.employee_name})

Substitute the field that you are grouping on for {table.employee_name}.

-LB