I am trying to get the columns in the SET COMPOUND=BYTOC report to total on specific columns.
Begin Balance
Current Balance
Payment Amount
If I use ON TABLE COLUMN-TOTAL AS 'TOTAL' BEG_BALANCE I get another tab in the excel workbook. I am wanting a total on the specific columns on each tab.
Any help would greatly be appreciated.

DEFINE FILE FCLWIUAM
DATEESTABLISHED/MDYY = DATECVT(CYMD, 'A8YYMD', 'YYMD');
FROMDATE/MDYY = DATECVT(FROM_DATE, 'A8YYMD', 'YYMD');
TODATE/MDYY = DATECVT(TO_DATE, 'A8YYMD', 'YYMD');
PAYMENTDATE/MDYY = DATECVT(PYMT_ENTER, 'A8YYMD', 'YYMD');
END
TABLE FILE FCLWIUAM
PRINT
     PREFIX AS 'Prefix'
     DCN AS 'Debtor ID'
     PGM AS 'Program'
     DATEESTABLISHED AS 'Date Established'
     FROMDATE AS 'From Date'
     TODATE AS 'To Date'
     BEG_BALANCE/P11.2M AS 'Begin Balance'
     WIUAM.CUR_BALANCE/P11.2M AS 'Current Balance'
     CLAIM_STATUS AS 'Claim, Status'
     WIU_STATUS AS 'WIU, Status'
     WIU_ACTION AS 'WIU, Action'
     LEGAL_ACTION AS 'Legal, Action'
     CAUSE_CODE AS 'Cause'
     PAYMENTDATE AS 'Payment Date'
     PYMT_TRANS AS 'Tran Type'
     PYMT_AMT/P11.2M AS 'Payment Amount'
     DEBTOR_COUNTY AS 'Debtor, County'
	 BY WIUAM.PGM NOPRINT
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K OPEN
END
SET COMPOUND=BYTOC
DEFINE FILE FCLWIUAM
DATEESTABLISHED/MDYY = DATECVT(CYMD, 'A8YYMD', 'YYMD');
FROMDATE/MDYY = DATECVT(FROM_DATE, 'A8YYMD', 'YYMD');
TODATE/MDYY = DATECVT(TO_DATE, 'A8YYMD', 'YYMD');
PAYMENTDATE/MDYY = DATECVT(PYMT_ENTER, 'A8YYMD', 'YYMD');
END
TABLE FILE FCLWIUAM
PRINT
     PREFIX AS 'Prefix'
     DCN AS 'Debtor ID'
     PGM AS 'Program'
     DATEESTABLISHED AS 'Date Established'
     FROMDATE AS 'From Date'
     TODATE AS 'To Date'
     BEG_BALANCE/P11.2M AS 'Begin Balance'
     CUR_BALANCE/P11.2M AS 'Current Balance'
     CLAIM_STATUS AS 'Claim, Status'
     WIU_STATUS AS 'WIU, Status'
     WIU_ACTION AS 'WIU, Action'
     LEGAL_ACTION AS 'Legal, Action'
     CAUSE_CODE AS 'Cause'
     PAYMENTDATE AS 'Payment Date'
     PYMT_TRANS AS 'Tran Type'
     PYMT_AMT/P11.2M AS 'Payment Amount'
     DEBTOR_COUNTY AS 'Debtor, County'
BY TOTAL COMPUTE FYPROGTYPE/A26 = 'FY2013-WIU-Program-Type-' || PGM; NOPRINT
-*ON TABLE COLUMN-TOTAL AS 'TOTAL' BEG_BALANCE'

ON TABLE PCHOLD FORMAT EXL2K

This message has been edited. Last edited by: <Kathryn Henning>,

ON TABLE applies to the entire report so instead of using an "ON TABLE TOTAL" you need to use "ON {Your Selected By Field) SUBTOTAL".

I have the report breaking on FYPROGTYPE which puts each program type on separate tabs but when I add the BY field with the BEG_BALANCE I get the error below. I do not want to total on the first by field.

  
BY TOTAL COMPUTE FYPROGTYPE/A26 = 'FY2013-WIU-Program-Type-' || PGM; NOPRINT
BY BEG_BALANCE NOPRINT
ON TABLE BEG_BALANCE SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
0 ERROR AT OR NEAR LINE    140  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC002) A WORD IS NOT RECOGNIZED: BEG_BALANCE
 BYPASSING TO END OF COMMAND
 (FOC3298) ERROR FOUND IN A COMPOUND REPORT
 Compound Report is TERMINATING.....

The columns I am trying to total on each excel tab are the following;
BEG_BALANCE/P11.2M AS 'Begin Balance'
CUR_BALANCE/P11.2M AS 'Current Balance'
PYMT_AMT/P11.2M AS 'Payment Amount'

The suggested resolution did not work. Not sure why this was closed.

quote:

ON TABLE BEG_BALANCE SUBTOTAL

Not what Alex suggested. This should be ON BEG_BALANCE SUBTOTAL.

T

I changed to ON BEG_BALANCE SUBTOTAL and I get the following error

0 NUMBER OF RECORDS IN TABLE= 4502 LINES= 4502
0 ERROR AT OR NEAR LINE 75 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC013) THE 'ON FIELDNAME' FIELD IS NOT A SORT FIELD: SUBTOTAL
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
(FOC3298) ERROR FOUND IN A COMPOUND REPORT
Compound Report is TERMINATING.....
I did not intend on using the BEG_BALANCE as a sort field.

 
BY TOTAL COMPUTE FYPROGTYPE/A26 = 'FY2013-WIU-Program-Type-' || PGM; NOPRINT 
ON TABLE NOTOTAL
ON BEG_BALANCE SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K

I also tried by adding the by field.

  
BY BEG_BALANCE NOPRINT
BY TOTAL COMPUTE FYPROGTYPE/A26 = 'FY2013-WIU-Program-Type-' || PGM; NOPRINT
ON TABLE NOTOTAL
ON BEG_BALANCE SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K

This gave me the message
Unknown error occurred. Agent on reporting server EDACDSS2 may have crashed. Please investigate reporting server log.
Is there no way to do this without a by field?