 |
MODIFY your thinking.. let’s report with hierarchies By Steve Simon |
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 |
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 , $ |
Fig. 2
FILENAME=CALENFOC, SUFFIX=FOC , $ |
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 CREATE FILE CALEN1 CASE WRITEM0 CASE WRITEM CASE ROOTS DATA VIA FIDEL |
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 |
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 |
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 |
Fig. 7
CASE ROOTS |
Fig. 8
2008 -* from WRITEM0 |
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 |
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 |
Fig. 12
FILEDEF OBRIENZ DISK C:/FUSE2008/CALENDARRAWDATA.TXT MODIFY FILE CALENDAR |
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'); |
Fig. 16
2008 -* Len = 0 |
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 |
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 |
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 FOR |
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.