Hi all,

I'm having an issue with passing a variable from a hold file to a table file.

The first hold file is counting distinct timesheet dates saving it to the variable 'TOTAL_WEEKS'. I need this field to be at the department grain and filtered by S_TS_TYPE_CD EQ 'R'.

The second hold file is doing the same but without the filter of S_TS_TYPE_CD.

Then I try adding &TOTAL_WEEKS in the table file and using a READFILE to get the variable from the hold file. When I try to get the variable from the hold file it isn't displaying correctly. It's not passing the &TOTAL_WEEKS correctly to the table file and I don't know why. Is there a better way to do this? I need the TOTAL_WEEKS filtered and calculated at a different level than the report I want to pass it to.

Please feel free to ask any questions and give any critiques as I'm at a loss.

Below is the my fex.

Thank you in advance!

 -DEFAULTH &TOTAL_WEEKS=_FOC_NULL;
-DEFAULTH &TOTAL_OTBEGINS=_FOC_NULL;
-*COMPONENT=Define_hyp_cp_labor_util_actual
DEFINE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
 TYPES/A29=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 'OTHER';
END
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
-SET &ECHO=ALL;
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';

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT AS 'TOTAL_WEEKS'
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD
-*WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID EQ '008706';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD FORMAT BINARY
-*ON TABLE PCHOLD FORMAT AHTML
END
-RUN

-READFILE foccache/MYHOLD

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); 
-*COMPUTE TOTAL_WEEKS/D12.2=CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT ;
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD
WHERE  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
-*WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID EQ '008706';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD1 FORMAT BINARY
-*ON TABLE PCHOLD FORMAT AHTML
END
-RUN

-READFILE foccache/MYHOLD1

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM
COMPUTE SORT1/A1='1';
COMPUTE WEEKS/D12=(&TOTAL_WEEKS);
DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS
HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ENTERED_HRS
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
BY LOWEST TYPES
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY
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;
-*ON TABLE HOLD AS MAINDATA FORMAT ALPHA
ON TABLE PCHOLD FORMAT AHTML
END 

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

zcbillions

Put this at the beginning of your fex.

SET ASNAMES = ON

That will make the hold file field name TOTAL_WEEKS and the & variable will be &TOTAL_WEEKS when you do the -READFILE

Hi Chuck,

Thank you for this.

Unfortunately, it didn't fix my issue but I did add it to the fex.

Since you have multiple BY fields in your TABLE FILE where you create the TOTAL_WEEKS field, it is possible that you have several rows in the hold file MYHOLD ?
If it's the case, it may be the source of your problem

How many rows result after the step ?

ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
-SET &ECHO=ALL;
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/A29=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 'OTHER';
END

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT AS 'TOTAL_WEEKS'
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD
-*WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID EQ '008706';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD FORMAT BINARY
-*ON TABLE PCHOLD FORMAT AHTML
END
-RUN

-TYPE &LINES

Also, no need to hold it in foccache. The file will exist as long as the fex (session) is running, then the file will be "deleted" from the memory

Hi Martin,

Thanks for your response. I'm not sure why the amount of rows would be a problem in the hold file..

It came back with 415 lines.

zcbillions

-READFILE reads 1 record at a time not all 415 so you are just getting the first record. If that is all you want great, otherwise you need a loop to retrieve all the records.

Add ?FF MYHOLD before the -READFILE to see the field names in that HOLD file. You should see TOTAL_WEEKS

OHHHHHHHHHHHH thank you so much Chuck! I did not realize that READFILE only read one record.

Thank you so much for the clarification!

quote:

Originally posted by zcbillions:
OHHHHHHHHHHHH thank you so much Chuck! I did not realize that READFILE only read one record.

Thank you so much for the clarification!

A little clarification : READFILE reads one record AT THE TIME !! It may read all your data using a loop but one record at the time.

Thank you Martin!

I am using a loop right now, but haven't used a loop before in WebFocus so this is a good assignment for me. I'm having issues with it. I'm thinking I need multiple loops? I have multiple hold files using calculations from the previous hold file. I have 2 loops providing the next hold file values. I'm not sure I'm doing this correctly?

Here's my code:

 
-DEFAULTH &TOTAL_WEEKS=_FOC_NULL;
-*-DEFAULTH &TOTAL_OTBEGINS=_FOC_NULL;
-DEFAULTH &TOTALOTBEGINS=TOTALOTBEGINS;
-*COMPONENT=Define_hyp_cp_labor_util_actual
DEFINE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
 TYPES/A29=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 'OTHER';
END
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
-SET &ECHO=ALL;
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';

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'
CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT AS 'TOTAL_WEEKS'
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD FORMAT BINARY
END
-RUN

-READFILE foccache/MYHOLD

-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &LINES
-READFILE foccache/MYHOLD
-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-SET &EMPL_ID.&I = &EMPL_ID ;
-SET &DEPARTMENT.&I = &DEPARTMENT ;
-*-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-*-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EOW_DT.&I = &EOW_DT ;
-TYPE &TOTAL_WEEKS.&I &EMPL_ID.&I &DEPARTMENT.&I
-:ENDREPEAT1 


TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( &TOTAL_WEEKS * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD1 FORMAT BINARY
END
-RUN


-REPEAT :ENDREPEAT2 FOR &I FROM 1 TO &LINES
-READFILE foccache/MYHOLD1
-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EMPL_ID.&I = &EMPL_ID ;
-*-SET &DEPARTMENT.&I = &DEPARTMENT ;
-*-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-*-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EOW_DT.&I = &EOW_DT ;
-TYPE  &TOTALOTBEGINS.&I
-:ENDREPEAT2 


TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM
COMPUTE SORT1/A1='1';
DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS
HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ENTERED_HRS
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
BY LOWEST TYPES
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY
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;
ON TABLE HOLD AS MAINDATA FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT AHTML
END
-************************************************************
TABLE FILE MAINDATA
SUM FST.OT_BEGINS
COMPUTE TOTSHOURS/P20.2 = FST.OT_BEGINS;
BY ORG_TYPE
BY EMPLOYEE
ON TABLE HOLD AS EMP_HRS1
END
TABLE FILE EMP_HRS1
SUM TOTSHOURS
BY ORG_TYPE
ON TABLE HOLD AS SUB_THOURS
END
-**************

FILEDEF OTYPE_SUM DISK maindata.ftm (APPEND

-************************************************************
JOIN ORG_TYPE IN MAINDATA TO ORG_TYPE IN SUB_THOURS AS J1

DEFINE FILE MAINDATA
EMP/A101=' ';
CNTRY/A6V=' ';
BU_IND/A27=' ';
DEPT/A10V=' ';
END
TABLE FILE MAINDATA
SUM

COMPUTE SORT1/A1='2';
    FST.TOTSHOURS
    ENTERED_HRS
BY ORG_TYPE
BY EMP
BY CNTRY
BY BU_IND
BY DEPT
BY TYPES
BY  UTIL_CATEGORY
ON TABLE HOLD AS OTYPE_SUM FORMAT ALPHA
END

-***********************************************************88
DEFINE FILE MAINDATA
CATEGORY/A50 = IF SORT1 EQ '2' THEN 'TOTAL '| ORG_TYPE ELSE ORG_TYPE;
END


TABLE FILE MAINDATA
SUM
COMPUTE PERCENTAGE/D12.2%=( ENTERED_HRS/(&TOTALOTBEGINS)) * 100 ;
COMPUTE JUST_WEEKS/D12=( &TOTAL_WEEKS) ;
BY ORG_TYPE NOPRINT SKIP-LINE
BY SORT1 NOPRINT
BY CATEGORY
BY EMPLOYEE AS 'EMPLOYEE'
BY COUNTRY AS 'COUNTRY'
BY BU_INDUSTRY AS 'BUSINESS UNIT'
BY DEPARTMENT AS 'DEPARTMENT'
ACROSS LOWEST TYPES AS ''
ACROSS UTIL_CATEGORY  AS '' SUBTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel,XLSX>).Select an output type.
ON TABLE ROW-TOTAL
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,$
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, HFREEZE=ON, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $

ENDSTYLE
END

-RUN

 

From what I can see

1- You probably have no needs to use foccache

2- From the below code you probably starts at the second available value since you first do a READFILE then start your loop with another READFILE. So, the first data record read is skipped from the loop.
May not be a problem if it's the purpose you are looking for.

-READFILE foccache/MYHOLD

-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &LINES
-READFILE foccache/MYHOLD
-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-SET &EMPL_ID.&I = &EMPL_ID ;
-SET &DEPARTMENT.&I = &DEPARTMENT ;
-*-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-*-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EOW_DT.&I = &EOW_DT ;
-TYPE &TOTAL_WEEKS.&I &EMPL_ID.&I &DEPARTMENT.&I
-:ENDREPEAT1 

3- How this is supposed to work ? You want to use the first READFILE as for the &TOTAL_WEEKS assignation ?

SUM COMPUTE TOTAL_OTBEGINS/D12.2=( &TOTAL_WEEKS * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'

4- Why a second loop since from the code used to create MYHOLD1 it will result in only one record ? You don't have any BY field and only one SUM so, the result will be one record

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( &TOTAL_WEEKS * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD1 FORMAT BINARY
END
-RUN

-REPEAT :ENDREPEAT2 FOR &I FROM 1 TO &LINES
-READFILE foccache/MYHOLD1
-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EMPL_ID.&I = &EMPL_ID ;
-*-SET &DEPARTMENT.&I = &DEPARTMENT ;
-*-SET &TOTAL_WEEKS.&I = &TOTAL_WEEKS ;
-*-SET &TOTALOTBEGINS.&I = &TOTALOTBEGINS ;
-*-SET &EOW_DT.&I = &EOW_DT ;
-TYPE  &TOTALOTBEGINS.&I
-:ENDREPEAT2 

5- More than anything -- Why are you creating loops when you are not using any of the assigned variables from these loops ?

Please clarify what you are attempting to do.

You may also have to consider others solutions.
Since your first data extraction returns multiple values by EMPL_ID and DEPARTMENT you may thing of using a JOIN or MATCH (or even MORE) based on EMPL_ID and DEPARTMENT key and depending of your goal.

From what I can see, you don't need any loop. You just need to have your first data extraction returns only one record (no BY fields) to have the total weeks.
Your issue seems to be the use of BY fields that creates multiple output rows.

Hi Martin,

Thank you so much for looking at my code! This is an utilization report by employee.

Let me see if i can make some clarifications that make sense to you. Here's an image of the report. https://imgur.com/gallery/BwZtijj I have the report working besides taking into account termed employees, hired employees, and employees who move departments.

On the first hold file:

 
TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'
CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT AS 'TOTAL_WEEKS'
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD FORMAT BINARY
END
-RUN
 

Here, I'm just trying to get the TOTAL_WEEKS variable. I was messing around with the COMPUTE, (COMPUTE TOTAL_OTBEGINS/D12.2=( CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS') That shouldn't be in the first hold file, only the second. I was seeing if I can remove the second hold file and do both in 1 hold file.

Anyways, in this first hold file I want to get the total number of weeks by counting distinct the timesheet date by the employee id and department where timesheet type is EQ to R. So this is taking into account employees and their hours on a timesheet. I need this due to employees getting termed and/or hired and if they also switch departments. Some employees number of weeks worked will be different base on that and in different departments, so that's why I need the BYs and the separate hold file to just calculate the number of weeks for that grain.

The second hold file:

 
TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2=( &TOTAL_WEEKS * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS); AS 'TOTALOTBEGINS'
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
-*BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS foccache/MYHOLD1 FORMAT BINARY
END
-RUN
 

This hold file is then taking the TOTAL_WEEKS and multiplying it by the OT_BEGINS. OT_BEGINS is the number of hours every employee is suppose to work per week.

In the fex, lastly, I'm setting up the MAINDATA table for the report. That all is working correctly. from what I can tell. I'm just having issues with the 2 hold files.

Also, I'm not too good at JOINING in a fex file, the syntax gets me. if you think I should be doing that.

Lastly, just to reiterate, I do need the multiple output rows by emp and dept because not all employees hours will be the same.

Please let me know if I need to clarify or add and info.

Thank you again!

According to what you stated, you need to have the TOTAL_WEEKS per Emp and Dept.

As per my understanding OT_BEGINS is the same no matter the employee ? Because you still don't have any BY field so I suppose the this value is the same for each employees.

Pretending that each employee is working the same number of hours.
So, it should be at first step as per below

SET ASNAMES = ON
TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS AS 'MINTIME'
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS MYHOLD1
END
-RUN

Then use this variable in the first TABLE FILE as bout below

-READFILE MYHOLD1
-RUN

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2 = CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * &MINTIME;
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPL_ID
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS MYHOLD
END
-RUN

This should result in each Emp/Dept with its own TOTAL_OTBEGINS value

But it's all confusing because you are stating that "not all employees hours will be the same" but you also say that "OT_BEGINS is the number of hours every employee is suppose to work per week"
When you say "every employee" is it "ALL employee" (e.g. ALL emp should work 40hrs a week) or "EACH employee" (e.g. EACH employee is supposed to work XYZ hours per week where each emp number of hours may differ)
And you are not including any BY field in the second TABLE FILE that can make this unique so it will result in only one row no matter the emp/dept.

What I can suggest is to
1- Extract the data that you need without any COMPUTE or calculation. Just the raw data : the number of weeks and number of hours per employees. You may have to do in two steps and/or two hold files.
2- Then once you're happy with your data, JOIN, MATCH or MORE them to then be able to perform your calculation. Or if one of the hold file result in only one row, you can then use the READFILE as per above sample code

quote:

But it's all confusing because you are stating that "not all employees hours will be the same" but you also say that "OT_BEGINS is the number of hours every employee is suppose to work per week"
When you say "every employee" is it "ALL employee" (e.g. ALL emp should work 40hrs a week) or "EACH employee" (e.g. EACH employee is supposed to work XYZ hours per week where each emp number of hours may differ)
And you are not including any BY field in the second TABLE FILE that can make this unique so it will result in only one row no matter the emp/dept.

You called it Martin. I do need the BYs for the OT_BEGINS field. The data is skewed.

When I add the BYs it's separating the data to multiple lines and the READFILE is only getting the first record. I tried to put in a loop in but I'm not sure I'm doing it correctly. Here's my code currently:

 

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS AS 'MINTIME'
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
-*WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS MYHOLD1
END
-RUN

-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &LINES
?FF MYHOLD1
-READFILE MYHOLD1
-SET &MINTIME.&I = &MINTIME ;
-SET &DEPARTMENT.&I = &DEPARTMENT ;
-SET &EMPLOYEE.&I = &EMPLOYEE ;
-TYPE  &MINTIME.&I &DEPARTMENT.&I &EMPLOYEE.I
-:ENDREPEAT1

-RUN

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2 = CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * &MINTIME;
HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ENTERED_HRS
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS MYHOLD
END
-RUN 

Is there something I'm missing or not doing right?

Thank you!

Certainly not the good way.
You are looping to read and create multiple variable which is ok but you are not using them anywhere
You are using &MINTIME which is only the last value read from your loop : it's the field from MYHOLD1 which is then assigned to &MINTIME.&I in the loop
So when you do the second table file and referencing &MINTIME, &MINTIME has the last value read from MYHOLD1

If you stick to use the loop, you will also need to loop the second hold file and (this is one option) generates multiple MYHOLD&I to then merge them into one file and no need to have multiple variables
See below sample. This will work assuming that you have at least one TMP file generated

TABLE FILE CAR
SUM SEATS
BY CAR    AS 'CARFLT'
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS FLTDATA
END
-RUN
-SET &NBLOOP = &LINES;
-TYPE &NBLOOP

-REPEAT LOOP FOR &I FROM 1 TO &NBLOOP

-READFILE FLTDATA
-RUN

TABLE FILE CAR
SUM SEATS
    RETAIL_COST
    COMPUTE RATIO /P6.2C = RETAIL_COST / &SEATS;
BY TOTAL COMPUTE ROWIND /I2 = &I;
BY CAR
BY COUNTRY
WHERE CAR EQ '&CARFLT';
ON TABLE HOLD AS TMP&I
END
-RUN
-LOOP

TABLE FILE TMP1
SUM SEATS
    RETAIL_COST
    RATIO
BY ROWIND
BY CAR
BY COUNTRY
ON TABLE HOLD AS RPTDATA
-REPEAT MORELOOP FOR &J FROM 2 TO &NBLOOP
MORE
FILE TMP&J
-MORELOOP
END
-RUN

TABLE FILE RPTDATA
SUM SEATS
    RETAIL_COST
    RATIO
BY ROWIND
BY COUNTRY
BY CAR
END
-RUN

But this is only one way to do it. You could use JOIN, MATCH, a loop with file APPEND instead of multiple hold file, ...

Hey Martin,

I really appreciate your help with my situation. I figured out the percentages by line. I also have a recalculation of all the lines grouped by org type. The recalculation is no longer working and is highlighted in red. We're using this recalculation because it wasn't calculating the total percentages correctly. For some reason it wasn't calculating correctly. Attached is a screenshot of the total's by columns calculation. https://imgur.com/a/BEC7wum

 
-DEFAULTH &TOTAL_WEEKS=_FOC_NULL;
-*-DEFAULTH &TOTAL_OTBEGINS=_FOC_NULL;
-DEFAULTH &TOTALOTBEGINS=TOTALOTBEGINS;
-*COMPONENT=Define_hyp_cp_labor_util_actual
DEFINE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
 TYPES/A29=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 'OTHER';
 
END
SET ASNAMES = ON
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
-SET &ECHO=ALL;
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';
-*TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
-*SUM MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS AS 'MINTIME'
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
-*BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
-*WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
-*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).Start Date.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).End Date.QUOTEDSTRING;
-*ON TABLE HOLD AS MYHOLD1
-*END
-*-RUN
-*

-*
-*-RUN

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM COMPUTE TOTAL_OTBEGINS/D12.2 = CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.TS_DT * MIN.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS;
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY  HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
WHERE HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.S_TS_TYPE_CD EQ 'R';
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).Start Date.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).End Date.QUOTEDSTRING;
ON TABLE HOLD AS MYHOLD
-*ON TABLE PCHOLD FORMAT AHTML
END
-RUN

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM
COMPUTE SORT1/A1='1';
DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.OT_BEGINS
HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ENTERED_HRS
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.ORG_TYPE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.EMPLOYEE
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.COUNTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.BU_INDUSTRY
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.DEPARTMENT
BY LOWEST TYPES
BY HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY
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;
ON TABLE HOLD AS MAINDATA FORMAT ALPHA
END

-************************************************************








 [COLOR:RED]

TABLE FILE MAINDATA
SUM FST.OT_BEGINS
-TYPE OT_BEGINS
COMPUTE TOTSHOURS/P20.2 = FST.OT_BEGINS;
BY ORG_TYPE
BY EMPLOYEE
BY DEPARTMENT 
ON TABLE HOLD AS EMP_HRS1
END
TABLE FILE EMP_HRS1
SUM TOTSHOURS
BY ORG_TYPE
ON TABLE HOLD AS SUB_THOURS
END
-**************

FILEDEF OTYPE_SUM DISK maindata.ftm (APPEND

-************************************************************
JOIN ORG_TYPE IN MAINDATA TO ORG_TYPE IN SUB_THOURS AS J1

DEFINE FILE MAINDATA
EMP/A101=' ';
CNTRY/A6V=' ';
BU_IND/A27=' ';
DEPT/A10V=' ';
END
TABLE FILE MAINDATA
SUM

COMPUTE SORT1/A1='2';
    FST.TOTSHOURS
    ENTERED_HRS
BY ORG_TYPE
BY EMP
BY CNTRY
BY BU_IND
BY DEPT
BY TYPES
BY  UTIL_CATEGORY
ON TABLE HOLD AS OTYPE_SUM FORMAT ALPHA
END

[/COLOR] 













-***********************************************************


JOIN EMPLOYEE AND DEPARTMENT IN MAINDATA TO EMPLOYEE AND DEPARTMENT IN MYHOLD AS J2

DEFINE FILE MAINDATA
CATEGORY/A50 = IF SORT1 EQ '2' THEN 'TOTAL '| ORG_TYPE ELSE ORG_TYPE;
END

TABLE FILE MAINDATA
SUM
COMPUTE PERCENTAGE/D12.2%=( ENTERED_HRS/(TOTAL_OTBEGINS)) * 100 ;
-*COMPUTE HOURS/D12=( ENTERED_HRS) ;
-*COMPUTE OTBEGINS/D12=( TOTAL_OTBEGINS) ;
BY ORG_TYPE NOPRINT SKIP-LINE
BY SORT1 NOPRINT
BY CATEGORY AS 'CATEGORY'
-*BY EMPLOYEE AS 'EMPLOYEE'
BY COUNTRY AS 'COUNTRY'
BY BU_INDUSTRY AS 'BUSINESS UNIT'
BY DEPARTMENT AS 'DEPARTMENT'
ACROSS LOWEST TYPES AS ''
ACROSS UTIL_CATEGORY  AS '' SUBTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel,XLSX>).Select an output type.
ON TABLE ROW-TOTAL
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,$
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, HFREEZE=ON, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $

TYPE=DATA,WHEN=SORT1 EQ 2,$TYPE=REPORT,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=ACROSSVALUE,
	 COLUMN=N8,
     BACKCOLOR=RGB(241 196 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N8,
     BACKCOLOR=RGB(241 196 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N9,
     BACKCOLOR=RGB(241 196 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N10,
     BACKCOLOR=RGB(241 196 0),
$

TYPE=ACROSSVALUE,
	 COLUMN=N11,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N12,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N13,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N14,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N15,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N16,
     BACKCOLOR=RGB(237 139 0),
$
TYPE=ACROSSVALUE,
	 COLUMN=N17,
     BACKCOLOR=RGB(220 107 47),
$
TYPE=ACROSSVALUE,
	 COLUMN=N18,
     BACKCOLOR=RGB(220 107 47),
$
TYPE=ACROSSVALUE,
	 COLUMN=N19,
     BACKCOLOR=RGB(220 107 47),
$
TYPE=ACROSSVALUE,
	 COLUMN=N20,
     BACKCOLOR=RGB(220 107 47),
$
TYPE=DATA,
     COLUMN=N10,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 151 189),
$
TYPE=DATA,
     COLUMN=N16,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 151 189),
$
TYPE=DATA,
     COLUMN=N20,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 151 189),
$
TYPE=DATA,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 151 189),
     WHEN=N2 EQ '2',
$
TYPE=DATA,
     COLUMN=ROWTOTAL(*),
     COLOR='WHITE',
	 BACKCOLOR=RGB(80 158 47),
$

ENDSTYLE
END

-RUN

 

Thank you so much!

I can't help you anymore.
I have no way to figure what the data is and check which step is creating the problem.
Looking at code only without knowing what it occurs with the data is going no where.

You have to do what each good programmer have learned to do : debug !
Perform step by step and check the result at each one, then you will find where is the problem.

You have to do it by yourself.
I think that now you have enough tools and tips to figure it out.

Good luck