[Solved] Matrix report - row & column totals that count an alpha field
I need a matrix type report with row & column totals. The problem is that the totals need to count the occurrences of an alpha field. Below is an example using the CAR table. I want to count the occurrences of "X".
TABLE FILE CAR SUM COMPUTE FLD1/A1 = 'X'; AS '' -* COMPUTE FLD2/I2 = 1; AS '' BY CAR BY MODEL ACROSS TYPE AS '' ROW-TOTAL COLUMN-TOTAL ON TABLE SET NODATA '' ON TABLE SET BYDISPLAY ON ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET STYLE * TYPE=REPORT, SQUEEZE=ON, FONT='ARIAL', SIZE=8, $ TYPE=ACROSSVALUE, COLOR=BLUE, JUSTIFY=CENTER, $ TYPE=REPORT, COLUMN=N3, WRAP=1.0, JUSTIFY=CENTER, $ TYPE=REPORT, COLUMN=N4, WRAP=1.0, JUSTIFY=CENTER, $ TYPE=REPORT, COLUMN=N5, WRAP=1.0, JUSTIFY=CENTER, $ TYPE=REPORT, COLUMN=N6, WRAP=1.0, JUSTIFY=CENTER, $ TYPE=REPORT, COLUMN=N7, WRAP=1.0, JUSTIFY=CENTER, $ ENDSTYLE END
Thanks in advance for your help.This message has been edited. Last edited by: Kathleen Butler,
Dev Studio 8.0.09 App Studio 8.2.01 DB2, FOCUS PDF, HTML, Excel, CSV In Focus since 1983
June 13, 2012, 04:02 PM
susannah
COMPUTE FLD2/I2S = 1; AS ''
this didn't do it for you?
or this: TABLE FILE CAR SUM COMPUTE FLD1/A1 = 'X'; -* COMPUTE FLD2/I2S = 1; AS '' BY CAR BY MODEL BY BODYTYPE ON TABLE HOLD END
TABLE FILE HOLD SUM CNT.FLD1 BY CAR BY MODEL ACROSS BODYTYPE AS '' ROW-TOTAL COLUMN-TOTAL ON TABLE SET NODATA '' ON TABLE SET BYDISPLAY ON etc
there's a CNT. prefix and a CNT.DST. prefix 'zat help? .*............. or hows this? TABLE FILE CAR SUM COMPUTE FLD1/A1 = 'X'; AS '' -* COMPUTE FLD2/I2 = 1; AS '' BY CAR BY MODEL BY BODYTYPE ON TABLE HOLD END TABLE FILE HOLD SUM CNT.FLD1 AS 'HOWMANY' BY CAR BY MODEL SUM FLD1 BY CAR BY MODEL ACROSS BODYTYPE AS ''
COLUMN-TOTAL ON TABLE SET NODATA '' ON TABLE SET BYDISPLAY ON ON TABLE SET STYLE * TYPE=REPORT,FIELD=C1,SEQUENCE=8,$ ENDThis message has been edited. Last edited by: susannah,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
June 15, 2012, 05:05 PM
TedB
Thanks Susannah for your help, your technique worked great. I appended the column totals at the end of the report by making it a compound report.
Also, thanks to everyone else who took the time to read my post. I love Focal Point!
Dev Studio 8.0.09 App Studio 8.2.01 DB2, FOCUS PDF, HTML, Excel, CSV In Focus since 1983