Focal Point Banner


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.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Simplify report code

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Simplify report code
 Login/Join
 
Member
posted
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,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: May 21, 2014Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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)


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Simplify report code

Copyright © 1996-2020 Information Builders