As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.
I am newer to Webfocus reporting and was looking for help to see if there was a way to simplify the following Focus code that was written by a previous employee. I thought maybe it could be done using some kind of loop process but I could not figure it out. Any help to simplify would be great.
-* File: IBFS:/PROD/WFC/Repository/Executive_Dashboards/CEO_REPORTS/Report2.fex Created by WebFOCUS AppStudio -INCLUDE IBFS:/WFC/Repository/Executive_Dashboards/CEO_REPORTS/DAYS_IN_WEEK.fex
DEFINE FILE STAR_INV_DAILY_SUM BUS_UNIT/A20= IF STAR_INV_DAILY_SUM.MAJOR_PRODUCT_GROUP_CODE EQ '003' OR '004' AND STAR_INV_DAILY_SUM.REPORTING_CODE EQ 'TR02' OR 'TR12'
If you are talking about genrating the WTD and DAY code, then yes.
I would suggest looking into Dialog Manager and the -REPEAT function
You can create a loop and put Dialog Manager variables into the code to replace the numbers.
e.g. -REPEAT ::My:Loop FOR &Cntr FROM 1 to 6 ; -TYPE &Cntr WTD_&Cntr|/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ &Cntr THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_&Cntr ELSE 0; -::My:Loop
The question is, if adding loops to your code is really simplifying it. If I where you, I would keep it like this, unless there is a requirement to make it dynamic (for instance only show 8 days when there is only 8 days in the dataset)
For those 36 day fields, it would be simpler to use an ACROSS:
DEFINE FILE STAR_INV_DAILY_SUM
BUS_UNIT/A20=
IF STAR_INV_DAILY_SUM.MAJOR_PRODUCT_GROUP_CODE EQ '003' OR '004'
AND STAR_INV_DAILY_SUM.REPORTING_CODE EQ 'TR02' OR 'TR12'
THEN 'POS BASE'
ELSE 'OTHER';
TOTAL_DOL/P12.2C=STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.ON_HAND_EXTENDED_COST + STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.IN_TRANSIT_EXTENDED_COST;
WTD_1/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 1 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_1 ELSE 0;
WTD_2/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 2 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_2 ELSE 0;
WTD_3/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 3 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_3 ELSE 0;
WTD_4/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 4 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_4 ELSE 0;
WTD_5/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 5 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_5 ELSE 0;
WTD_6/P12.2C = IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_WEEK_IN_MONTH EQ 6 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL/&DAYS_WK_6 ELSE 0;
MTD/P12.2C=0;
LABEL/A30='Total Inv $';
GROUP/A30= 'INVENTORY';
END
TABLE FILE STAR_INV_DAILY_SUM
SUM
COMPUTE MONTH_BUD/P12.2C=0;
COMPUTE DAILY_BUD/P12.2C=0;
COMPUTE DAILY_AVG/P12.2C=MTD/&DAYS_TO_DATE;
WTD_1
WTD_2
WTD_3
WTD_4
WTD_5
WTD_6
MTD
BY GROUP
BY BUS_UNIT
BY LABEL
SUM
STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL
ACROSS STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH
BY GROUP
BY BUS_UNIT
BY LABEL
WHERE BUS_UNIT NE 'OTHER';
WHERE STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_YEAR EQ &YEAR;
WHERE STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_MONTH EQ &MONTH;
ON TABLE HOLD AS 'APV_HOLD/CEO_INV_POS_BASE_DOL'
END
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :