×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Next_Review_Date - Eliminate multiple rows?

Next_Review_Date - Eliminate multiple rows?

Next_Review_Date - Eliminate multiple rows?

(OP)
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.

RE: Next_Review_Date - Eliminate multiple rows?

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

RE: Next_Review_Date - Eliminate multiple rows?

(OP)
It is Enterprise, version 3.00B

RE: Next_Review_Date - Eliminate multiple rows?

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

RE: Next_Review_Date - Eliminate multiple rows?

(OP)
It is PS_Employee_Review

RE: Next_Review_Date - Eliminate multiple rows?

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

RE: Next_Review_Date - Eliminate multiple rows?

(OP)
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!

RE: Next_Review_Date - Eliminate multiple rows?

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

RE: Next_Review_Date - Eliminate multiple rows?

(OP)
The first four are:

EMPLID
EMPL_RCD_NBR
REVIEW_DT
NEXT_REVIEW_DT

RE: Next_Review_Date - Eliminate multiple rows?

Quote (Charles Cook):

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

RE: Next_Review_Date - Eliminate multiple rows?

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

RE: Next_Review_Date - Eliminate multiple rows?

(OP)
It worked!!! THANK YOU!!!! I really appreciate it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close