Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Explanation of SUM/PRINT/BY in following code?
 Login/Join
 
Member
posted
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, 2010Report This Post
Expert
posted 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, 2005Report This Post
Member
posted 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, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted Hide Post
Tom,

The entire report is 2780 lines long. Frowner


version 7.6.10 on windows
all available formats
 
Posts: 18 | Registered: September 20, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted 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, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted 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, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted 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, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted Hide Post
That space was there prior to my addition of the OTHERSUMAMT. If I comment it out, it doesn't work. Frowner


version 7.6.10 on windows
all available formats
 
Posts: 18 | Registered: September 20, 2010Report This Post
Expert
posted 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, 2006Report This Post
Member
posted 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, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders