MODIFY your thinking.. let’s report with hierarchies



Introduction

Hierarchies are certainly one of the better additions to the WebFOCUS arsenal, especially to those of us who are in the financial world. More often than not, developers lack the vital business knowledge to ensure that the reports that they generate, are not only efficient but effective. This is one of the reasons that we (amongst others) have implemented hierarchies as a part of our reporting standards.
Having attended a few “hierarchy oriented” sessions at the most recent Information Builders Summit, I was not surprised to see the “old FOCUS file”  utilized as the data source along with the appropriate master file.
The problem that surfaced (to many folks) was the age old problem of how do I get the data into the hierarchy FOCUS file…

Getting Started

There is an old adage that there are many way to complete a task. When it comes to hierarchies, they are always “demo’d” as FOCUS database files. This was true at the summit as well.
While the new “WITHIN” clause makes it possible to create as hierarchy based upon a “flat file” (according to Harry Lotrowski IBI), the author wished to utilize a FOCUS database file.
Our first inclination is to take THAT flat file and to “table it” out and hold it in a FOCUS FORMAT
(See Fig. 1)

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS MYHIER FORMAT FOCUS INDEX YEARR
END
-RUN

Fig. 1

This does not work most of the time as you are dealing with a FOCUS file, which expects the master file to have the same name, indexing and structure as the database. Additionally the internals of the FOCUS database differ from a flat file in many ways. One example is that a flat file can use any master file as long as the layout is the same. Plain and simply it is not worth the effort to attempt to create a workable hierarchy database file in this manner. Fig 2 shows the master file created from the code in Fig.1.

Fig 3, the form of master file required to create a hierarchy.

FILENAME= CALENFOC , SUFFIX=FOC     , $
SEGMENT=SEG01, SEGTYPE=S1, $ 
FIELDNAME=FOCLIST     , ALIAS=E01, USAGE=I5, $ 
FIELDNAME=DAYY        , ALIAS=E02, USAGE=A8, $ 
FIELDNAME=YEARRMTH    , ALIAS=E03, USAGE=A6, $ 
FIELDNAME=YEARR       , ALIAS=E04, USAGE=A4, $ 
FIELDNAME=HOLDDATE    , ALIAS=E05, USAGE=A8, MISSING=ON, $

Fig. 2

FILENAME=CALENFOC, SUFFIX=FOC     , $ 
SEGMENT=SEG01, SEGTYPE=S1, $ 
FIELDNAME=FOCLIST     , ALIAS=E01, USAGE=I5, $ 
FIELDNAME=DAYY        , 
ALIAS=DAYY,  FORMAT=A8, TITLE='Hold Date',  $FIELDNAME=YEARRMTH        , 
ALIAS=YEARMTH,   FORMAT=A6, TITLE='Crnt YearMonth', PROPERTY=PARENT_OF, REFERENCE=DAYY, $ 
FIELDNAME=YEARR           ,
ALIAS=YEARRR,   FORMAT=A4, TITLE='Year', 
PROPERTY=PARENT_OF, REFERENCE=YEARRMTH, $ FIELDNAME=HOLDDATE,         
ALIAS=HDATE,   FORMAT=A8,
TITLE='HoldDate', MISSING=ON, $

Fig. 3

As discussed above, another alternative is to use the new “WITHIN” clause in the master file , with a flat file data source.
No matter which way one looks at the situation, one cannot help but think that things are becoming a bit messy.

Fear not dear reader, “we older FOCUS folks” have come to your rescue with a brilliant part of WebFOCUS seldom utilized.. MODIFY.

For the sake of argument, assume that we wanted to create a financial report utilizing a “Calendar Hierarchy”. The hierarchy would then be linked to fact data by means of a common key.

A calendar is a good example as each year has 12 months and each month has many days.
Our first challenge is to create the raw data to feed the hierarchy and once again MODIFY comes to the rescue with its own inherent CASE LOGIC.

Creating the data for our hierarchy
The code shown below in Fig1, is all that is required to generate the necessary raw data to populate our hierarchy. For those of us that are not familiar with MODIFY, we shall step through the code on a line for line basis.

-* File CodeACalendar.fex
FILEDEF OBRIENZ DISK C:\FUSE2008\CALENDARRAWDATA.TXT
FILEDEF CALEN1   DISK C:\FUSE2008\CALEN1.FOC
APP HOLD FINANCIAL_REPORTS
-RUN CREATE FILE CALEN1
-RUN
MODIFY FILE CALEN1
COMPUTE STOPPER/I9 = 1;
COMPUTE STOPPER1/I9 = 1;
COMPUTE YEARR/YY = '2008';
COMPUTE YEARRMTH/YYM = '200801';
COMPUTE YEARRMTHDAY/YYMD = '20080101';
COMPUTE YEARRMTHDAY1/A8YYMD = YEARRMTHDAY;
COMPUTE YEARRMTHDAY2/A8 = YEARRMTHDAY1;
COMPUTE YEARRMTH2/A6 = SUBSTR(8,YEARRMTHDAY1,1,6,6,'A6');
COMPUTE YEARRMTH3/A6YYM = YEARRMTH;
COMPUTE YEARRMTH4/A6    = YEARRMTH3;
GOTO WRITEM0 CASE WRITEM0
TYPE ON OBRIENZ "<YEARR>"
GOTO WRITEM
ENDCASE CASE WRITEM
IF STOPPER EQ 367 THEN GOTO EXIT;
IF STOPPER EQ 1 THEN PERFORM ROOTS;
COMPUTE YEARRMTHDAY1 = YEARRMTHDAY;
COMPUTE YEARRMTHDAY2 = YEARRMTHDAY1;
COMPUTE YEARRMTH2    = SUBSTR(8,YEARRMTHDAY1,1,6,6,'A6');
TYPE ON OBRIENZ "<YEARR><YEARRMTH2><YEARRMTHDAY2>"
COMPUTE YEARRMTHDAY =  YEARRMTHDAY + 1;
COMPUTE STOPPER = STOPPER + 1;
GOTO WRITEM
ENDCASE CASE ROOTS
IF STOPPER1 GT 12 THEN GOTO ENDCASE;
COMPUTE YEARRMTH3       = YEARRMTH;
COMPUTE YEARRMTH4       = YEARRMTH3;
TYPE ON OBRIENZ "<YEARR><YEARRMTH4>"
COMPUTE YEARRMTH = YEARRMTH  + 1;
COMPUTE STOPPER1 = STOPPER1 + 1;
PERFORM ROOTS
ENDCASE DATA VIA FIDEL
END

Fig. 4

The CALEN  MASTER  file is fairly simple and the CALEN database has no purpose other than to generate our output data. Remember that we are utilizing this database SOLELY to take advantage of MODIFY’s Case Logic.
Also note that the name of the field within the master file is NOT relevant.

FILE=ABC            ,
SUFFIX=FOC                                    
SEGNAME=ABC     ,
SEGTYPE=S0                                       
FIELDNAME   =ANYOLDNAME          
ALIAS = ANYOLDNAME, 
FORMAT = A4      ,$     

Before one can utilize a FOCUS database, it must be “FILEDEF’d”  and “CREATED”.
This is where the following lines of code come into play.

FILEDEF CALEN1   DISK C:\FUSE2008\CALEN1.FOC -RUN CREATE FILE CALEN1 -RUN MODIFY FILE CALEN1 ….

We then define a few variables that will help in the generation of the necessary output

COMPUTE STOPPER/I9          = 1; 
COMPUTE STOPPER1/I9         = 1; 
COMPUTE YEARR/YY            = '2008';   
COMPUTE YEARRMTH/YYM        = '200801'; 
COMPUTE YEARRMTHDAY/YYMD    = '20080101'; 
COMPUTE YEARRMTHDAY1/A8YYMD = YEARRMTHDAY; 
COMPUTE YEARRMTHDAY2/A8     = YEARRMTHDAY1; 
COMPUTE YEARRMTH2/A6        = SUBSTR(8,YEARRMTHDAY1,1,6,6,'A6'); 
COMPUTE YEARRMTH3/A6YYM     = YEARRMTH; 
COMPUTE YEARRMTH4/A6        = YEARRMTH3;

Fig. 5

STOPPER and STOPPER1 are utilized as counters for iteration purposes.
YEARR contains the four digit year.
YEARRMTH will be incremented below to cater for the months from January through December.
YEARRMTHDAY caters for the individual days of the year.

Now that we have declared all the necessary variables, it is time to discuss the necessary CASE logic involved in the process.
As mentioned above, YEARR and for that matter all our “values” are written out to our extract file via the TYPE ON statement (see Fig 6). We first go to WRITEM0 to write the YEARR to the output file. See fig. 6.
We then go to CASE WRITEM.

GOTO WRITEM0

CASE WRITEM0 TYPE ON OBRIENZ "<YEARR>" GOTO WRITEM ENDCASE

Fig. 6

Once at CASE WRITEM (Fig 7) the initial value of the variable STOPPER has a value of 1 and therefore the CASE ‘ROOTS’ is executed. ‘ROOTS’ is then executed 12 times (See Fig. 8) incrementing the month each iteration. This  creates your YEAR MONTH combinations. The YEARR and YEARRMTH values are written out to the extract.
After ROOTS has executed 12 times we return to WRITEM and your extract file may be seen in Fig. 9:

 

CASE WRITEM
IF STOPPER EQ 367 THEN GOTO EXIT;
IF STOPPER EQ 1 THEN PERFORM ROOTS;
COMPUTE YEARRMTHDAY1 = YEARRMTHDAY;
COMPUTE YEARRMTHDAY2 = YEARRMTHDAY1;
COMPUTE YEARRMTH2    = SUBSTR(8,YEARRMTHDAY1,1,6,6,'A6');
TYPE ON OBRIENZ "<YEARR><YEARRMTH2><YEARRMTHDAY2>"
COMPUTE YEARRMTHDAY =  YEARRMTHDAY + 1;
COMPUTE STOPPER = STOPPER + 1;
GOTO WRITEM
ENDCASE

Fig. 7

CASE ROOTS
IF STOPPER1 GT 12 THEN GOTO ENDCASE;
COMPUTE YEARRMTH3       = YEARRMTH;
COMPUTE YEARRMTH4       = YEARRMTH3;
TYPE ON OBRIENZ "<YEARR><YEARRMTH4>"
COMPUTE YEARRMTH = YEARRMTH  + 1;
COMPUTE STOPPER1 = STOPPER1 + 1;
PERFORM ROOTS
ENDCASE

Fig. 8

       2008         -* from WRITEM0 
       2008 200801  -* from WRITEM 
       2008 200802  -* from WRITEM
       2008 200803  -* from WRITEM
       .......
       2008 200812  -* from WRITEM

Fig. 9

After having returned from ROOTS it is time to write our daily records to the extract file.
This must be done in a special manner.
First off we write the year, then the year-month combination and then year-month-day combination to file.
See Fig. 10

200820080120080101
200820080120080102
200820080120080103
200820080120080104
200820080120080105

				……

Fig 10

We continue this process until the variable STOPPER has reached a value of 367. Stopper was initialized to 1 and 2008 has 366 days.
Once complete, your extract should be similar to the one shown in Fig 11.

2008
2008200801
2008200802
2008200803
2008200804
2008200805
2008200806
2008200807
2008200808
2008200809
2008200810
2008200811
2008200812
200820080120080101
200820080120080102
200820080120080103
200820080120080104
200820080120080105
200820080120080106
200820080120080107
200820080120080108
200820080120080109
200820080120080110
200820080120080111
200820080120080112
200820080120080113
200820080120080114
……………
200820081220081224
200820081220081225
200820081220081226
200820081220081227
200820081220081228
200820081220081229
200820081220081230
200820081220081231

Fig 11

You will note that there are two blank spaces in front of each record.
One has two options. The first is to do a global replace and remove these blanks.
The other is the handle the blanks when LOADING the FOCUS database. In Fig. 11 the author removed the blanks. The choice is left up to the reader.

Inserting the raw data into the database
Fig. 13 shows the code necessary to load our raw data file into the FOCUS database.
Once again, for those of us that are not familiar with MODIFY, we shall go through the code line by line.
Our MASTER file is shown in Fig 12

FILE=CALENDAR       ,SUFFIX=FOC
SEGNAME=DATTES   ,SEGTYPE=S01
FIELDNAME=DAYY,           ALIAS=DAYY,  FORMAT=A8,
TITLE='Hold Date', FIELDTYPE=I, $
FIELDNAME=YEARRMTH,    ALIAS=YEARMTH,   FORMAT=A6,
TITLE='Crnt YearMonth',FIELDTYPE=I, 
PROPERTY=PARENT_OF, REFERENCE=DAYY, $
FIELDNAME=YEARR,    ALIAS=YEARRR,   FORMAT=A4,
TITLE='Year', FIELDTYPE=I,
PROPERTY=PARENT_OF, REFERENCE=YEARRMTH, $
FIELDNAME=HOLDDATE,   ALIAS=HDATE,   FORMAT=A8,
TITLE='HoldDate', MISSING=ON, $

Fig. 12

 

 

FILEDEF OBRIENZ DISK C:/FUSE2008/CALENDARRAWDATA.TXT
FILEDEF CALENDAR DISK C:/FUSE2008/Calendar.foc
-RUN
CREATE FILE CALENDAR
-RUN MODIFY FILE CALENDAR
COMPUTE LEN/I9 = 0;
COMPUTE TEMPDATE/A8YYMD=;
COMPUTE TEMPDATE1/I8YYMD = ;
COMPUTE LEADINGBLANKS/A2 = ;
FIXFORM LEADINGBLANKS YEARR/A4 YEARRMTH/A6 DAYY/A8  
COMPUTE LEN = ARGLEN(8,DAYY,'I9');
COMPUTE HOLDDATE = IF (LEN LT 7) THEN '        ' ELSE DAYY;
MATCH DAYY
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON OBRIENZ
END
-RUN

Fig. 13

Once again we issue our normal FILEDEF’s and CREATE / TRUNCATE our FOCUS database.
See Fig.14

FILEDEF OBRIENZ DISK C:/FUSE2008/CALENDARRAWDATA.TXT
FILEDEF CALENDAR DISK C:/FUSE2008/Calendar.foc
-RUN
CREATE FILE CALENDAR
-RUN

Fig. 14

Now let’s have a look at the MODIFY code, step by step.
For this load the author DID NOT remove the leading blanks (as was done in Fig. 11). The purpose was to show the reader how we can handle the leading blanks from within the MODIFY.
Note that a variable called LEADINGBLANKS was declared as an Alpha 2 format. This will be used to ‘READ’ the two blanks. We do nothing with the variable after that.
The “READ” is performed by the FIXFORM command. See Fig. 15

 

FIXFORM LEADINGBLANKS YEARR/A4 YEARRMTH/A6 DAYY/A8

Fig. 15

We then wish to create our field that will be utilized to JOIN the hierarchy to the fact data. This field is called HOLDDATE. The reason that we check the length of ‘DAYY’ is because the first records processed are NOT DAILY records. Record 1 is the YEARR. (A4), the next 12 are Year – Month combinations, thus there is no such thing as a valid HOLDDATE. These records are purely for usage by our hierarchy.
We therefore make the HOLDDATE blank for these records.
(See Fig 16 for the relevant code and Fig17 to understand why HOLDDATE is blank for the first few records.)

 

COMPUTE LEN = ARGLEN(8,DAYY,'I9'); 
COMPUTE HOLDDATE = IF (LEN LT 7) THEN '        ' ELSE DAYY;

Fig. 16

2008                                             -* Len = 0
2008200801                                 -* Len = 0
2008200802                                 -* Len = 0
2008200803                                 -* Len = 0
2008200804                                 -* Len = 0
2008200805                                 -* Len = 0
2008200806                                 -* Len = 0
2008200807                                 -* Len = 0
2008200808                                 -* Len = 0
2008200809                                 -* Len = 0
2008200810                                 -* Len = 0
2008200811                                 -* Len = 0
2008200812                                 -* Len = 0
200820080120080101                 -* Len = 8
200820080120080102                 -* Len = 8 

Fig. 17

The actual data insertion into the FOCUS database is done with the following code.
What we “tell” the “MODIFY” routine to do is to MATCH the key (our incoming ‘DAYY’) with the ‘days’ already in the database. If the ‘DAYY’ exists then we “REJECT” the incoming record, otherwise we “INCLUDE” it into our database (See Fig. 18).

 

MATCH DAYY ON NOMATCH INCLUDE ON MATCH REJECT

Fig. 18

The “DATA” mentioned in Fig. 19 is actually handled before any data processing begins as it tells the MODIFY where to find the incoming data. This is done by means of the data file’s DDNAME.

DATA ON OBRIENZ END

Fig. 19

 

Once complete, the data within database should resemble the data shown in Fig. 20.

Fig. 20

Utilizing our hierarchy
The reader is reminded that we shall be using the HOLDDATE field in Fig 20 to join to our fact data.
The fact data may be seen in Fig. 21

Fig. 21

Now that we have all that we require, we are in a position to create our hierarchy based report.
Opening a new procedure in Developer Studio, we request our CALENDAR master file. See Fig 22.

Fig. 22

Our report painter then comes up. See Fig 23.

Fig. 23
Within the painter, the author has defined the report. Note that the FOR field is DAYY (see tool tip). The remaining fields in the report painter view are defined as BY fields.
Switching to MATRIX view we see the developed hierarchy ready for use.  See Fig 24.

Fig. 24

Coding our report
Fig 25 shows the basics of the code that is required for our report.

FILEDEF CALENDAR DISK C:/ibi/apps/corefocusfrl/calendar.foc
 FILEDEF CALENFOC DISK C:/ibi/apps/corefocusfrl/CALENFOC.foc
 JOIN CLEAR *
-RUN
SET ALL = OFF
-RUN
TABLE FILE CALENDARFACT     GET FACT DATA 
PRINT *
BY HOLDDATE NOPRINT
ON TABLE HOLD AS CALENFOC FORMAT FOCUS INDEX HOLDDATE
END
-RUN
JOIN DAYY IN CALENDAR TO HOLDDATE IN CALENFOC AS J0;  -RUN
TABLE FILE CALENDAR
PRINT
     DAYY
     SSB_FUND
     ASSETID
     ACCOUNTNUM
     LOTID
     COUPON
     SHARESPAR
     MARKETVALL
     MARKETVALB
WHERE MARKETVALB NE 0 FOR
     DAYY
" " LABEL R1 OVER
'2008' WITH CHILDREN ALL AS CAPTION LABEL R2
HEADING
" "
" "
"                                        <+0> "
"                                        <+0>           <+0>FASB Report: <+0>Daily"
" "
FOOTING
""
WHERE HOLDDATE ne '   ';
ON TABLE NOTOTAL
ON TABLE SET BLANKINDENT ON
				.. 
		

Fig. 25

Fig. 26 shows the same report within the Report Painter.

Fig. 26

The finished report may be seen in Fig 27.

Fig. 27

After thoughts The demanding world of financial reporting does have some silver linings. The FOCUS hierarchy structure helps our developers make some sense of the financial data jungle. Creating hierarchies becomes a breeze once the developer obtains an adequate knowledge of how to create and properly utilize them. The outcome helps our senior management effect the decisions so vital to our corporate interest.

If any questions, please contact Steve at okeofs1@cox.net.