Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Having issues with Percentage Subtotals/Totals recomputing
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Having issues with Percentage Subtotals/Totals recomputing
 Login/Join
 
Gold member
posted
Hi all,

I have a report where I have a computed percentage field that when multiple weeks are selected it's adding the percentage rather than recalculating the %. I have the % as a RECOMPUTE, please see code below:

 
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
DEFINE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
 TYPES/A101=IF HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY EQ 'AT RISK' OR 'FIXED PRICE' OR 'T&|M' THEN 'BILLABLE' ELSE IF HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY NE 'AT RISK' OR 'FIXED PRICE' OR 'T&|M' OR 'CUSTOMER SUPPORT' OR
'LEAVE' OR 'OTHER' THEN 'NON-BILLABLE' ELSE '' ;
END

-SET &TOTAL_PROJ_USD='COMPUTE Percent_Utilized/D12.2%=( HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ENTERED_HRS/( (CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.WEEK_NUM) * DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS ) ) * 100 ;';


TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM &TOTAL_PROJ_USD
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE AS 'CATEGORY' RECOMPUTE  AS 'Subtotal:'
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE AS 'EMPLOYEE'
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY AS 'COUNTRY'
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY AS 'BUSINESS UNIT'
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT AS 'DEPARTMENT'
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE EQ 'CUSTOMER SUPPORT' OR 'NON-SERVICES' OR 'SERVICES BILLABLE' OR 'SERVICES NON BILLABLE';
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.STARTDATE GE &STARTDATE.(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.STARTDATE IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A15,SORT=ASCENDING).STARTDATE:.QUOTEDSTRING;
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EOW_DT LE &EOW_DT.(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EOW_DT IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A15,SORT=ASCENDING).EOW_DT:.QUOTEDSTRING;
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE EQ &ORG_TYPE.(OR(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A21,SORT=ASCENDING)).ORG_TYPE:.;
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY EQ &COUNTRY.(OR(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A6V,SORT=ASCENDING)).COUNTRY:.;
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY EQ &BU_INDUSTRY.(OR(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A27,SORT=ASCENDING)).BU_INDUSTRY:.;
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE EQ &EMPLOYEE.(FIND HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE IN si_bi/hyp_tables/HYP_CP_LABOR_UTIL_ACTUAL |FORMAT=A101,SORT=ASCENDING).EMPLOYEE:.QUOTEDSTRING;
ACROSS HIGHEST TYPES AS ' '
ACROSS HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY AS ' ' RECOMPUTE  AS 'Subtotal:'
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel,XLSX>).Select an output type.
ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 99999
ON TABLE SET GRWIDTH 1
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *

INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENBlue_Light2.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $

ENDSTYLE
END

-RUN

 

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 73 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Have you tried:
  
ON HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE RECOMPUTE  AS 'Subtotal:'


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Zach

To add to what Babak is suggesting you must have hand coded the RECOMPUTE yourself because there is no way to have that code generated by the GUI for a BY or an ACROSS. In order to generate the code Babak suggested you select the field and choose Recompute and it only enables that option on a BY (Sort Down)


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1571 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
IBI REPORT

Thanks for the responses Chuck and Babak.

I attached the report to this thread of how the users would like it to look. We want a subtotal at the Category level and one and the UTIL_Category which is an across. Are you saying that's not possible Chuck?

Thank you!
Zack


WebFOCUS 8
Windows, All Outputs
 
Posts: 73 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
My suggestion is to try removing the RECOMPUTE from the BY and add it in as an ON.


WebFOCUS 8206, Unix, Windows
 
Posts: 1660 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
zach

From App Studio right mouse click on the Sort Across field and choose ACROSS-TOTAL, that may be what you want for that. Babak is right about the BY remove the RECOMPUTE from the BY statement and add this one.

ON HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE RECOMPUTE AS 'Subtotal:'


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1571 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Having issues with Percentage Subtotals/Totals recomputing

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.