Hi all,

I'm trying to join 2 different hold files I'm creating in my fex. The hold files are at different levels. Essentially what I'm trying to do is total up employees billable hours and divide that by the total hours worked. One hold file has the billable hours at the CATEGORY LEVEL and the other has total hours at the employee level. Both hold files are showing the correct data, but when I join those 2 hold files together I'm missing data for some CATEGORIES. There are 2 CATEGORIES and it seems like if both CATEGORIES have hours it shows just the first one that shows on the report. But if the employee just has 1 CATEGORY with hours it shows correctly.

Here is an image of my HOURS report and the PERCENTAGE report. The hours are showing correctly, the percentages aren't. Take a look at the hours image, the person with 169.5 hours under fixed price also has 9.5 hours under T&M. In the Percentages image it's showing the percentage for fixed price correct, but not showing anything for T&M.

https://imgur.com/a/a61ofl7


Here is my code:

 
-DEFAULTH &TOTAL_WEEKS=_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
COMPUTE SORT1/A1='1';
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.UTIL_CATEGORY
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
-*-RUN

TABLE FILE MAINDATA
SUM COMPUTE BILLABLE_HOURS/D12.2= ENTERED_HRS;
BY ORG_TYPE
BY EMPLOYEE AS 'BILLABLE EMPLOYEE'
BY UTIL_CATEGORY
WHERE UTIL_CATEGORY EQ 'AT RISK' OR 'FIXED PRICE' OR 'T&|M';
ON TABLE HOLD AS MYHOLD3
-*ON TABLE PCHOLD FORMAT AHTML
END
-*-RUN

TABLE FILE MAINDATA
SUM COMPUTE TOTAL_HOURS/D12.2= ENTERED_HRS ;
COMPUTE SORT1/A1='1';
BY ORG_TYPE
BY EMPLOYEE
WHERE UTIL_CATEGORY EQ 'AT RISK' OR 'FIXED PRICE' OR 'T&|M' OR 'B&|P' OR 'COST COLLECTOR' OR 'OVERHEAD' OR 'PRODUCT DEVELOPMENT' OR 'SALES' OR 'CUSTOMER SUPPORT' OR 'OTHER' OR 'RATABLE SERVICES';
ON TABLE HOLD AS MYHOLD2
-*ON TABLE PCHOLD FORMAT AHTML
END
-*-RUN

JOIN LEFT_OUTER EMPLOYEE AND ORG_TYPE  IN MYHOLD2 TO 'BILLABLE EMPLOYEE' AND ORG_TYPE  IN MYHOLD3 AS J2
-************************************************************

DEFINE FILE MAINDATA
CATEGORY_ORD/A29= DECODE UTIL_CATEGORY('AT RISK' ATRISK 'FIXED PRICE' FIXEDPRICE 'T&|M' T&|M);
END

TABLE FILE MYHOLD2
SUM
COMPUTE PERCENTAGE/D12.2%= IF SORT1 EQ '1' THEN ((BILLABLE_HOURS/TOTAL_HOURS) * 100) ELSE ((BILLABLE_HOURS/TOTAL_HOURS ) * 100);
BILLABLE_HOURS
TOTAL_HOURS
-*BY SORT1 
BY ORG_TYPE  RECOMPUTE
BY EMPLOYEE 
ACROSS UTIL_CATEGORY RECOMPUTE
WHERE ORG_TYPE EQ 'SERVICES BILLABLE'
-*WHERE ORG_TYPE EQ 'SERVICES BILLABLE' OR (UTIL_CATEGORY EQ 'AT RISK' OR 'FIXED PRICE' OR 'T&|M' AND ORG_TYPE EQ 'SERVICES BILLABLE');
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel,XLSX>).Select an output type.
-*ON TABLE ROW-TOTAL
ON TABLE SET ONLINE-FMT HTML
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, HFREEZE=ON, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
TYPE=DATA,
	 COLUMN=COLUMNTOTAL(*),
     COLOR='WHITE',
	 BACKCOLOR=RGB(80 158 47),
$
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
 

Also, is there anyway to debug a join in AppStudio?

Thank you!

This message has been edited. Last edited by: zcbillions,

I might be wrong but INNER and OUTER JOINs don't really apply to sequential alpha formatted hold files. Have you tried holding these tables FORMAT FOCUS or another DBMS with an index?

That's interesting. I have not. I will give it a try. Thanks!

Hi BabakNYC,

I moved the hold files to FORMAT FOCUS and I indexed BILLABLE_HOURS, TOTAL_HOURS AND EMPLOYEE and I'm getting the same results.

So I brushed up on my joining in AppStudio, and I wasn't using the MULTIPLE keyword to join. Fun stuff.

Hi BabakNYC,

Now it's skewing my RECOMPUTE. If it has hours for T&M and Fixed Price, it's adding both TOTAL HOURS for the TOTAL of my recompute. I just need one of the TOTALS for the TOTAL percentage calculation. I'm not sure how to deal with this as RECOMPUTE is an information builders function.