We need to produce a report that each row has different format of data, for example:

	DEPT 1	DEPT 2
Total Revenue ($)	$100,000	$ 22,111
Total Employee (count)	142	99
Revenue Increase (%)	1.5%	2.1%

The only way I found is to create a dataset like this:


TYPE DEPT NUMBER
====== ===== ======
rev DEPT1 XXX
emp_count DEPT1 XXX
percent DEPT1 XXX

And use FML to produce the output like this:

TABLE ABC
SUM NUMBER
ACROSS DEPT
FOR TYPE

rev LABLE r NOPRINT OVER
RECAP revenue/D10M=r; AS 'Revenue' OVER
...
END

The problem is for each row I have to hide it first to get a label, then repeat the same row with a RECAP to customize the output format. Is there a way I can only do it once without a RECAP, or any better solution?

Thanks!

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

You could transpose the data like this post shows Transpose data.

Just make sure it is one row per department before using the code in the link above.

sorry dont have time to show utilizing the car file, however you'll get the picture:
DEFINE FILE myfile
NEWFMT/A8 = IF TYPE EQ 'REV' THEN 'D15M' ELSE IF TYPE EQ 'PERCENT' THEN 'D8.1%' ELSE 'D12' ;
END
TABLE FILE myfile
SUM NUMBER/NEWFMT
ACROSS PERIOD
FOR TYPE REV OVER
EMP_COUNT OVER
PERCENT
END

We utilize this technique when we have to MacGuyver many metric columns into a singular metric column to do FML type reporting. Hope it helps.

quote:

Originally posted by jimbo:
sorry dont have time to show utilizing the car file, however you'll get the picture:
DEFINE FILE myfile
NEWFMT/A8 = IF TYPE EQ 'REV' THEN 'D15M' ELSE IF TYPE EQ 'PERCENT' THEN 'D8.1%' ELSE 'D12' ;
END
TABLE FILE myfile
SUM NUMBER/NEWFMT
ACROSS PERIOD
FOR TYPE REV OVER
EMP_COUNT OVER
PERCENT
END

We utilize this technique when we have to MacGuyver many metric columns into a singular metric column to do FML type reporting. Hope it helps.

This is exactly what I needed. I'm amazed you can use a variable as format string in this way. Thanks jimbo!