Hi

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 you

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

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

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

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

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.

Thank you for your suggestions and solutions.
By slightly tweaking according to my code, I am able to get these to work.