Hi WebFOCUS “Cummunity”,
Recently we step to the point where we want to have multiple time variables available for our users and programmers no matter the IBI tool they are using to create their report.

Those variables change every day based on the current date and our SQL table which has our Retail calendar. The fex find some value such as the current Retail Year, the previous Retail Year and the Retail Year of two years ago and so on. The same calculation is done for week and month number.

That calculation part was not difficult to setup and the variables was available from any IBI tool. The problem was Report Caster. If report, using any of the defined variables was scheduled, Report Caster did not recognize the variables.

To solve this problem here what we did.

1- Define the variables as global variables, ex: &&R1YEAR

2- Change the variable setup fex (PERIOD_CALCULATION.fex) to “write” a “SET” command line in a “report” as follow:

-* File PERIOD_CALCULATION.fex
-*
-* DETERMINE SOME CURRENT AND PREVIOUS TIME INFORMATION
-* SUCH AS :
-* - RETAIL-1 YEAR, MONTH, WEEK
-* - CALENDAR YEAR, MONTH, WEEK
-*
-SET &CURR_DATE=&YYMD;
-SET &PREV_DATE=AYMD(&YYMD, -1, 'I8YYMD');
FILEDEF DAYVARS DISK usual_modules/day_variables.fex
END
-RUN
-WRITE DAYVARS NOCLOSE -* LAST UPDATED &DATEYYMD.EVAL &TOD.EVAL
-RUN
-WRITE DAYVARS NOCLOSE -SET &|&|CURR_DATE=&CURR_DATE.EVAL;
-WRITE DAYVARS NOCLOSE -SET &|&|PREV_DATE=&PREV_DATE.EVAL;
-RUN

DEFINE FILE CL_SALES_WEEKLY
R1YEAR/I4=CL_SALES_WEEKLY.DWD_V_TIME.DWD_TIME_RETAIL_YR;
END
TABLE FILE CL_SALES_WEEKLY
BY R1YEAR
WHERE ( CL_SALES_WEEKLY.DWD_V_TIME.DWD_TIME_DATE EQ DT(&CURR_DATE) );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SAVE AS 'CURR_YEAR_SAVE' FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END
-RUN

-* TO AVOID ACCESS PROBLEM WHEN DB NOT AVAILABLE
-* BUT STILL ABLE TO WORK
-IF &RETCODE NE 0 THEN GOTO NOACCESS;

-READ CURR_YEAR_SAVE &R1YEAR_I.I4.
-RUN
-SET &R1YEAR=&R1YEAR_I;
-SET &R1YEAR_1=&R1YEAR_I - 1;
-SET &R1YEAR_2=&R1YEAR_I - 2;
-SET &R1YEAR_3=&R1YEAR_I - 3;
-WRITE DAYVARS NOCLOSE -SET &|&|R1YEAR=&R1YEAR_I.EVAL;
-WRITE DAYVARS NOCLOSE -SET &|&|R1YEAR_1=&R1YEAR_1.EVAL;
-WRITE DAYVARS NOCLOSE -SET &|&|R1YEAR_2=&R1YEAR_2.EVAL;
-WRITE DAYVARS NOCLOSE -SET &|&|R1YEAR_3=&R1YEAR_3.EVAL;
-RUN

-NOACCESS

3- As result a new fex (DAY_VARIABLES.fex) with the “SET” command line:

-* LAST UPDATED 2008/01/10 00.01.01
-SET &&CURR_DATE=20080110;
-SET &&PREV_DATE=20080109;
-SET &&R1YEAR=2008;
-SET &&R1YEAR_1=2007;
-SET &&R1YEAR_2=2006;
-SET &&R1YEAR_3=2005;

4- Include the fex DAY_VARIABLES.fex in our server profile file:

\ibi\64bit\srv76\wfs\etc\edasprof.prf
-*********************************************************
-* Profile generated on 03 May 2007 at 14:11:41
-*********************************************************
-*
-*
APP ENABLE
-*
-INCLUDE usual_modules/day_variables
-*

5- Finally we have to create a scheduled job that runs every day at 12:01AM and execute the fex PERIOD_CALCULATION previously defined.

Wishing that can help a lot of you.

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

A bit complex solution, but if it works.....

your last part to set the actual and prev years has to be changed now every year.....

you can say

-SET &&CURDATE=&YYMD;
-SET &&CURYEAR=EDIT('&YYMD.EVAL','9999');
-SET &&R1YEAR_1=&&CURYEAR-1;
-SET ....

We have a list of commands like the above in our edasprof.prf.

Frank, I did not see that until after I posted. Apologies.
Ira

Thanks for your suggestion Frank but we are not using the &YYMD to set our &&R1YEAR because the retail year do not have necessarly the same start as the civil year.

Reason why I'm reading it from a SQL table that is feed by the retail calendar and changed every year.

And yes it seems to be complex, but not that much and it is the only way to have RC able to read many variables. It is not in the scope of the product's changes for now to do it. So we have to find a work around.