Hi All,

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'

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;

DAY_1_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 1 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_2_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 2 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_3_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 3 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_4_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 4 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_5_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 5 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_6_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 6 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_7_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 7 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_8_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 8 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_9_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 9 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_10_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 10 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_11_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 11 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_12_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 12 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_13_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 13 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_14_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 14 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_15_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 15 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_16_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 16 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_17_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 17 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_18_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 18 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_19_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 19 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_20_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 20 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_21_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 21 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_22_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 22 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_23_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 23 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_24_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 24 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_25_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 25 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_26_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 26 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_27_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 27 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_28_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 28 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_29_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 29 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_30_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 30 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_31_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 31 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_32_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 32 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_33_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 33 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_34_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 34 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_35_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 35 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 0;
DAY_36_DATA/P12.2C=IF STAR_INV_DAILY_SUM.D_DATE.ACCOUNTING_DAY_IN_MONTH EQ 36 THEN STAR_INV_DAILY_SUM.F_INV_DAILY_SUM.TOTAL_DOL ELSE 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;
DAY_1_DATA
DAY_2_DATA
DAY_3_DATA
DAY_4_DATA
DAY_5_DATA
DAY_6_DATA
DAY_7_DATA
DAY_8_DATA
DAY_9_DATA
DAY_10_DATA
DAY_11_DATA
DAY_12_DATA
DAY_13_DATA
DAY_14_DATA
DAY_15_DATA
DAY_16_DATA
DAY_17_DATA
DAY_18_DATA
DAY_19_DATA
DAY_20_DATA
DAY_21_DATA
DAY_22_DATA
DAY_23_DATA
DAY_24_DATA
DAY_25_DATA
DAY_26_DATA
DAY_27_DATA
DAY_28_DATA
DAY_29_DATA
DAY_30_DATA
DAY_31_DATA
DAY_32_DATA
DAY_33_DATA
DAY_34_DATA
DAY_35_DATA
DAY_36_DATA
WTD_1
WTD_2
WTD_3
WTD_4
WTD_5
WTD_6
MTD
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

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

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