I am working on a report. After all joins and calculations, final print request is in this format and I am trying to calculate totals.
TABLE FILE T1
SUM
CNTR_1 AS '' OVER
CNTR_2 AS ''
BY NAME AS ''
ACROSS T_NAME AS ''
END
I need column total of just CNTR_1, so I used "ON TABLE COLUMN-TOTAL CNTR_1". In the same way, how can I get row total for just that but not CNTR_2?
thank youThis message has been edited. Last edited by: Kerry,
8.1.05 HTML,PDF,EXL2K, Active, All
June 05, 2012, 12:42 PM
Danny-SRL
Enigma,
This is a bit tricky (and maybe someone else can come up with asimpler solution). I didn't find a command to sum certain fields in an across, so here goes:
-* File enigma01.fex
-* Find the number and values of the ACROSS field
TABLE FILE CAR
BY SEATS
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS SNUM
END
-RUN
-* Save them in a &variable array
-SET &SEATS=&LINES;
-REPEAT #GETS FOR &I FROM 1 TO &SEATS;
-READ SNUM,&S.&I
-#GETS
-RUN
-* Create the data and hold it
TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
BY COUNTRY
-* This COMPUTE sums only every other value, hence the across total of the RETAIL_COSTs
ACROSS SEATS COMPUTE
TOT/D6=C1
-REPEAT #TOT FOR &I FROM 2 TO &SEATS;
-SET &C=&I * 2 - 1;
+ C&C
-#TOT
;
-* The ASNAMES gives each saved field a suffix equal to the value of the ACROSS field
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
-* Print the individual fields of the HOLD file for the report
TABLE FILE HOLD
SUM
-REPEAT #RET FOR &I FROM 1 TO &SEATS;
RET&S.&I AS ''
-#RET
TOT AS '' OVER
-REPEAT #DEA FOR &I FROM 1 TO &SEATS;
DEA&S.&I AS ''
-#DEA
BY COUNTRY
ON TABLE COLUMN-TOTAL
-REPEAT #COLT FOR &I FROM 1 TO &SEATS;
RET&S.&I
-#COLT
TOT
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 05, 2012, 12:54 PM
Enigma006
Thanks Danny. I will give this a try but this is more complex. I provided a simpler version of the code but there is lot more. So, I am doubtful how much this will work.
quote:
Originally posted by Danny-SRL: Enigma,
This is a bit tricky (and maybe someone else can come up with asimpler solution). I didn't find a command to sum certain fields in an across, so here goes:
-* File enigma01.fex
-* Find the number and values of the ACROSS field
TABLE FILE CAR
BY SEATS
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS SNUM
END
-RUN
-* Save them in a &variable array
-SET &SEATS=&LINES;
-REPEAT #GETS FOR &I FROM 1 TO &SEATS;
-READ SNUM,&S.&I
-#GETS
-RUN
-* Create the data and hold it
TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
BY COUNTRY
-* This COMPUTE sums only every other value, hence the across total of the RETAIL_COSTs
ACROSS SEATS COMPUTE
TOT/D6=C1
-REPEAT #TOT FOR &I FROM 2 TO &SEATS;
-SET &C=&I * 2 - 1;
+ C&C
-#TOT
;
-* The ASNAMES gives each saved field a suffix equal to the value of the ACROSS field
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
-* Print the individual fields of the HOLD file for the report
TABLE FILE HOLD
SUM
-REPEAT #RET FOR &I FROM 1 TO &SEATS;
RET&S.&I AS ''
-#RET
TOT AS '' OVER
-REPEAT #DEA FOR &I FROM 1 TO &SEATS;
DEA&S.&I AS ''
-#DEA
BY COUNTRY
ON TABLE COLUMN-TOTAL
-REPEAT #COLT FOR &I FROM 1 TO &SEATS;
RET&S.&I
-#COLT
TOT
END
8.1.05 HTML,PDF,EXL2K, Active, All
June 05, 2012, 04:18 PM
Dan Satchell
Here's another approach. Essentially it involves creating a new country named 'TOTAL' and populating it with summarized values for the BY field (CAR) from the first summary field only (RETAIL_COST in this example). The names of the across field (COUNTRY) are prefixed with a space so they will sort before the 'TOTAL' column.
DEFINE FILE CAR
XCOUNTRY/A11 = ' ' | COUNTRY ;
END
-*
TABLE FILE CAR
SUM
RETAIL_COST
DEALER_COST
BY CAR
BY XCOUNTRY
ON TABLE HOLD AS CARHOLD1 FORMAT ALPHA
END
-*
FILEDEF CARHOLD1 DISK CARHOLD1.FTM (APPEND
-*
TABLE FILE CAR
SUM
COMPUTE XCOUNTRY/A11 = 'TOTAL';
RETAIL_COST
BY CAR
ON TABLE SAVE AS CARHOLD1
END
-*
TABLE FILE CARHOLD1
SUM
RETAIL_COST/D8S AS '' OVER
DEALER_COST/D8S AS ''
BY CAR AS ''
ACROSS XCOUNTRY AS ''
ON TABLE COLUMN-TOTAL RETAIL_COST
ON TABLE SET NODATA ''
END
WebFOCUS 7.7.05
June 06, 2012, 07:43 AM
Danny-SRL
Enigma,
Forget my previous solution: I forgot good old McGuyver!!! Ouch! SHAME ON ME!
JOIN BLANK WITH SEATS IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
SEATS=IF COUNTER EQ 1 THEN SEATS ELSE 9;
DEALER_COST/D6S=IF COUNTER EQ 1 THEN DEALER_COST ELSE 0;
END
TABLE FILE CAR
IF COUNTER LE 2
SUM RETAIL_COST AS ''
OVER DEALER_COST AS ''
BY COUNTRY
ACROSS SEATS NOPRINT
ON TABLE COLUMN-TOTAL RETAIL_COST
END
If I may be so bold: quite elegant.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 06, 2012, 09:42 AM
Enigma006
Thank you for your suggestions and solutions. By slightly tweaking according to my code, I am able to get these to work.