Jan Feb Mar Apr ... 2005 Dept Production Gross n n n n n Ded m m m m m MIS Gross Ded
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
DEFINE FILE EMPLOYEE PAYDTYM/YYM = PAY_DATE; END TABLE FILE EMPLOYEE SUM GROSS OVER DED_AMT BY DEPARTMENT ACROSS PAYDTYM END
SET ASNAMES = ON DEFINE FILE EMPLOYEE PAYDTYM/YYM = PAY_DATE; GROSSTYPE/A20 = 'GROSSAMT'; DEDTYPE/A20 = 'DEDAMT'; END TABLE FILE EMPLOYEE PRINT DEPARTMENT PAYDTYM GROSSTYPE AS MEASTYPE GROSS AS MEASURE ON TABLE HOLD AS R1 END TABLE FILE EMPLOYEE PRINT DEPARTMENT PAYDTYM DEDTYPE DED_AMT ON TABLE HOLD AS R2 END TABLE FILE R1 PRINT * ON TABLE HOLD AS RPT1 MORE FILE R2 END TABLE FILE RPT1 SUM AVE.MEASURE NOPRINT BY DEPARTMENT BY MEASTYPE SUM AVE.MEASURE AS '' BY DEPARTMENT BY MEASTYPE ACROSS PAYDTYM COMPUTE AVGMEAS/D12.2 = C1; AS 'YEARLY AVG' COMPUTE DIFF/D12.2 = AVGMEAS - C11; END
DEFINE FILE EMPLOYEE PAYDATE/A17 = EDIT(PAY_DATE); -* Convert the paydate (I6YMD) to Alpha PAYMNTH/A4 = CHGDAT('YMD','MT',PAYDATE,'A4'); PAYYEAR/YY = PAY_DATE; END -* Get a count of columns for the calculations TABLE FILE EMPLOYEE BY PAYMNTH WHERE PAYYEAR EQ 1982 ON TABLE SAVE END -RUN -SET &Cols = &LINES; -* TABLE FILE EMPLOYEE SUM AVE.DED_AMT AS 'Deductions' OVER AVE.GROSS AS 'Gross' -* Force your columns using COLUMNS ... AND ... ACROSS PAYMNTH AS '' COLUMNS 'JAN' AND 'FEB' AND 'MAR' AND 'APR' AND 'MAY' AND 'JUN' AND 'JUL' AND 'AUG' AND 'SEP' AND 'OCT' AND 'NOV' AND 'DEC' -* Force the two extract columns required AND 'AVG' AND 'DIFF' WHERE PAYYEAR EQ 1982 -* Now the FML bit FOR DEPARTMENT 'MIS' LABEL MIS OVER 'PRODUCTION' LABEL PRD OVER RECAP MIS(25) = (MIS(1) + MIS(3) + MIS(5) + MIS(7) + MIS(9) + MIS(11) + MIS(13) + MIS(15) + MIS(17) + MIS(19) + MIS(21) + MIS(23)) / &Cols; OVER RECAP MIS(26) = (MIS(2) + MIS(4) + MIS(6) + MIS(8) + MIS(10) + MIS(12) + MIS(14) + MIS(16) + MIS(18) + MIS(20) + MIS(22) + MIS(24)) / &Cols; OVER RECAP PRD(25) = (PRD(1) + PRD(3) + PRD(5) + PRD(7) + PRD(9) + PRD(11) + PRD(13) + PRD(15) + PRD(17) + PRD(19) + PRD(21) + PRD(23)) / &Cols; OVER RECAP PRD(26) = (PRD(2) + PRD(4) + PRD(6) + PRD(8) + PRD(10) + PRD(12) + PRD(14) + PRD(16) + PRD(18) + PRD(20) + PRD(22) + PRD(24)) / &Cols; OVER -* Here you need to achieve similar calculations for the DIFF column "" ON TABLE SET HTMLCSS ON ON TABLE SET PAGE NOLEAD ON TABLE SET STYLE * GRID=OFF, SIZE=8, $ ENDSTYLE END
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |