I'm trying to do a crosstab that would (in CAR terms) report the avg gross pay, and avg deduction amounts, by department, across the months of a year, and then have a yearly avg for each of those at the end:

                    Jan    Feb    Mar   Apr   ...     2005
Dept
Production   Gross    n      n      n     n              n
             Ded      m      m      m     m              m
MIS          Gross
             Ded

I can do the crosstab and even use OVER to display the Gross over Ded, but it's the year end average that I'm having difficulty with. I'm using the multi-verb approach, but when I do it screws up the entire report.

Is this better done with FML? Any pointers?

This message has been edited. Last edited by: MacLonghorn,

Mark

If you post a snippet of your code we might be able to see better what you're up to.

If you post your example you better can put this [code ] [/code ] around it (without the spaces).

Here's a quick sample:

  
DEFINE FILE EMPLOYEE
	PAYDTYM/YYM = PAY_DATE;
END
TABLE FILE EMPLOYEE
SUM
	GROSS OVER
	DED_AMT
BY DEPARTMENT
ACROSS PAYDTYM
END
 

This does a sum, but I'm trying to do a monthly average, and then on top of that, have a yearly average at the end, to the right of the last month. Also, I need to display the year end averages OVER each other, as in the main report.

I think I've got a solution. Instead of using OVER, I can transform the different values (GROSS and DED_AMT) into a single column, using MORE. Then I can use the BY groupings instead of over....

 
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
 

But is there a nicer, easier way to do this with FML?

Mark,

One of the problems with achieving a similar result using FML is that you would need to "invent" additional columns.

Now this can be done by using the COLUMNS syntax on the ACROSS verb, but you would need to have an across value that allowed you to force two new columns. For instance, you couldn't just use the format MT and then have COLUMNS 'JAN' AND .... 'DEC' AND 'COL1' AND 'COL2' because you would get a data value format error (COL1 isn't an MT format). What I would do is convert the month into an alpha string and then force the two additional columns, as COL1 is an acceptable alpha value.

Another problem that you would encounter is, to get the deductions over the gross, you would have to use OVER between the summed values. This would mean that the cell annotation that you would have to use in the RECAP statements would have to be carefully done.

See this example using the same data as per yours -

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

Not sure if you think this is a "nicer, easier" method than that you already have, but it is in FML as you wanted. For my money this is the way to go as is only needs one proper pass of the data (the extract for the &Cols aside).

Enjoy

T

Edited to correct spelling

This message has been edited. Last edited by: Tony A,

Excellent, thanks Tony. Good FML lesson/technique for me. I think I'll stick with the prior method and try to make as few passes as possible.