Next_Review_Date - Eliminate multiple rows?

[Gymnast14]

I am trying to create a report to show the employees next review date. However, it pulls multiple lines for each employee because they have more than one "next review date" entered. For example, Jane Doe has three lines because she has 5-1-05, 5-1-06, and 5-1-07 all listed in the Employee_review table.

Anyone know if a way to get ReportSmith to just pull the "maximum" date line for each person (in other words, only pull one line for Jane Doe with the 5-1-07 date).

This table doesn't have the effective date option, so I can't use that. I was able to use a pivot table in excel to only pull the "maximum" date for each person, but I'd like to be able to get ReportSmith to do it for me, if possible. Thanks in advance.

 

[CharlesCook]

What version of ADP is this? Enterprise or PCPW or what? I'm just not finding the "Employee_review" table.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[Gymnast14]

It is Enterprise, version 3.00B

 

[CharlesCook]

is it?

PS_Employee_Review

or

PV_Employee_Review

or something a little different?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[Gymnast14]

It is PS_Employee_Review

 

[CharlesCook]

I don't have acces to PS_Employee_Review but I found it in a Data Dict so I think I have what I need.

What we are going to do is a Selection Critera that will do effective dating using the REVIEW_DT column.

Create a NEW report that uses PS_Employee_Review give this table an Alias of ER

Go to Selections and add a new selection.
The Data Field ER.REVIEW_DT is equal to Formula...
The formula is where everything happens. We are going to do the same kind of query as effective dating.

(SELECT
MAX("INNERALIAS"."REVIEW_DT")
FROM
PS_EMPLOYEE_REVIEW INNERALIAS
WHERE
((("INNERALIAS"."EMPLID" = "ER"."EMPLID")
AND
(("INNERALIAS"."EMPL_RCD_NBR" = "ER"."EMPL_RCD_NBR")))

It should end up looking something like this:

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[Gymnast14]

Ok, I tried it, and I get this error message:

"ORA-00904: "ER"."EMPL_RCD_NBR":invalid identifier

Any suggestions? Thanks SO much for your help!

 

[CharlesCook]

I'm lookin at an old version of a Data Dict. Does PS_Employee_Review still have a EMPL_RCD_NBR field?

What are the first 4 fields in your PS_Employee_Review table?

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[Gymnast14]

The first four are:

EMPLID
EMPL_RCD_NBR
REVIEW_DT
NEXT_REVIEW_DT

 

[CharlesCook]

Create a NEW report that uses PS_Employee_Review give this table an Alias of ER

Did you give the table an Alias of ER?

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[CharlesCook]

I just noticed I had a problem with my WHERE Clause. Use this SQL.

(SELECT
MAX("INNERALIAS"."REVIEW_DT")
FROM
PS_EMPLOYEE_REVIEW INNERALIAS
WHERE
(("INNERALIAS"."EMPLID" = "ER"."EMPLID")
AND
("INNERALIAS"."EMPL_RCD_NBR" = "ER"."EMPL_RCD_NBR")))


CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[Gymnast14]

It worked!!! THANK YOU!!!! I really appreciate it.