Derived Field or Macro 2

[Helixe]

Hello,

First off, our company utilizes PCPW 5.1, and Reportsmith ver 4.2 (oracle)

Being a new user, I've searched the forum to verify if my question has already been posted. There are a couple that are similar but not exactly what I'm looking for.

I'm hoping someone can assist. I've created a report that has a derived filed to calculate Length of service ("LOS"). "LOS" results in Years and Months. I'm trying to create another derived field that will compare the results and give a value based on an annual maximum. e.g., If "LOS" is 0-5 years, then give 25 days, if "LOS" is 6-10 years, then give 37.5, if "LOS" is >10 years, then give 50 days.

I am open for suggestions.

Thank you in advance.

 

[CharlesCook]

Give this a try:

CASE
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) < 6 THEN 25
WHEN ((TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) > 5) AND (TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) < 10)) THEN 37.5
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) > 10 THEN 50
END

YOU WILL NEED TO CHANGE "PS_EMPLOYMENT"."HIRE_DT" to the PCPW FIELD NAME. I put this together on a enterprise system not a PCPW system.

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

 

[Helixe]

Charles,

Thank you for the quick reply. Yes, I too am more familiar with ReportSmith under the ADP Enterprise application.

I will attempt to utilize your suggestion and post my results if successful.

Thank you,

 

[CharlesCook]

I think the part in bold red is incorrect:

CASE
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) < 6 THEN 25
WHEN ((TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) > 5) AND (TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) < 10[/color red])) THEN 37.5
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,"PS_EMPLOYMENT"."HIRE_DT") / 12) > 10 THEN 50
END

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