Hierarchy Drilldown Using Dialogue Manager and GUI

Hierarchy Drilldown Using Dialogue Manager and GUI

By Ken Lane

Challenge:

A challenge frequently encountered by today's Business Intelligence consultant is to live up to the expectation placed upon them through pre-sales presentations. How many times have you heard in a pre-sales meeting that you can develop robust applications without having to touch a line of code? I know I have heard it numerous times and once it's out there, the client expects it.

This was truly evident in my most recent engagement. A major concern of the client on this project was that of support. The client was concerned that there would not be sufficient skills within the organization to maintain the reporting application I created. Other areas of the organization had the benefit of a couple of years of exposure to WebFOCUS; with developers who quickly became skilled at coding procedures. In this part of the organization, no such resource existed. Hence, one of the requirements of the project was to ensure that everything was done within the GUI.

For the most part, the requirements for the project were not overly complicated and could certainly fit into the Developer Studio GUI. However, there was one requirement that did present a challenge. The users required a report that would allow them to access the reportable measures through the top level in the flattened hierarchy, with the ability to drilldown through each subsequent level. In the past, this was easily accomplished through the use of parameters passed through each successive level. An example of such code would be:

-*----------------------------------------------------------------------------
-* This logic assumes we are starting at the top level of the hierarchy.  
-* Let us also assume that we are dealing with an organization hierarchy.
-*----------------------------------------------------------------------------
-*
-*----------------------------------------------------------------------------
-* Establish defaults for the previous and current levels in the hierarchy.
-*----------------------------------------------------------------------------
-*
-DEFAULT &PREV_HIER_LEVEL = '0';
-DEFAULT &CURR_HIER_LEVEL = '1';
-*
-*----------------------------------------------------------------------------
-* Establish the default for the org id (top of the hierarchy).
-*----------------------------------------------------------------------------
-*
-DEFAULT &PREV_ORG_ID = '00000001';
-*
-*----------------------------------------------------------------------------
-* Establish the value of the next hierarchy level for use in the drilldown
-*----------------------------------------------------------------------------
-*
-SET &NEXT_HIER_LEVEL = &CURR_HIER_LEVEL + 1;
-*
TABLE FILE FLATTENED_HIERARCHY
SUM MEASURE_NAME
   BY LEVEL&CURR_HIER_LEVEL
-IF &CURR_HIER_LEVEL EQ '1' THEN GOTO :NO_WH ;
WHERE LEVEL&PREV_HIER_LEVEL EQ '&PREV_ORG_ID';
-:NO_WH
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=LEVEL&CURR_HIER_LEVEL, 
     FOCEXEC=hierarchy1(PREV_ORG_ID = LEVEL&CURR_HIER_LEVEL \
               PREV_HIER_LEVEL = &CURR_HIER_LEVEL \
               CURR_HIER_LEVEL = &NEXT_HIER_LEVEL),$
ENDSTYLE
END

While the above logic is only one way of accomplishing such a task, it does highlight the fact that a single module can meet the drilldown requirements in a dynamic fashion with each successive level in the hierarchy being accessible through the manipulation of parameter values.

Now try opening this report in Report Painter.

You can't. As soon as you try, you receive the following message:



Mind you, the logic involved to create such as report is very minimal and can be done with little effort. Anyone who has developed reports such as these can attest to this. However, it does go against one of the requirements of the project – that being, everything must have the ability to be supported through the GUI.

So, how did I solve the problem?

Solution:

Actually, it proved to be much easier than first expected. After contemplating what I was trying to do and the restrictions of Report Painter, I developed the following solution in a very short period of time.

-*----------------------------------------------------------------------------
-* Establish default values for the hierarchy level and the organization id
-*----------------------------------------------------------------------------
-*
-DEFAULT &HIER_LEVEL = '1';
-DEFAULT &PREV_ORG_ID = '00000001';
-*
-*----------------------------------------------------------------------------
-* Establish the value of the next hierarchy level for use in the drilldown
-*----------------------------------------------------------------------------
-*
-SET &NEXT_HIER_LEVEL = &HIER_LEVEL + 1;
-*

For the processing regarding the drilldown, I needed to establish the current level and extract THAT column. I wasn’t concerned with the column name, but rather the contents of the column. By checking the value of &HIER_LEVEL, I could determine which column value is required.

Note that it is not the column name, but rather a new column that contains the SAME data as the required column (for the current level). Issuing a WHERE clause on this new column is essence allowed me to perform a where on that level in the hierarchy. In reality, the new columns created through the DEFINE permitted me to dynamically set their values to the current values in the hierarchy.

By doing this, I eliminated the need to have Dialogue Manager commands within the TABLE FILE and thus, the module could be opened within Developer Studio Report Painter.

-*----------------------------------------------------------------------------
-* Define the necessary columns.
-*
-* Also included are four additional columns:
-*   WHERE_ORG_ID       - The org id for the previous level in the hierarchy.
-*   WHERE_ORG_DESC - The org description for the previous level.
-*   ORG_ID                       - The org id for the current level.
-*   ORG_ID_DESCR        - The org description for the current level.
-*----------------------------------------------------------------------------
-*
DEFINE FILE FLATTENED_HIERARCHY
-*
 WHERE_ORG_ID/A15 = IF ('&HIER_LEVEL' EQ '1') THEN LEVEL1 ELSE
    IF ('&HIER_LEVEL' EQ '2') THEN LEVEL1 ELSE
    IF ('&HIER_LEVEL' EQ '3') THEN LEVEL2 ELSE
    IF ('&HIER_LEVEL' EQ '4') THEN LEVEL3 ELSE
    IF ('&HIER_LEVEL' EQ '5') THEN LEVEL4 ELSE
    IF ('&HIER_LEVEL' EQ '6') THEN LEVEL5 ELSE
    IF ('&HIER_LEVEL' EQ '7') THEN LEVEL6 ELSE
    IF ('&HIER_LEVEL' EQ '8') THEN LEVEL7 ELSE ' ';
-*
 WHERE_ORG_DESC/A50 = IF ('&HIER_LEVEL' EQ '1') THEN LEVEL1_DESC ELSE
    IF ('&HIER_LEVEL' EQ '2') THEN LEVEL1_DESC ELSE
    IF ('&HIER_LEVEL' EQ '3') THEN LEVEL2_DESC ELSE
    IF ('&HIER_LEVEL' EQ '4') THEN LEVEL3_DESC ELSE
    IF ('&HIER_LEVEL' EQ '5') THEN LEVEL4_DESC ELSE
    IF ('&HIER_LEVEL' EQ '6') THEN LEVEL5_DESC ELSE
    IF ('&HIER_LEVEL' EQ '7') THEN LEVEL6_DESC ELSE
    IF ('&HIER_LEVEL' EQ '8') THEN LEVEL7_DESC ELSE ' ';
-* 
ORG_ID/A15 = IF ('&HIER_LEVEL' EQ '1') THEN LEVEL1 ELSE
    IF ('&HIER_LEVEL' EQ '2') THEN LEVEL2 ELSE
    IF ('&HIER_LEVEL' EQ '3') THEN LEVEL3 ELSE
    IF ('&HIER_LEVEL' EQ '4') THEN LEVEL4 ELSE
    IF ('&HIER_LEVEL' EQ '5') THEN LEVEL5 ELSE
    IF ('&HIER_LEVEL' EQ '6') THEN LEVEL6 ELSE
    IF ('&HIER_LEVEL' EQ '7') THEN LEVEL7 ELSE
    IF ('&HIER_LEVEL' EQ '8') THEN LEVEL8 ELSE ' ';
-* 
ORG_ID_DESCR/A50 = IF ('&HIER_LEVEL' EQ '1') THEN LEVEL1_DESC ELSE
    IF ('&HIER_LEVEL' EQ '2') THEN LEVEL2_DESC ELSE
    IF ('&HIER_LEVEL' EQ '3') THEN LEVEL3_DESC ELSE
    IF ('&HIER_LEVEL' EQ '4') THEN LEVEL4_DESC ELSE
    IF ('&HIER_LEVEL' EQ '5') THEN LEVEL5_DESC ELSE
    IF ('&HIER_LEVEL' EQ '6') THEN LEVEL6_DESC ELSE
    IF ('&HIER_LEVEL' EQ '7') THEN LEVEL7_DESC ELSE
    IF ('&HIER_LEVEL' EQ '8') THEN LEVEL8_DESC ELSE ' ';
-*
END
-*
-*----------------------------------------------------------------------------
-* The following report allows for the drilldown to subsequent levels.
-*----------------------------------------------------------------------------

TABLE FILE FLATTENED_HIERARCHY
HEADING
"PREVIOUS HIERARCHY LEVEL < WHERE_ORG_ID < WHERE_ORG_DESC"
" "
 BY ORG_ID
 BY ORG_ID_DESCR
WHERE WHERE_ORG_ID EQ '&PREV_ORG_ID'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN, GRID=OFF, FONT='ARIAL',SIZE=9,$
TYPE=DATA, COLUMN=ORG_ID, FOCEXEC=hier_drill1(PREV_ORG_ID = ORG_ID \
   HIER_LEVEL = '&NEXT_HIER_LEVEL'),$
TYPE=HEADING, STYLE=BOLD, $
ENDSTYLE
END

If any questions, please contact Ken at kenlane@shaw.ca.