Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Explanation of SUM/PRINT/BY in following code?

 Go Search Notify Tools
 [SOLVED] Explanation of SUM/PRINT/BY in following code?
Member
 posted September 27, 2010 01:24 PM
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,

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 02:20 PM Hide Post
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...

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

 Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005 IP
Member
 posted September 27, 2010 02:28 PM Hide Post
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,

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 02:49 PM Hide Post
Fran,

Put ALL the code between the code tags...

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 27, 2010 02:56 PM Hide Post
Tom,

The entire report is 2780 lines long.

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 03:03 PM Hide Post
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...

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 27, 2010 03:13 PM Hide Post
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
```

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 03:21 PM Hide Post
Then make that STEP3, with a SUM?

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 27, 2010 03:23 PM Hide Post
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
```

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 03:26 PM Hide Post
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...

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 27, 2010 03:29 PM Hide Post
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.

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 03:35 PM Hide Post
Well you have SP1 (space) in Column 1???

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 27, 2010 03:37 PM Hide Post
That space was there prior to my addition of the OTHERSUMAMT. If I comment it out, it doesn't work.

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP
Expert
 posted September 27, 2010 03:45 PM Hide Post
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

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

 Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006 IP
Member
 posted September 28, 2010 03:01 PM Hide Post
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.

version 7.6.10 on windows
all available formats

 Posts: 18 | Registered: September 20, 2010 IP