IBI WebFOCUS - Calculation or re- computation at Subtotal and Subfoot using RECAP

In my Recent project, I had a requirement where I had to show subtotal based on the selection from launch page. And the Sub Total had both total as well as %'s to display. The percentage was nothing but for example. 100*Sum(Column A)/Sum(Column B). I thought to share this with all of you since it can be reused for similar requirement and can help others to save lot of time. 

 This situation could have handled by using RECOMPUTE (ON TABLE RECOMPUTE "OR" ON SORT_COLUMN  RECOMPUTE) concept. However there were some challenges with that. 
1) First issue was when we do RECOMPUTE all the numeric columns will get RECOMPUTEd. So if RECOMPUTE is needed to only selected columns, all other should be changed to alpha format. That is not a feasible approach according to me. 
2) If all alpha columns in the report are not sorted, Sub Total row will be coming in 2 rows. That is another issue.

 So to avoid RECOMPUTE and use SUBFOOT (which I prefer most because of it's flexibility), We can use RECAP. We can use the RECAP and COMPUTE commands to create subtotal values in a calculation. The subtotal values are not displayed. Only the result of the calculation is shown on the report. 
There are certain points we should take care of while using RECAP:
1) RECAP uses the current value of the named sort field, the current subtotal values of any computational fields that appear as display fields, or the last value for alphanumeric fields. 
2) The field names in the expression must be fields that appear on the report. That is, they must be display fields or sort control fields. 
3) Each RECAP value displays on a separate line. However, if the request contains a RECAP command and SUBFOOT text, the RECAP value displays only in the SUBFOOT text and must be specified in the text using a spot marker. 
4) The calculations in a RECAP or COMPUTE can appear anywhere under the control break, along with any text. 
5) In an ON phrase, a COMPUTE command is the same as a RECAP command. 
6) The word RECAP may not be specified more than seven times. However, more than seven RECAP calculations are permitted.  

 Example:
For Grand Total we can COMPUTE the Total Percentage inside final Table File:

 COMPUTE AVG_ONTIMEP/P20.2% =100*TOT.RCVONTIME/TOT.SHIPQTY1; NOPRINT
COMPUTE AVG_CURDTP/P20.2% =100*TOT.RECTOCURDT/TOT.SHIPQTY1; NOPRINT

 And The calculated columns can be used in SubFoot as given:
ON TABLE SUBFOOT
"Grand Totals:  <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <ST.SHIPQTY1<ST.RCVONTIME_T<AVG_ONTIMEP_A<ST.RECTOCURDT_T<AVG_CURDTP_A <+0> <+0> <+0> <+0> "

 *Where one <+0> represent after how many columns your total or % will print. You need to manually adjust that. It will not consider automatically just below the column like in Subtotal and RECOMPUTE.

 For Subtotals you have to use RECAP to get the desired result:
In the below example, we have 3 sort by's and subtotals might or might not be selected(from launch page) to display in the report. 

 -IF &SUBT1 EQ '' THEN GOTO SKP_SUBT1;
ON &SORT_BY1_T RECAP
RCVONTIME_T1/D20C = RCVONTIME;
SHIPQTY1_T1/D20C = SHIPQTY1;
RECTOCURDT_T1/D20C = RECTOCURDT;
SUBT_ONTIMEP_T1/P20.2% =IF (100*RCVONTIME_T1/SHIPQTY1_T1) GE 100 THEN 100 ELSE 100*RCVONTIME_T1/SHIPQTY1_T1;
SUBT_CURDTP_T1/P20.2% =100*RECTOCURDT_T1/SHIPQTY1_T1; 
SUBT_ONTIMEP_A_T1/A25 = PTOA(SUBT_ONTIMEP_T1, '(P20.2%)', SUBT_ONTIMEP_A_T1); 
SUBT_CURDTP_A_T1/A25 = PTOA(SUBT_CURDTP_T1, '(P20.2%)', SUBT_CURDTP_A_T1);  
ON &SORT_BY1_T.EVAL SUBFOOT "<SORTBY1 <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <SHIPQTY1_T1<RCVONTIME_T1<SUBT_ONTIMEP_A_T1<RECTOCURDT_T1<SUBT_CURDTP_A_T1<+0> <+0> <+0> <+0> <+0> "
-SKP_SUBT1

 -IF &SUBT2 EQ '' THEN GOTO SKP_SUBT2;
ON &SORT_BY2_T RECAP
RCVONTIME_T2/D20C = RCVONTIME;
SHIPQTY1_T2/D20C = SHIPQTY1;
RECTOCURDT_T2/D20C = RECTOCURDT;
SUBT_ONTIMEP_T2/P20.2% =IF (100*RCVONTIME_T2/SHIPQTY1_T2) GE 100 THEN 100 ELSE 100*RCVONTIME_T2/SHIPQTY1_T2;
SUBT_CURDTP_T2/P20.2% =100*RECTOCURDT_T2/SHIPQTY1_T2; 
SUBT_ONTIMEP_A_T2/A25 = PTOA(SUBT_ONTIMEP_T2, '(P20.2%)', SUBT_ONTIMEP_A_T2); 
SUBT_CURDTP_A_T2/A25 = PTOA(SUBT_CURDTP_T2, '(P20.2%)', SUBT_CURDTP_A_T2);  
ON &SORT_BY2_T.EVAL SUBFOOT "<SORTBY2 <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <SHIPQTY1_T2<RCVONTIME_T2<SUBT_ONTIMEP_A_T2<RECTOCURDT_T2<SUBT_CURDTP_A_T2<+0> <+0> <+0> <+0> <+0> "
-SKP_SUBT2

 -IF &SUBT3 EQ '' THEN GOTO SKP_SUBT3;
ON &SORT_BY3_T RECAP
RCVONTIME_T3/D20C = RCVONTIME;
SHIPQTY1_T3/D20C = SHIPQTY1;
RECTOCURDT_T3/D20C = RECTOCURDT;
SUBT_ONTIMEP_T3/P20.2% =IF (100*RCVONTIME_T3/SHIPQTY1_T3) GE 100 THEN 100 ELSE 100*RCVONTIME_T3/SHIPQTY1_T3;
SUBT_CURDTP_T3/P20.2% =100*RECTOCURDT_T3/SHIPQTY1_T3; 
SUBT_ONTIMEP_A_T3/A25 = PTOA(SUBT_ONTIMEP_T3, '(P20.2%)', SUBT_ONTIMEP_A_T3); 
SUBT_CURDTP_A_T3/A25 = PTOA(SUBT_CURDTP_T3, '(P20.2%)', SUBT_CURDTP_A_T3);  
ON &SORT_BY3_T.EVAL SUBFOOT "<SORTBY3 <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <SHIPQTY1_T3<RCVONTIME_T3<SUBT_ONTIMEP_A_T3<RECTOCURDT_T3<SUBT_CURDTP_A_T3<+0> <+0> <+0> <+0> <+0> "
-SKP_SUBT3

 Where &SORT_BY1_T, &SORT_BY2_T and &SORT_BY3_T are the variables holding the by fields on which subtotal is required. 
The Recap code and Subfoot will not appear in the report if Subtotal on the sort by's are not selected from launch page. All are handled dynamically.

 Happy Reporting!!!

Comments

Post a Comment

Popular posts from this blog

IBI WebFOCUS - Functions available and syntax to use

Using Functions Functions operate on one or more arguments and return a single value. The returned value can be stored in a field, assigned to a Dialogue Manager variable, used in a calculation or other processing, or used in a selection or validation test. Functions provide a convenient way to perform certain calculations and manipulations. There are two types of functions: Internal functions. Built into the WebFOCUS language, requiring no extra work to access or use. The following reporting and Maintain functions are internal functions. You can not replace any of these internal functions with your own functions of the same name. All other functions are external. ABS ASIS DMY, MDY, and YMD DECODE EDIT FIND LAST LOG LOOKUP MAX and MIN SQRT All Maintain-specific functions External functions. Stored in an external library that must be accessed. When invoking these functions, an argument specifying the output fie
Read more

IBI WebFOCUS - Testing and Debugging in WebFOCUS codes

You can debug a Web FOCUS application by querying your environment to display information such as file status, release, server information, and joins, as well as by identifying files you are using. Other debugging methods are available in Web FOCUS, including specialized Dialogue Manager Variables and traces performed from the Web Console. Debugging Your Application with Query Commands You can use query commands to display information about your metadata, data sources, language environment, Developer Studio, and Web FOCUS. This is useful for determining any aspects of your application that may be preventing the proper behavior of your application. You can query your environment with a query command executed in Developer Studio or Web FOCUS. The result of a query command is returned in your browser window or as a comment in the HTML file. Use the applicable Web browser functions to view the HTML comments (for example, View Source in Microsoft Internet Explorer). How to Issue
Read more

IBI WebFOCUS - Difference (Preference) between Join and Match:

For most of the reports in our WebFOCUS reporting world, we usually pull data from multiple tables and and generate the report. We all know that we can either Join Data Sources(JOIN) or Merge Data Sources(MATCH) to create report from multiple tables. Which one to choose from JOIN and MATCH? Now the question is what is the diffrence between the two and which one to choose from the 2 option. To explain this let's understand how does they process. JOIN: Basically JOIN is temporary, virtual connection between 2 or more physical or logical sources. Data in the first data source (host file) determines which records are retrieved from cross-reference file. When a record from the host file of a one-to-many join has no corresponding record(s) in the cross-reference file, the record is automatically excluded from the report output.  To control this, you can SET ALL to ON, in which case records from the host file, without corresponding records in the cross-reference file, will be disp
Read more