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)Fiscal Dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(SOLVED)Fiscal Dates
 Login/Join
 
Gold member
posted
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
 
Posts: 52 | Location: BOWL OF PASTA | Registered: October 13, 2016Report This Post
Master
posted Hide Post
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
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 52 | Location: BOWL OF PASTA | Registered: October 13, 2016Report This Post
Master
posted Hide Post
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
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report 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)Fiscal Dates

Copyright © 1996-2020 Information Builders