Can someone please explain to me (abstractly) the different between the PRINT, SUM, and BY commands in the following code? This should be able to help me figure out my problem.

 
TABLE FILE ECP001OUT
SUM
     'ECP001OUT.ECP001OU.SUMCNT' NOPRINT
     'ECP001OUT.ECP001OU.SUMAMT' NOPRINT
     COMPUTE ALCLAB/A50 = 'Total ALC: '|ALC; NOPRINT
     COMPUTE ALSSUMCNT/P11C = SUMCNT; NOPRINT
     COMPUTE ALSSUMAMT/P18.2CM = SUMAMT; NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
SUM
     'ECP001OUT.ECP001OU.SUMCNT' NOPRINT
     'ECP001OUT.ECP001OU.SUMAMT' NOPRINT
     COMPUTE ALC2LAB/A50 = 'Total Cashflow: '|( ALC|ALC2 ); NOPRINT
     COMPUTE ALS2SUMCNT/P11C = SUMCNT; NOPRINT
     COMPUTE ALS2SUMAMT/P18.2CM = SUMAMT; NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
PRINT
     SP1 AS ' '
     'ECP001OUT.ECP001OU.PROCESSOR' AS 'Processing Site'
     'ECP001OUT.ECP001OU.TRANS_CREATION_DATE_STRING' AS 'Transaction Date'
     'ECP001OUT.ECP001OU.SUMMARY_COUNT' AS 'Summary Count'
     'ECP001OUT.ECP001OU.SUMMARY_AMOUNT' AS 'Summary Amount'
     SP1 AS ' '
     TAXDESC_1 NOPRINT     TAX_AMT_1 NOPRINT
     TAXDESC_2 NOPRINT     TAX_AMT_2 NOPRINT
     TAXDESC_3 NOPRINT     TAX_AMT_3 NOPRINT
     TAXDESC_4 NOPRINT     TAX_AMT_4 NOPRINT
     TAXDESC_5 NOPRINT     TAX_AMT_5 NOPRINT
     TAXDESC_7 NOPRINT     TAX_AMT_7 NOPRINT
     TAXDESC_8 NOPRINT     TAX_AMT_8 NOPRINT
	 COMPUTE OTHER_AMT/P18.2CM = ALS2SUMAMT-(TAX_AMT_1+TAX_AMT_2+TAX_AMT_3+TAX_AMT_4+TAX_AMT_5+TAX_AMT_7+TAX_AMT_8); NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.SECT' NOPRINT

 

This message has been edited. Last edited by: Fran Fitzpatrick,

The SUM BY statements aggregate the data at a higher level than the PRINT BY statement. Since the SUM BY fields are NOPRINT, I can surmise they're used in headings or subheadings to show totals at a higher level, once by ALC and another by ALC2...

Okay, I understand that. So how would I do something similar to

COMPUTE OTHER_AMT/P18.2CM = ALS2SUMAMT-(TAX_AMT_1+TAX_AMT_2+TAX_AMT_3+TAX_AMT_4+TAX_AMT_5+TAX_AMT_7+TAX_AMT_8); NOPRINT

except I would like to replace ALS2SUMAMT with an 'ECP001OUT.ECP001OU.SUMAMT' with the same BY statements as the PRINT statement.

I tried doing the adding the following before the PRINT, which actually works and gives me the result that I was looking for, but it totally messes up the formatting of the final report, which I don't know why because it has all NOPRINT statements.

SUM
     'ECP001OUT.ECP001OU.SUMAMT' NOPRINT
     COMPUTE OTHERSUMAMT/P18.2CM = SUMAMT; NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT

Can anyone help? Does that make sense? (whenever I try to do this it isn't working)

This message has been edited. Last edited by: Fran Fitzpatrick,

Fran,

Put ALL the code between the code tags...

Tom,

The entire report is 2780 lines long.

Then do it step-by-step:

  

-* GOTO STEP2

-STEP1
TABLE FILE ECP001OUT
SUM
     'ECP001OUT.ECP001OU.SUMCNT'  
     'ECP001OUT.ECP001OU.SUMAMT'  
     COMPUTE ALCLAB/A50 = 'Total ALC: '|ALC; 
     COMPUTE ALSSUMCNT/P11C = SUMCNT;       
     COMPUTE ALSSUMAMT/P18.2CM = SUMAMT; 
 BY 'ECP001OUT.ECP001OU.ALC' 
END
-EXIT

Are these numbers correct?
Yes, take out the the commented GOTO and test STEP2 - No, why?

-STEP2
TABLE FILE ECP001OUT
SUM
     'ECP001OUT.ECP001OU.SUMCNT' 
     'ECP001OUT.ECP001OU.SUMAMT' 
     COMPUTE ALC2LAB/A50 = 'Total Cashflow: '|( ALC|ALC2 ); 
     COMPUTE ALS2SUMCNT/P11C = SUMCNT; 
     COMPUTE ALS2SUMAMT/P18.2CM = SUMAMT;
BY 'ECP001OUT.ECP001OU.ALC'  
BY 'ECP001OUT.ECP001OU.ALC2' 
END
-EXIT

Make sure the data is correct for these 2 steps, then get back to us...

I've confirmed that the values are correct. I do not want to touch those values. I actually want to perform a COMPUTE on a different value...

Currently, the value does the operation on ALS2SUMAMT:

COMPUTE OTHER_AMT/P18.2CM = ALS2SUMAMT-(TAX_AMT_1+TAX_AMT_2+TAX_AMT_3+TAX_AMT_4+TAX_AMT_5+TAX_AMT_7+TAX_AMT_8); NOPRINT

Instead, I want to change ALS2SUMAMT to the value of 'ECP001OUT.ECP001OU.SUMAMT' with the following BY's:

BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT

Then make that STEP3, with a SUM?

I've done the following, but it throws off the formatting for the entire report:

 
SUM
     'ECP001OUT.ECP001OU.SUMAMT' NOPRINT
     COMPUTE OTHERSUMAMT/P18.2CM = SUMAMT; NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT
PRINT
     SP1 AS ' '
     'ECP001OUT.ECP001OU.PROCESSOR' AS 'Processing Site'
     'ECP001OUT.ECP001OU.TRANS_CREATION_DATE_STRING' AS 'Transaction Date'
     'ECP001OUT.ECP001OU.SUMMARY_COUNT' AS 'Summary Count'
     'ECP001OUT.ECP001OU.SUMMARY_AMOUNT' AS 'Summary Amount'
     SP1 AS ' '
     TAXDESC_1 NOPRINT     TAX_AMT_1 NOPRINT
     TAXDESC_2 NOPRINT     TAX_AMT_2 NOPRINT
     TAXDESC_3 NOPRINT     TAX_AMT_3 NOPRINT
     TAXDESC_4 NOPRINT     TAX_AMT_4 NOPRINT
     TAXDESC_5 NOPRINT     TAX_AMT_5 NOPRINT
     TAXDESC_7 NOPRINT     TAX_AMT_7 NOPRINT
     TAXDESC_8 NOPRINT     TAX_AMT_8 NOPRINT
	 COMPUTE OTHER_AMT/P18.2CM = OTHERSUMAMT-(TAX_AMT_1+TAX_AMT_2+TAX_AMT_3+TAX_AMT_4+TAX_AMT_5+TAX_AMT_7+TAX_AMT_8); NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.SECT' NOPRINT
 

quote:

I've done the following, but it throws off the formatting for the entire report:

What does that mean? Alignment of columns, column formats, etc...

It looks like it added a new, blank column before the first column. Therefore shifting columns to the right and even wrapping some TAX_AMT values to the next line.

Well you have SP1 (space) in Column 1???

That space was there prior to my addition of the OTHERSUMAMT. If I comment it out, it doesn't work.

Fran, it's very difficult to guess. If the code is not doing what you want, maybe change to MATCH functionality; I really have no clue...

  
-STEP1
TABLE FILE ECP001OUT
SUM
     COMPUTE ALCLAB/A50 = 'Total ALC: '|ALC; 
     COMPUTE ALSSUMCNT/P11C = SUMCNT;       
     COMPUTE ALSSUMAMT/P18.2CM = SUMAMT; 
 BY ALC
  ON TABLE HOLD AS HOLD1
END
-RUN

-STEP2
TABLE FILE ECP001OUT
SUM
     COMPUTE ALC2LAB/A50 = 'Total Cashflow: '|( ALC|ALC2 ); 
     COMPUTE ALS2SUMCNT/P11C = SUMCNT; 
     COMPUTE ALS2SUMAMT/P18.2CM = SUMAMT;
BY ALC  
BY ALC2 
  ON TABLE HOLD AS HOLD2
END
-RUN

-STEP3
MATCH FILE HOLD1
PRINT
   ALCLAB
   ALSSUMCNT
   ALSSUMAMT
 BY ALC
RUN
FILE HOLD2
PRINT
   ALC2LAB
   ALS2SUMCNT
   ALS2SUMAMT
 BY ALC
 BY ALC2
  AFTER MATCH HOLD AS HOLD3 OLD-OR-NEW
END
-RUN

JOIN LEFT_OUTER ALC IN HOLD3 TO ALL ALC IN ECP001OUT AS J1
-RUN

DEFINE FILE HOLD3
  SP1/A1 = ' ';
END
TABLE FILE HOLD3
PRINT
     SP1 AS ' '
     'ECP001OUT.ECP001OU.PROCESSOR' AS 'Processing Site'
     'ECP001OUT.ECP001OU.TRANS_CREATION_DATE_STRING' AS 'Transaction Date'
     'ECP001OUT.ECP001OU.SUMMARY_COUNT' AS 'Summary Count'
     'ECP001OUT.ECP001OU.SUMMARY_AMOUNT' AS 'Summary Amount'
     SP1 AS ' '
     TAXDESC_1 NOPRINT     TAX_AMT_1 NOPRINT
     TAXDESC_2 NOPRINT     TAX_AMT_2 NOPRINT
     TAXDESC_3 NOPRINT     TAX_AMT_3 NOPRINT
     TAXDESC_4 NOPRINT     TAX_AMT_4 NOPRINT
     TAXDESC_5 NOPRINT     TAX_AMT_5 NOPRINT
     TAXDESC_7 NOPRINT     TAX_AMT_7 NOPRINT
     TAXDESC_8 NOPRINT     TAX_AMT_8 NOPRINT
	 COMPUTE OTHER_AMT/P18.2CM = OTHERSUMAMT-(TAX_AMT_1+TAX_AMT_2+TAX_AMT_3+TAX_AMT_4+TAX_AMT_5+TAX_AMT_7+TAX_AMT_8); NOPRINT
BY 'ECP001OUT.ECP001OU.ALC' NOPRINT
BY 'ECP001OUT.ECP001OU.ALC2' NOPRINT
BY 'ECP001OUT.ECP001OU.DT215' NOPRINT
BY 'ECP001OUT.ECP001OU.DEP_TKT_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.DEPOSIT_TICKET_NO' NOPRINT
BY 'ECP001OUT.ECP001OU.SECT' NOPRINT

Outside of this, I really have no clue, difficult to work with bits-and-pieces...

Tom

I would like to give an update of what the problem was...

By adding a new SUM block, I inadvertently inserted two additional columns into the report. This therefore through off the formatting by two columns.

With the help of Tom Flynn (thanks again!) I've just increased all of my column numbers by two.

For example,

N17

became

N19

throughout the report.