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 firstname.lastname@example.org 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
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'
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;
LABEL/A30='Total Inv $';
TABLE FILE STAR_INV_DAILY_SUM
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'
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 :