Compare Summary Values

[MHurdatADP]

This is Enterprise V4, RS 3.10. I have two columns, one with REG hours, the other with OT hours. I can create a derived field which compares OT hours to REG hours at the detail level as a percent (OT/REG).

I can sum the REG and OT hours columns by dept or paygroup. Is it possible to compare the summary totals in the same way as at the detail level?

 

[CharlesCook]

You would need to create a Macro Derived Field. It would be a very simple macro but I don't think I could walk you through it in a text based way. I need to see the report to do it.

These will help:

http://www.srs-inc.com/Downloads/Doc4x/UsersGuide/ugc9.pdf

http://www.srs-inc.com/Downloads/Doc4x/UsersGuide/ugc10.pdf

http://www.srs-inc.com/Downloads/Doc4x/UsersGuide/ugc11.pdf

http://www.srs-inc.com/support/KnowledgeBase/start.htm

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

 

[MHurdatADP]

Thanks for the guides. Unfortunately, until I have some formal training using macros, or at least an example, it's all Greek. What I want to do is show Overtime Hours as a percent of Regular Hours and Overtime Earnings as a percent of Regular Earnings. (OT/REG) Reg hours and earnings come from row 1, plus earnings code 300, while OT hours/earnings come from row 2 and earnings code 202.

Here's the SQL

SELECT
( DECODE("PS_AL_CHK_HRS_ERN"."ROW_NBR",1 ,"PS_AL_CHK_HRS_ERN"."AL_HOURS",0 ) + DECODE("PS_AL_CHK_HRS_ERN"."ERNCD",'300' , "PS_AL_CHK_HRS_ERN"."AL_HOURS",0) ), ( DECODE("PS_AL_CHK_HRS_ERN"."ROW_NBR",1 ,"PS_AL_CHK_HRS_ERN"."EARNINGS",0 ) + DECODE("PS_AL_CHK_HRS_ERN"."ERNCD",'300' ,"PS_AL_CHK_HRS_ERN"."EARNINGS",0 )) , ( DECODE("PS_AL_CHK_HRS_ERN"."ROW_NBR",2 ,"PS_AL_CHK_HRS_ERN"."AL_HOURS",0 ) + DECODE("PS_AL_CHK_HRS_ERN"."ERNCD",'202' , "PS_AL_CHK_HRS_ERN"."AL_HOURS",0) ), ( DECODE("PS_AL_CHK_HRS_ERN"."ROW_NBR",2 ,"PS_AL_CHK_HRS_ERN"."EARNINGS",0 ) + DECODE("PS_AL_CHK_HRS_ERN"."ERNCD",'202' ,"PS_AL_CHK_HRS_ERN"."EARNINGS",0 )) ,
"PS_AL_CHK_HRS_ERN"."AL_HOURS", "PS_AL_CHK_HRS_ERN"."EARNINGS", "PS_AL_CHK_HRS_ERN"."ERNCD", "PS_AL_CHK_HRS_ERN"."FILE_NBR", "PS_JOB"."PAYGROUP", "PS_JOB"."HOME_DEPARTMENT"
FROM
"PS_AL_CHK_HRS_ERN", "PS_JOB"
WHERE
((("PS_AL_CHK_HRS_ERN"."PAYGROUP" IN( 'TXM', 'TH2', 'TPS')) AND
(("PS_JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
("PS_AL_CHK_HRS_ERN"."PAYGROUP" = '<<Paygroup>>')))
AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID" ) AND ("PS_AL_CHK_HRS_ERN"."FILE_NBR" = "PS_JOB"."FILE_NBR" ) AND ("PS_AL_CHK_HRS_ERN"."PAYGROUP" = "PS_JOB"."PAYGROUP" )
ORDER BY
"PS_JOB"."PAYGROUP", "PS_JOB"."HOME_DEPARTMENT