I need to complete a report by Employee, Dept, and PaySumGrp types. Each column represents a PaySumGrp (Regular Hours, OverTime, Holiday Hours, etc..)

I am Grouping by PRTIME.EMPLOYEE, and Putting the Running Totals of each PaySumGrp in the Footer. I also have a parameter for PRTIME.PER_END_DATE - My Running totals appear to just be calculating the current Pay Period - which is ok, but if I add a GFb to Employee, How can I get a running total for YTD? Isn't the Parameter qualifier above just going to force the total on current Pay Period?

Heres the SQL Query:


SELECT DISTINCT "EMPLOYEE"."HM_ACCT_UNIT", "PRTIME"."PAY_SUM_GRP", "EMPLOYEE"."EMP_STATUS", "PRTIME"."HOURS", "JOBCODE"."DESCRIPTION", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."JOB_CODE", "PRTIME"."EMPLOYEE", "DEPTCODE"."R_NAME", "PRTIME"."PER_END_DATE", "PRTIME"."PAYROLL_YEAR"
FROM "LAWSON"."DEPTCODE" "DEPTCODE", "LAWSON"."EMPLOYEE" "EMPLOYEE", "LAWSON"."PRTIME" "PRTIME", "LAWSON"."JOBCODE" "JOBCODE"
WHERE ("DEPTCODE"."DEPARTMENT"="EMPLOYEE"."HM_ACCT_UNIT") AND ("EMPLOYEE"."EMPLOYEE"="PRTIME"."EMPLOYEE") AND ("EMPLOYEE"."JOB_CODE"="JOBCODE"."JOB_CODE" (+)) AND ("PRTIME"."PER_END_DATE">=TO_DATE ('15-01-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "PRTIME"."PER_END_DATE"<TO_DATE ('16-01-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND NOT ("EMPLOYEE"."EMP_STATUS"='RT' OR "EMPLOYEE"."EMP_STATUS"='SV' OR "EMPLOYEE"."EMP_STATUS"='XI' OR "EMPLOYEE"."EMP_STATUS"='XR' OR "EMPLOYEE"."EMP_STATUS"='XV' OR "EMPLOYEE"."EMP_STATUS"='XZ' OR "EMPLOYEE"."EMP_STATUS"='ZA' OR "EMPLOYEE"."EMP_STATUS"='ZB' OR "EMPLOYEE"."EMP_STATUS"='ZC' OR "EMPLOYEE"."EMP_STATUS"='ZO' OR "EMPLOYEE"."EMP_STATUS"='ZP' OR "EMPLOYEE"."EMP_STATUS"='ZS' OR "EMPLOYEE"."EMP_STATUS"='ZV' OR "EMPLOYEE"."EMP_STATUS"='ZX')
ORDER BY "EMPLOYEE"."HM_ACCT_UNIT", "PRTIME"."EMPLOYEE

NOTE: BECAUSE YOU ARE REPORTING OFF PRTIME I DONT RECOMMEND THIS.

Having said that, you use the Pay Period as your grouping and reset on you Pay Period running total, then create a second running total that never resets. In your select statement return all rows where;

Should work.

Martin, youve done this before, is that right?


hth
matt

Yeah, that should work.