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,

Have you tried:

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

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)

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

My suggestion is to try removing the RECOMPUTE from the BY and add it in as an ON.

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:'