Focal Point
(SOLVED)Fiscal Dates

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6427025586

February 13, 2017, 12:29 PM
tomatosauce
(SOLVED)Fiscal Dates
Hi All,

We are currently working on a preprocess procedure that can help us to determine the date parameters for any report that we want to run across our enterprise. Some of the parameters are as follows.

LW_BOFP (Last Week, Beginning of Fiscal Period)

LW_EOFP (Last Week, End of Fiscal Period)

LW_BOFQ (Last Week, Beginning of Fiscal Quarter)

LW_EOFQ (Last Week, End of Fiscal Quarter)

LW_BOFY (Last Week, Beginning of Fiscal Year)

LW_EOFY (Last Week, End of Fiscal Year).

We already have a date dimension master file which has all the above values generated in oracle database from our accounting department.

So i created a fex as follows.

 
-SET &LWEEK    = DATECVT( DATEADD( DATECVT(&YYMD,'I8YYMD','YYMD') ,'D', -7), 'YYMD','I8YYMD');
-SET &LWEEK_BOW = DATECVT(DATEADD( DATEMOV( DATECVT(&LWEEK,'I8YYMD','YYMD') ,'BOW'),'D','-1'),'YYMD','I8YYMD');
-SET &LWEEK_EOW = DATECVT(DATEADD( DATEMOV( DATECVT(&LWEEK,'I8YYMD','YYMD') ,'EOW'),'D','1'),'YYMD','I8YYMD');
-SET &&LWEEK_EOW_DATE = EDIT(&LWEEK_EOW, '$$$$99')|'/'| EDIT(&LWEEK_EOW, '$$$$$$99')|'/' | EDIT (&LWEEK_EOW, '9999$');
*- get's the last week's EOW date
TABLE FILE MSTR_DATE_DIMENSION
PRINT
     
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_BEGIN_DATE
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_END_DATE
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_QTR_BEGIN_DATE
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_QTR_END_DATE
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_PER_BEGIN_DATE
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_PER_END_DATE
WHERE MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.CAL_DATE EQ '&&LWEEK_EOW_DATE';
END 


This will return all the values i need as parameters for my main procedures. My issue is how should i pass these values to main fex's.

I know you could get the individual value as
How can i read the values of these dates from a hold file and send it as parameters to my procedures. All the dates are in MDYY format

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


Tharun Katanguru
SBOX- 8205 DEV/TEST/PROD : 8105 8205
Linux, All Outputs
February 13, 2017, 01:56 PM
eric.woerle
What you want to use is -READFILE. Here is some information from the help file

quote:

Reading Variable Values From and Writing Variable Values to an External File
In this section:

Closing an External File
How to:

Retrieve a Variable Value From an External File
Write a Variable Value to an External File
Read Master File Fields Into Dialogue Manager Variables
Reference:

Usage Notes for -READFILE


You can read variable values from an external file, or write variable values to an external file with the -READ and -WRITE commands.

You can supply variable values with the -READ command. For example, an external file may contain the start and end dates of a reporting period. Dialogue manager can read these values from an external file and use them a variable in a WHERE command that limits the range of data selected in a report request.
You can save variable values in an external file with the -WRITE command. For example, a request can store the summed total of sales for the day in an external file so that it can be compared to the total sales of the following day.
The external file can be a fixed-format file (in which the data is in fixed columns) or a free-format file (in which the data is comma delimited).

When using a -READ or -WRITE command, the external file must be included in a FILEDEF command in your procedure. A -RUN command must then separate the FILEDEF command and the -READ or -WRITE command.

You can also read a file using the -READFILE command. The -READFILE command reads a file by first reading its Master File and creating Dialogue Manager amper variables based on the ACTUAL formats for each field in the Master File. It then reads the file and, if necessary, converts the fields from numeric values to alphanumeric strings before returning them to the created variables. Display options in the USAGE formats are not propagated to the variables. The names of the amper variables are the field names prefixed with an ampersand (&).


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


xx
Syntax: How to Retrieve a Variable Value From an External File
-READ filename[,] [NOCLOSE] &name[.format.][,][&name][.format.]
where:

filename[,]
Is the name of the external file, which must be defined to the operating system. A space after filename denotes a fixed-format file, while a comma denotes a free-format file.

On UNIX and Windows platforms, a FILEDEF for the external file is required.
On z/OS, the external file must be allocated in the JCL or dynamically allocated by WebFOCUS with the ALLOCATE command.
NOCLOSE
Keeps the external file open until the -READ operation is complete. Files kept open with NOCLOSE can be closed using the command -CLOSE filename or a subsequent -WRITE command.

&name[,]
Is the variable name. For free-format files, you may separate the variable names with commas. If the list of variables is longer than one line, end the first line with a comma and begin the next line with a dash followed by a blank. For fixed-format files, including comma-delimited files, begin the next line with a dash, a blank, and a comma.

Free-format

-READ EXTFILE, &CITY, &CODE1,- &CODE2
Fixed-format

-READ EXTFILE &CITY.A8. &CODE1.A3.,- ,&CODE2.A3.
.format.
Is the format of the variable. For a free-format file, specifying this value is optional. For a fixed-format file, format is the length or the type and the length. The type is either A (alphanumeric), which is the default, or I (numeric). The format value must be delimited by periods. The format is ignored for comma-delimited files.

Note: Instead of using .format., you can specify the length of a variable using -SET and enclosing the corresponding number of blanks in single quotes. For example:

-SET &CITY=' ';
-SET &CODE1=' ';
-SET &CODE2=' ';




Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
February 13, 2017, 02:26 PM
tomatosauce
Eric Thanks for the reply. I just figured out a solution after going through some of the posts in the forum.

 
SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON

-SET &LWEEK    = DATECVT( DATEADD( DATECVT(&YYMD,'I8YYMD','YYMD') ,'D', -7), 'YYMD','I8YYMD');
-SET &LWEEK_BOW = DATECVT(DATEADD( DATEMOV( DATECVT(&LWEEK,'I8YYMD','YYMD') ,'BOW'),'D','-1'),'YYMD','I8YYMD');
-SET &LWEEK_EOW = DATECVT(DATEADD( DATEMOV( DATECVT(&LWEEK,'I8YYMD','YYMD') ,'EOW'),'D','1'),'YYMD','I8YYMD');
-SET &&LWEEK_EOW_DATE = EDIT(&LWEEK_EOW, '$$$$99')|'/'| EDIT(&LWEEK_EOW, '$$$$$$99')|'/' | EDIT (&LWEEK_EOW, '9999$');

TABLE FILE MSTR_DATE_DIMENSION
PRINT

     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_BEGIN_DATE/MDYY
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_END_DATE/MDYY
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_QTR_BEGIN_DATE/MDYY
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_QTR_END_DATE/MDYY
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_PER_BEGIN_DATE/MDYY
     MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.FISCAL_YEAR_PER_END_DATE/MDYY
WHERE MSTR_DATE_DIMENSION.MSTR_DATE_DIMENSION.CAL_DATE EQ '&&LWEEK_EOW_DATE';
ON TABLE SAVE AS DATES
-RUN
-READ DATES &FISCAL_YEAR_BEGIN_DATE.8. &FISCAL_YEAR_END_DATE.8. &FISCAL_YEAR_QTR_BEGIN_DATE.8. &FISCAL_YEAR_QTR_END_DATE.8. &FISCAL_YEAR_PER_BEGIN_DATE.I8. &FISCAL_YEAR_PER_END_DATE.8.

-SET &LW_BOFY = EDIT(&FISCAL_YEAR_BEGIN_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_BEGIN_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_BEGIN_DATE,'$$$$9999');
-SET &LW_EOFY = EDIT(&FISCAL_YEAR_END_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_END_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_END_DATE,'$$$$9999');
-SET &LW_BOFQ = EDIT(&FISCAL_YEAR_QTR_BEGIN_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_QTR_BEGIN_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_QTR_BEGIN_DATE,'$$$$9999');
-SET &LW_EOFQ = EDIT(&FISCAL_YEAR_QTR_END_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_QTR_END_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_QTR_END_DATE,'$$$$9999');
-SET &LW_BOFP = EDIT(&FISCAL_YEAR_PER_BEGIN_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_PER_BEGIN_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_PER_BEGIN_DATE,'$$$$9999');
-SET &LW_EOFP = EDIT(&FISCAL_YEAR_PER_END_DATE,'99$')|'/'| EDIT(&FISCAL_YEAR_PER_END_DATE,'$$99$')|'/'| EDIT(&FISCAL_YEAR_PER_END_DATE,'$$$$9999');

-TYPE LW_BOFY (Last Week, Beginning of Fiscal Year) = &LW_BOFY
-TYPE LW_EOFY (Last Week, End of Fiscal Year) = &LW_EOFY
-TYPE LW_BOFQ (Last Week, Beginning of Fiscal Quarter) = &LW_BOFQ
-TYPE LW_EOFQ (Last Week, End of Fiscal Quarter) = &LW_EOFQ
-TYPE LW_BOFP (Last Week, Beginning of Fiscal Period) = &LW_BOFP
-TYPE LW_EOFP (Last Week, End of Fiscal Period) = &LW_EOFP 


This works like a charm for me.


Tharun Katanguru
SBOX- 8205 DEV/TEST/PROD : 8105 8205
Linux, All Outputs
February 13, 2017, 03:04 PM
eric.woerle
yup, that's it exactly. Keep that in your bag of tricks for the future. It can be very useful. Also familiarize your self with -READFILE though. Its similar to -READ, but it will automatically create the variables for you. You won't have to worry about figuring out the correct data length of the field. This becomes helpful with AnV fields which will have 6 proceeding characters (defines the length of the field) to the value of the field that would need to be accounted for. The help file is pretty good on it. I just posted a snipit of it for you.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2