 |
Creating Flexible Reports at Run Time |
Problem
A few years back, I was asked to construct a FOCUS based roll forward report to cater for the purchase and sales of asset lots.
The report was to be an ‘across’ report with the user entering a beginning and end date.
In our business an asset is held on a particular date if it has not already been sold. A lot contains a certain amount of shares. Shares may be sold from a lot BUT no shares may be added to a lot after the lot is declared.
The end user would enter a start date for the report as well as an end date. The report would have columns with days displayed horizontally ranging from Day01 Day02 Day03 etc up to and including the end date.
Further, new buys could occur between the start and end date of the period and these were to be flagged as ‘Buys’.
Sells could occur during the period. The sell could be the entire lot at one time or in several stages.
In the case that there was no change in shares from the start date through the end date, then the record should be flagged with a ‘*’ to indication no ‘movement’.
The business rules are best seen within the table below:
(FR000_table)
The start and end date are submitted by the user for the period under consideration. This is obtained from &variables. The start and end date are applied to the data extract portion of the code, in the form of a predicate. See Appendix A.
A six day report may be seen as below:
(FR001)
Or a 21 day report:
(FR002)
(FR003)
Solution
Step 1: Extract the raw data from the SQL Server database and hold it in START2
-SET &TEMPPATH = TEMPPATH(100,'A100'); FILEDEF HOLDMAST DIR &TEMPPATH FILEDEF HOLDMAST DIR C:\IBI\APPS\FINANCIALREPORTS APP HOLD FINANCIALREPORTS -RUN -INCLUDE ALLOCS -SET &SDATE='20071201'; -SET &EDATE='20071210'; -SET &FUND = 'CI1A'; SET ASNAMES =ON SET ALL = ON SET DEFCENT =19 SET YRTHRESH = 80 SET ALL = ON JOIN CLEAR * -SET RPTDTIND = 'H'; -RUN DEFINE FILE ACSGROLL CONCATVAR1/A25 = S_FUND || S_ASSET_ID || S_LOT_NUM || S_ACCOUNT; SDATE/YYMD = '&SDATE'; EDATE/YYMD = '&EDATE'; END TABLE FILE ACSGROLL PRINT CONCATVAR1 HOLDDATESD S_PAR_SHARES BY CONCATVAR1 NOPRINT BY HOLDDATESD NOPRINT WHERE HOLDDATESD GE SDATE WHERE HOLDDATESD LE EDATE ON TABLE HOLD AS START2 END -RUN |
Step 2: Dynamically (at run time) create the necessary DEFINES.
* This is achieved by:
SUM HOLDDATESD BY HOLDDATESD NOPRINT
(FR004)
* Creating a recordday DEFINED field , one for each HOLDDATESD
(FR005)
* Now ascertain how many times we have to iterate to produce the correct number of DEFINES and use this number and DIALOG Manager Code to generate the DEFINES. We do this by obtaining the value of &LINES(from above). The defines are shown below.
DAY01/D15.2 = IF (RECORDDAY EQ 1) THEN S_PAR_SHARES ELSE 0; DAY02/D15.2 = IF (RECORDDAY EQ 2) THEN S_PAR_SHARES ELSE 0; DAY03/D15.2 = IF (RECORDDAY EQ 3) THEN S_PAR_SHARES ELSE 0; DAY04/D15.2 = IF (RECORDDAY EQ 4) THEN S_PAR_SHARES ELSE 0; DAY05/D15.2 = IF (RECORDDAY EQ 5) THEN S_PAR_SHARES ELSE 0; DAY06/D15.2 = IF (RECORDDAY EQ 6) THEN S_PAR_SHARES ELSE 0; DAY07/D15.2 = IF (RECORDDAY EQ 7) THEN S_PAR_SHARES ELSE 0; DAY08/D15.2 = IF (RECORDDAY EQ 8) THEN S_PAR_SHARES ELSE 0; DAY09/D15.2 = IF (RECORDDAY EQ 9) THEN S_PAR_SHARES ELSE 0; DAY10/D15.2 = IF (RECORDDAY EQ 10) THEN S_PAR_SHARES ELSE 0; DAY11/D15.2 = IF (RECORDDAY EQ 11) THEN S_PAR_SHARES ELSE 0; DAY12/D15.2 = IF (RECORDDAY EQ 12) THEN S_PAR_SHARES ELSE 0; DAY13/D15.2 = IF (RECORDDAY EQ 13) THEN S_PAR_SHARES ELSE 0; DAY14/D15.2 = IF (RECORDDAY EQ 14) THEN S_PAR_SHARES ELSE 0; DAY15/D15.2 = IF (RECORDDAY EQ 15) THEN S_PAR_SHARES ELSE 0; DAY16/D15.2 = IF (RECORDDAY EQ 16) THEN S_PAR_SHARES ELSE 0; DAY17/D15.2 = IF (RECORDDAY EQ 17) THEN S_PAR_SHARES ELSE 0; DAY18/D15.2 = IF (RECORDDAY EQ 18) THEN S_PAR_SHARES ELSE 0; DAY19/D15.2 = IF (RECORDDAY EQ 19) THEN S_PAR_SHARES ELSE 0; DAY20/D15.2 = IF (RECORDDAY EQ 20) THEN S_PAR_SHARES ELSE 0; DAY21/D15.2 = IF (RECORDDAY EQ 21) THEN S_PAR_SHARES ELSE 0; |
* At the same time we create a flat file that lists each ‘DAY’ which will be included between the
PRINT statement and the ON TABLE HOLD statement( a bit further in the code).
This –INCLUDE file is called FUND11. Its contents at this point are shown below.
DAY01 DAY02 DAY03 DAY04 DAY05 DAY06 DAY07 DAY08 DAY09 DAY10 DAY11 DAY12 DAY13 DAY14 DAY15 DAY16 DAY17 DAY18 DAY19 DAY20 DAY21 |
The full code extract may be seen in APPENDIX B.
Step 3: Now we JOIN HOLDDATESD from the SQL Server extract (hold file START2) to the HOLDDATESD from the ‘recordday’. Extract and hold the extract. See code in Appendix A (in Brown). The results are shown below:
|
||||
CONCATVAR1 |
HOLDDATESD |
S_PAR_SHARES |
RECORDDAY |
|
ZYXV12599X9E1439002911 |
2007/11/30 00:00:00.000 |
4,085.90 |
1 |
|
ZYXV12599X9E1439002911 |
2007/12/03 00:00:00.000 |
4,085.90 |
2 |
|
ZYXV12599X9E1439002911 |
2007/12/04 00:00:00.000 |
4,085.90 |
3 |
|
ZYXV12599X9E1439002911 |
2007/12/05 00:00:00.000 |
4,085.90 |
4 |
|
ZYXV12599X9E1439002911 |
2007/12/06 00:00:00.000 |
4,085.90 |
5 |
|
ZYXV12599X9E1439002911 |
2007/12/07 00:00:00.000 |
4,085.90 |
6 |
|
ZYXV12599X9E1439002911 |
2007/12/10 00:00:00.000 |
4,085.90 |
7 |
|
ZYXV12599X9E1439002911 |
2007/12/11 00:00:00.000 |
4,085.90 |
8 |
|
ZYXV12599X9E1439002911 |
2007/12/12 00:00:00.000 |
4,085.90 |
9 |
|
ZYXV12599X9E1439002911 |
2007/12/13 00:00:00.000 |
4,085.90 |
10 |
|
ZYXV12599X9E1439002911 |
2007/12/14 00:00:00.000 |
4,085.90 |
11 |
|
ZYXV12599X9E1439002911 |
2007/12/17 00:00:00.000 |
4,085.90 |
12 |
|
ZYXV12599X9E1439002911 |
2007/12/18 00:00:00.000 |
4,085.90 |
13 |
|
ZYXV12599X9E1439002911 |
2007/12/19 00:00:00.000 |
4,085.90 |
14 |
|
ZYXV12599X9E1439002911 |
2007/12/20 00:00:00.000 |
4,085.90 |
15 |
|
ZYXV12599X9E1439002911 |
2007/12/21 00:00:00.000 |
4,085.90 |
16 |
|
ZYXV12599X9E1439002911 |
2007/12/24 00:00:00.000 |
4,085.90 |
17 |
|
ZYXV12599X9E1439002911 |
2007/12/26 00:00:00.000 |
4,085.90 |
18 |
|
ZYXV12599X9E1439002911 |
2007/12/27 00:00:00.000 |
4,085.90 |
19 |
|
ZYXV12599X9E1439002911 |
2007/12/28 00:00:00.000 |
4,085.90 |
20 |
|
ZYXV12599X9E1439002911 |
2007/12/31 00:00:00.000 |
4,085.90 |
21 |
|
ZYXV12599X9E1440002912 |
2007/12/04 00:00:00.000 |
38.81 |
3 |
|
ZYXV12599X9E1440002912 |
2007/12/05 00:00:00.000 |
38.81 |
4 |
|
ZYXV12599X9E1440002912 |
2007/12/06 00:00:00.000 |
38.81 |
5 |
|
ZYXV12599X9E1440002912 |
2007/12/07 00:00:00.000 |
38.81 |
6 |
|
ZYXV12599X9E1440002912 |
2007/12/10 00:00:00.000 |
38.81 |
7 |
|
ZYXV12599X9E1440002912 |
2007/12/11 00:00:00.000 |
38.81 |
8 |
|
ZYXV12599X9E1440002912 |
2007/12/12 00:00:00.000 |
38.81 |
9 |
|
ZYXV12599X9E1440002912 |
2007/12/13 00:00:00.000 |
38.81 |
10 |
|
ZYXV12599X9E1440002912 |
2007/12/14 00:00:00.000 |
38.81 |
11 |
|
ZYXV12599X9E1440002912 |
2007/12/17 00:00:00.000 |
38.81 |
12 |
|
ZYXV12599X9E1440002912 |
2007/12/18 00:00:00.000 |
38.81 |
13 |
|
ZYXV12599X9E1440002912 |
2007/12/19 00:00:00.000 |
38.81 |
14 |
|
ZYXV12599X9E1440002912 |
2007/12/20 00:00:00.000 |
38.81 |
15 |
|
ZYXV12599X9E1440002912 |
2007/12/21 00:00:00.000 |
38.81 |
16 |
|
ZYXV12599X9E1440002912 |
2007/12/24 00:00:00.000 |
38.81 |
17 |
|
ZYXV12599X9E1440002912 |
2007/12/26 00:00:00.000 |
38.81 |
18 |
|
ZYXV12599X9E1440002912 |
2007/12/27 00:00:00.000 |
38.81 |
19 |
|
ZYXV12599X9E1440002912 |
2007/12/28 00:00:00.000 |
38.81 |
20 |
|
ZYXV12599X9E1440002912 |
2007/12/31 00:00:00.000 |
38.81 |
21 |
|
ZYXV12599X9E1441002913 |
2007/12/12 00:00:00.000 |
50,877.81 |
9 |
|
ZYXV12599X9E1441002913 |
2007/12/13 00:00:00.000 |
50,877.81 |
10 |
|
ZYXV12599X9E1441002913 |
2007/12/14 00:00:00.000 |
50,877.81 |
11 |
|
ZYXV12599X9E1441002913 |
2007/12/17 00:00:00.000 |
50,877.81 |
12 |
|
ZYXV12599X9E1441002913 |
2007/12/18 00:00:00.000 |
50,877.81 |
13 |
|
ZYXV12599X9E1441002913 |
2007/12/19 00:00:00.000 |
50,877.81 |
14 |
|
ZYXV12599X9E1441002913 |
2007/12/20 00:00:00.000 |
50,877.81 |
15 |
|
ZYXV12599X9E1441002913 |
2007/12/21 00:00:00.000 |
50,877.81 |
16 |
|
ZYXV12599X9E1441002913 |
2007/12/24 00:00:00.000 |
50,877.81 |
17 |
|
ZYXV12599X9E1441002913 |
2007/12/26 00:00:00.000 |
50,877.81 |
18 |
|
ZYXV12599X9E1441002913 |
2007/12/27 00:00:00.000 |
50,877.81 |
19 |
|
|
||||
CONCATVAR1 |
HOLDDATESD |
S_PAR_SHARES |
RECORDDAY |
|
ZYXV12599X9E1441002913 |
2007/12/28 00:00:00.000 |
50,877.81 |
20 |
|
ZYXV12599X9E1441002913 |
2007/12/31 00:00:00.000 |
50,877.81 |
21 |
|
Step 4: Print out the contents of the hold file START3
Note that we include our generated hold files ‘ADDLINES’ (purple) and FUND11 ‘green’. See the description of these two files above.
To verify what is transpiring we can set &ECHO = ON.
DEFINE FILE START3 DAY01/D15.2 = IF (RECORDDAY EQ 1) THEN S_PAR_SHARES ELSE 0; DAY02/D15.2 = IF (RECORDDAY EQ 2) THEN S_PAR_SHARES ELSE 0; DAY03/D15.2 = IF (RECORDDAY EQ 3) THEN S_PAR_SHARES ELSE 0; DAY04/D15.2 = IF (RECORDDAY EQ 4) THEN S_PAR_SHARES ELSE 0; DAY05/D15.2 = IF (RECORDDAY EQ 5) THEN S_PAR_SHARES ELSE 0; DAY06/D15.2 = IF (RECORDDAY EQ 6) THEN S_PAR_SHARES ELSE 0; DAY07/D15.2 = IF (RECORDDAY EQ 7) THEN S_PAR_SHARES ELSE 0; DAY08/D15.2 = IF (RECORDDAY EQ 8) THEN S_PAR_SHARES ELSE 0; DAY09/D15.2 = IF (RECORDDAY EQ 9) THEN S_PAR_SHARES ELSE 0; DAY10/D15.2 = IF (RECORDDAY EQ 10) THEN S_PAR_SHARES ELSE 0; DAY11/D15.2 = IF (RECORDDAY EQ 11) THEN S_PAR_SHARES ELSE 0; DAY12/D15.2 = IF (RECORDDAY EQ 12) THEN S_PAR_SHARES ELSE 0; DAY13/D15.2 = IF (RECORDDAY EQ 13) THEN S_PAR_SHARES ELSE 0; DAY14/D15.2 = IF (RECORDDAY EQ 14) THEN S_PAR_SHARES ELSE 0; DAY15/D15.2 = IF (RECORDDAY EQ 15) THEN S_PAR_SHARES ELSE 0; DAY16/D15.2 = IF (RECORDDAY EQ 16) THEN S_PAR_SHARES ELSE 0; DAY17/D15.2 = IF (RECORDDAY EQ 17) THEN S_PAR_SHARES ELSE 0; DAY18/D15.2 = IF (RECORDDAY EQ 18) THEN S_PAR_SHARES ELSE 0; DAY19/D15.2 = IF (RECORDDAY EQ 19) THEN S_PAR_SHARES ELSE 0; DAY20/D15.2 = IF (RECORDDAY EQ 20) THEN S_PAR_SHARES ELSE 0; DAY21/D15.2 = IF (RECORDDAY EQ 21) THEN S_PAR_SHARES ELSE 0; END TABLE FILE START3 PRINT CONCATVAR1 HOLDDATESD S_PAR_SHARES DAY01 DAY02 DAY03 DAY04 DAY05 DAY06 DAY07 DAY08 DAY09 DAY10 DAY11 DAY12 DAY13 DAY14 DAY15 DAY16 DAY17 DAY18 DAY19 DAY20 DAY21 ON TABLE HOLD AS START4 END |
Step 5: Sum the shares BY concatvar1 (described above). Once again we can set &ECHO = ON.
TABLE FILE START4
SUM CONCATVAR1 S_PAR_SHARES
DAY01
DAY02
DAY03
DAY04
DAY05
DAY06
DAY07
DAY08
DAY09
DAY10
DAY11
DAY12
DAY13
DAY14
DAY15
DAY16
DAY17
DAY18
DAY19
DAY20
DAY21
BY CONCATVAR1 NOPRINT
ON TABLE HOLD AS START5
END
0 NUMBER OF RECORDS IN TABLE= 53 LINES= 3 |
Step 6: Calculate the financial difference between each of the ‘day’ fields.
This step once again requires the creation of a few DEFINE’s. The code to generate the DEFINE’s may be seen in Appendix D. The actual DEFINES may be seen immediately below. Business rules state that if the financial values at the start are the same as the value at the end date, then WHAT_AM_I becomes a *. If during the period there is a purchase then WHAT_AM_I becomes a B. If there is a purchase and then a part is sold then WHAT_AM_I becomes an B/S(This becomes clearer in Appendix E). Sells only are indicated by an S. The reader should remember that once a “lot” is created there are NEVER any additional shares added.
Step 7: Run the final extract See Appendix E
Once again we can set &ECHO = ON to see how the final extract is created. The results may be seen below:
DEFINE FILE START5
WHAT_AM_I/A3 = IF DAY01 EQ DAY21 THEN '*' ELSE
IF DAY02 GT DAY01 THEN 'B' ELSE
IF DAY03 GT DAY02 THEN 'B' ELSE
IF DAY04 GT DAY03 THEN 'B' ELSE
IF DAY05 GT DAY04 THEN 'B' ELSE
IF DAY06 GT DAY05 THEN 'B' ELSE
IF DAY07 GT DAY06 THEN 'B' ELSE
IF DAY08 GT DAY07 THEN 'B' ELSE
IF DAY09 GT DAY08 THEN 'B' ELSE
IF DAY10 GT DAY09 THEN 'B' ELSE WHAT_AM_I;
WHAT_AM_I = IF DAY11 GT DAY10 THEN 'B' ELSE
IF DAY12 GT DAY11 THEN 'B' ELSE
IF DAY13 GT DAY12 THEN 'B' ELSE
IF DAY14 GT DAY13 THEN 'B' ELSE
IF DAY15 GT DAY14 THEN 'B' ELSE
IF DAY16 GT DAY15 THEN 'B' ELSE
IF DAY17 GT DAY16 THEN 'B' ELSE
IF DAY18 GT DAY17 THEN 'B' ELSE
IF DAY19 GT DAY18 THEN 'B' ELSE
IF DAY20 GT DAY19 THEN 'B' ELSE WHAT_AM_I;
WHAT_AM_I = IF DAY21 GT DAY20 THEN 'B' ELSE
WHAT_AM_I;
WHAT_AM_I = IF DAY02 LT DAY01 THEN 'S' ELSE
IF DAY03 LT DAY02 THEN 'S' ELSE
IF DAY04 LT DAY03 THEN 'S' ELSE
IF DAY05 LT DAY04 THEN 'S' ELSE
IF DAY06 LT DAY05 THEN 'S' ELSE
IF DAY07 LT DAY06 THEN 'S' ELSE
IF DAY08 LT DAY07 THEN 'S' ELSE
IF DAY09 LT DAY08 THEN 'S' ELSE
IF DAY10 LT DAY09 THEN 'S' ELSE
IF DAY11 LT DAY10 THEN 'S' ELSE WHAT_AM_I;
WHAT_AM_I = IF DAY12 LT DAY11 THEN 'S' ELSE
IF DAY13 LT DAY12 THEN 'S' ELSE
IF DAY14 LT DAY13 THEN 'S' ELSE
IF DAY15 LT DAY14 THEN 'S' ELSE
IF DAY16 LT DAY15 THEN 'S' ELSE
IF DAY17 LT DAY16 THEN 'S' ELSE
IF DAY18 LT DAY17 THEN 'S' ELSE
IF DAY19 LT DAY18 THEN 'S' ELSE
IF DAY20 LT DAY19 THEN 'S' ELSE
IF DAY21 LT DAY20 THEN 'S' ELSE WHAT_AM_I;
BUY/D15.2 = IF DAY01 EQ DAY21 THEN 0 ELSE
IF DAY02 GT DAY01 THEN DAY02 ELSE
IF DAY03 GT DAY02 THEN DAY03 ELSE
IF DAY04 GT DAY03 THEN DAY04 ELSE
IF DAY05 GT DAY04 THEN DAY05 ELSE
IF DAY06 GT DAY05 THEN DAY06 ELSE
IF DAY07 GT DAY06 THEN DAY07 ELSE
IF DAY08 GT DAY07 THEN DAY08 ELSE
IF DAY09 GT DAY08 THEN DAY09 ELSE
IF DAY10 GT DAY09 THEN DAY10 ELSE
IF DAY11 GT DAY10 THEN DAY11 ELSE BUY;
BUY = IF DAY12 GT DAY11 THEN DAY12 ELSE
IF DAY13 GT DAY12 THEN DAY13 ELSE
IF DAY14 GT DAY13 THEN DAY14 ELSE
IF DAY15 GT DAY14 THEN DAY15 ELSE
IF DAY16 GT DAY15 THEN DAY16 ELSE
IF DAY17 GT DAY16 THEN DAY17 ELSE
IF DAY18 GT DAY17 THEN DAY18 ELSE
IF DAY19 GT DAY18 THEN DAY19 ELSE
IF DAY20 GT DAY19 THEN DAY20 ELSE
IF DAY21 GT DAY20 THEN DAY21 ELSE BUY;
SELL/D15.2 = IF DAY01 EQ DAY21 THEN 0 ELSE SELL;
SELL = IF DAY02 LT DAY01 THEN SELL + (DAY01-DAY02) ELSE SELL;
SELL = IF DAY03 LT DAY02 THEN SELL + (DAY02-DAY03) ELSE SELL;
SELL = IF DAY04 LT DAY03 THEN SELL + (DAY03-DAY04) ELSE SELL;
SELL = IF DAY05 LT DAY04 THEN SELL + (DAY04-DAY05) ELSE SELL;
SELL = IF DAY06 LT DAY05 THEN SELL + (DAY05-DAY06) ELSE SELL;
SELL = IF DAY07 LT DAY06 THEN SELL + (DAY06-DAY07) ELSE SELL;
SELL = IF DAY08 LT DAY07 THEN SELL + (DAY07-DAY08) ELSE SELL;
SELL = IF DAY09 LT DAY08 THEN SELL + (DAY08-DAY09) ELSE SELL;
SELL = IF DAY10 LT DAY09 THEN SELL + (DAY09-DAY10) ELSE SELL;
SELL = IF DAY11 LT DAY10 THEN SELL + (DAY10-DAY11) ELSE SELL;
SELL = IF DAY12 LT DAY11 THEN SELL + (DAY11-DAY12) ELSE SELL;
SELL = IF DAY13 LT DAY12 THEN SELL + (DAY12-DAY13) ELSE SELL;
SELL = IF DAY14 LT DAY13 THEN SELL + (DAY13-DAY14) ELSE SELL;
SELL = IF DAY15 LT DAY14 THEN SELL + (DAY14-DAY15) ELSE SELL;
SELL = IF DAY16 LT DAY15 THEN SELL + (DAY15-DAY16) ELSE SELL;
SELL = IF DAY17 LT DAY16 THEN SELL + (DAY16-DAY17) ELSE SELL;
SELL = IF DAY18 LT DAY17 THEN SELL + (DAY17-DAY18) ELSE SELL;
SELL = IF DAY19 LT DAY18 THEN SELL + (DAY18-DAY19) ELSE SELL;
SELL = IF DAY20 LT DAY19 THEN SELL + (DAY19-DAY20) ELSE SELL;
SELL = IF DAY21 LT DAY20 THEN SELL + (DAY20-DAY21) ELSE SELL;
WHAT_AM_I = IF (BUY GT 0) AND (SELL GT 0) THEN 'B/S' ELSE WHAT_AM_I;
END
TABLE FILE START5
PRINT WHAT_AM_I BUY SELL
DAY01
DAY02
DAY03
DAY04
DAY05
DAY06
DAY07
DAY08
DAY09
DAY10
DAY11
DAY12
DAY13
DAY14
DAY15
DAY16
DAY17
DAY18
DAY19
DAY20
DAY21
BY CONCATVAR1
ON TABLE HOLD AS ROLLFWD
END |
Step 8
Our report may be run against the hold file ROLLFWD.
The report code and display may be seen in APPENDIX F.
Click here to see the Appendix file.
If any questions, please contact Steve at okeofs1@cox.net.