Hierarchical Reporting: BY HIERARCHY

How to:

Cube data sources such as Essbase or SAP BW are organized into dimensions and facts. Dimensions are often organized into hierarchies. The synonyms for cube data sources have attributes that describe the dimension hierarchies, and WebFOCUS has hierarchical reporting syntax that can automatically report against these hierarchies and display the results indented to show the hierarchical relationships.

WebFOCUS also supports defining dimension hierarchies in synonyms for non-cube data sources that have hierarchical data. Once hierarchical dimensions are defined in a synonym, you can issue hierarchical reporting requests against them. Non-cube synonyms with hierarchical attributes are called virtual cubes.

Dimensions are categories of data, such as Region or Time, that you use to analyze and compare business performance. Dimensions consist of data elements that are called members. For example, a Region dimension could have members England and France.

Dimension members are usually organized into hierarchies. Hierarchies can be viewed as tree-like structures where members are the nodes. For example, the Region dimension may have the element World at its top level (the root node). The World element may have children nodes (members) representing continents. Continents, in turn, can have children nodes that represent countries, and countries can have children nodes representing states or cities. Nodes with no children are called leaf nodes.

Measures are numeric values, such as Sales Volume or Net Income, that are used to quantify how your business is performing.

A cube consists of data derived from facts, which are records about individual business transactions. For example, an individual fact record reflects a sales transaction of a certain number of items of a certain product at a certain price, which occurred in a certain store at a certain moment in time. The cube contains summarized fact values for all combinations of measures and members of different dimensions.

A synonym describes a hierarchy using a set of fields that define the hierarchical structure and the relationships between the hierarchy members. WebFOCUS has special hierarchical reporting syntax for reporting on hierarchies.

Hierarchical reporting requests have several phases:
Top of page
x
Syntax: How to Specify a Hierarchy in a Master File

The data source must have at least one dimension that is organized hierarchically. The declaration for a dimension is:

DIMENSION=dimname,CAPTION=dimcaption, $

where:

dimname

Is a name for the dimension.

dimcaption

Is a label for the dimension.

The declaration for a hierarchy within the dimension is:

HIERARCHY=hname,CAPTION='hcaption',HRY_DIMENSION=dimname,
HRY_STRUCTURE=RECURSIVE, $

where:

hname

Is a name for the hierarchy.

hcaption

Is a label for the hierarchy.

dimname

Is the name of the dimension for which this hierarchy is defined.

Several fields are used to define a parent/child hierarchy. Each has a PROPERTY attribute that describes which hierarchy property it represents. Each hierarchy must have a unique identifier field. This field is called the hierarchy field. If the synonym represents a FOCUS data source, this field must be indexed (FIELDTYPE=I). The declaration for the hierarchy field is:

FIELD=hfield,ALIAS=halias,USAGE= An, [ACTUAL=Am,] WITHIN='*hierarchy',PROPERTY=UID, [TITLE='title1',] [FIELDTYPE=I,] $

where:

hfield

Is the field name for the hierarchy field.

halias

Is the alias for the hierarchy field. If the data source is relational, this must be the name of the column in the Relational DBMS.

hierarchy

Is the name of the hierarchy to which this field belongs.

USAGE= An, [ACTUAL=Am,]

Are the USAGE format and, if the data source is not a FOCUS data source, the ACTUAL format of the field.

title1

Is an optional title for the field.

Other fields defined for the hierarchy include the parent field and the caption field. Each of these fields has the same name as the hierarchy field with a suffix added. Each has a PROPERTY attribute that specifies its role in the hierarchy and a REFERENCE attribute that points to the corresponding hierarchy field.

The following is the declaration for the parent field. The parent field is needed to define the parent/child relationships in the hierarchy:

FIELD=hfield_PARENT,ALIAS=parentalias,USAGE=An,[ACTUAL=Am,] [TITLE=ptitle,]
      PROPERTY=PARENT_OF, REFERENCE=hfield, $

where:

hfield

Is the hierarchy field.

parentalias

Is the alias for the parent field. If the data source is relational, this must be the name of the column in the relational DBMS.

USAGE= An, [ACTUAL=Am,]

Are the USAGE format and, if the data source is not a FOCUS data source, the ACTUAL format of the field.

ptitle

Is a column title for the parent field.

The following is the declaration for the caption field. A caption is a descriptive title for each value of the hierarchy field. It is part of the data and, therefore, is different from a TITLE attribute in the Master File, which is a literal title for the column on the report output.

FIELD=hfield_CAPTION,ALIAS=capalias,USAGE=Ann,[ACTUAL=Amm,] [TITLE=captitle,]
        PROPERTY=CAPTION, REFERENCE=hfield, $

where:

hfield

Is the hierarchy field.

capalias

Is the alias for the caption field. If the data source is relational, this must be the name of the column in the relational DBMS.

USAGE= Ann, [ACTUAL=Amm,]

Are the USAGE format and, if the data source is not a FOCUS data source, the ACTUAL format of the field.

captitle

Is a column title for the caption field.

Example: Sample Master File With a Dimension Hierarchy

The following Master File is based on the CENTGL Master File, which has an FML hierarchy defined. This version is named NEWGL and it has a dimension hierarchy of accounts in which GL_ACCOUNT is the hierarchy field, GL_ACCOUNT_PARENT is the parent field, and GL_ACCOUNT_CAPTION is the caption field. There are other fields based on the hierarchy (GL_ACCOUNT_LEVEL, GL_ROLLUP_OP, and GL_ACCOUNT_TYPE). In addition, there is a measure field (GL_ACCOUNT_AMOUNT): 

FILE=NEWGL       ,SUFFIX=FOC,$
SEGNAME=ACCOUNTS   ,SEGTYPE=S01
DIMENSION=Accnt,CAPTION=Accnt, $
HIERARCHY=Accnt,CAPTION='Accnt',HRY_DIMENSION=Accnt,
HRY_STRUCTURE=RECURSIVE, $
FIELD=GL_ACCOUNT,GLACCT,A7,WITHIN='*Accnt',PROPERTY=UID,
            TITLE='Ledger,Account', FIELDTYPE=I, $
FIELD=GL_ACCOUNT_PARENT,GLPAR,A7, TITLE=Parent,
            PROPERTY=PARENT_OF, REFERENCE=GL_ACCOUNT, $
FIELD=GL_ACCOUNT_TYPE,GLTYPE,A1, TITLE=Type,$
FIELD=GL_ROLLUP_OP,ROLL,A1, TITLE=Op, $
FIELD=GL_ACCOUNT_LEVEL,GLLEVEL,I3, TITLE=Lev, $
FIELDNAME=GL_ACCOUNT_AMOUNT,GLAMT,D12.2, TITLE=Amount, $
FIELD=GL_ACCOUNT_CAPTION,GLCAP,A30, TITLE=Caption,
            PROPERTY=CAPTION, REFERENCE=GL_ACCOUNT, $
FIELD=SYS_ACCOUNT,ALINE,A6, TITLE='System,Account,Line', MISSING=ON, $

The following procedure loads data into this data source, as long as the Master File is available to WebFOCUS (on the path or allocated):

CREATE FILE NEWGL NOMSG
-RUN
MODIFY FILE NEWGL
COMPUTE TGL_ACCOUNT_LEVEL/A3=;
COMPUTE TGL_ACCOUNT_AMOUNT/A12=;
FIXFORM GL_ACCOUNT/A4B X3 GL_ACCOUNT_PARENT/A4B X3 GL_ACCOUNT_TYPE/A1B
FIXFORM SYS_ACCOUNT/A4B GL_ROLLUP_OP/A1B
FIXFORM TGL_ACCOUNT_LEVEL/A3B GL_ACCOUNT_CAPTION/A30B
FIXFORM TGL_ACCOUNT_AMOUNT/A12B
COMPUTE GL_ACCOUNT_LEVEL = EDIT(TGL_ACCOUNT_LEVEL);
COMPUTE GL_ACCOUNT_AMOUNT = ATODBL(TGL_ACCOUNT_AMOUNT , '12', GL_ACCOUNT_AMOUNT);
 
MATCH GL_ACCOUNT
   ON MATCH REJECT
   ON NOMATCH INCLUDE
DATA
1000          R.   +  1Profit Before Tax                          
2000   1000   R.   +  2Gross Margin                               
2100   2000   R.   +  3Sales Revenue                              
2200   2100   R.   +  4Retail Sales                               
2210   2200   R7001+  5Retail - Television                  505.00
2220   2200   R7002+  5Retail - Stereo                      505.00
2230   2200   R7003+  5Retail - Video Player                505.00
2240   2200   R7004+  5Retail - Computer                    505.00
2250   2200   R7005+  5Retail - Video Camera                505.00
2300   2100   R.   +  4Mail Order Sales                           
2310   2300   R7011+  5Mail Order - Television              505.00
2320   2300   R7012+  5Mail Order - Stereo                  505.00
2330   2300   R7013+  5Mail Order - Video Player            505.00
2340   2300   R7014+  5Mail Order - Computer                505.00
2350   2300   R7015+  5Mail Order - Video Camera            505.00
2400   2100   R.   +  4Internet Sales                             
2410   2400   R7021+  5Internet - Television                505.00
2420   2400   R7022+  5Internet - Stereo                    505.00
2430   2400   R7023+  5Internet - Video Player              505.00
2440   2400   R7024+  5Internet - Computer                  505.00
2450   2400   R7025+  5Internet - Video Camera              505.00
2500   2000   E.   -  3Cost Of Goods Sold                         
2600   2500   E.   +  4Variable Material Costs                    
2610   2600   E7101+  5Television COGS                      505.00
2620   2600   E7102+  5Stereo COGS                          505.00
2630   2600   E7103+  5Video COGS                           505.00
2640   2600   E7104+  5Computer COGS                        505.00
2650   2600   E7105+  5Video Camera COGS                    505.00
2700   2500   E7111+  4Direct Labor                         404.00
2800   2500   E7112+  4Fixed Costs                          404.00
3000   1000   E.   -  2Total Operating Expenses                   
3100   3000   E.   +  3Selling Expenses                           
3110   3100   E.   +  4Advertising                                
3112   3110   E7202+  5TV/Radio                             505.00
3114   3110   E7203+  5Print Media                          505.00
3116   3110   E7206+  5Internet Advertising                 505.00
3120   3100   E7212+  4Promotional Expenses                 404.00
3130   3100   E7213+  4Joint Marketing                      404.00
3140   3100   E7214+  4Bonuses/Commisions                   404.00
3200   3000   E.   +  3General + Admin Expenses                   
3300   3200   E.   +  4Salaries-Corporate                         
3310   3300   E7301+  5Salaries-Corp Mgmt                   505.00
3320   3300   E7302+  5Salaries-Administration              505.00
3330   3300   E7303+  5IT Contractors                       505.00
3400   3200   E.   +  4Company Benefits                           
3410   3400   E7311+  5Social Security                      505.00
3420   3400   E7312+  5Unemployment                         505.00
3430   3400   E7313+  5Vacation Pay                         505.00
3440   3400   E7314+  5Sick Pay                             505.00
3450   3400   E.   +  5Insurances
3451   3450   E7321+  6Medical Insurance                    606.00
3452   3450   E7322+  6Dental Insurance                     606.00
3453   3450   E7323+  6Pharmacy Insurance                   606.00
3454   3450   E7324+  6Disability Insurance                 606.00
3455   3450   E7325+  6Life Insurance                       606.00
3500   3200   E.   +  4Depreciation Expenses                      
3510   3500   E7411+  5Equipment                            505.00
3520   3500   E7412+  5Building                             505.00
3530   3500   E7413+  5Vehicles                             505.00
3600   3200   R7414-  4Gain/(Loss) Sale of Equipment        404.00
3700   3200   E.   +  4Leasehold Expenses                         
3710   3700   E7421+  5Equipment                            505.00
3720   3700   E7422+  5Buildings                            505.00
3730   3700   R7429-  5Sub-Lease Income                     505.00
3800   3200   E7440+  4Interest Expenses                    404.00
3900   3200   E.   +  4Utilities                                  
3910   3900   E7451+  5Electric                             505.00
3920   3900   E7452+  5Gas                                  505.00
3930   3900   E7453+  5Telephone                            505.00
3940   3900   E7454+  5Water                                505.00
3950   3900   E7455+  5Internet Access                      505.00
5000   1000   E.   -  2Total R+D Costs
5100   5000   E7511+  3Salaries                             303.00
5200   5000   E7521+  3Misc. Equipment                      303.00
END
Top of page
x
Syntax: How to Report on a Hierarchy

In hierarchical reporting, measure values for child dimension members will be rolled up to generate the parent values. In the data source, the parent members should not have values for the measures.

SUM measure_field ...
BY hierarchy_field [HIERARCHY [WHEN expression_using_hierarchy_fields;]
[SHOW [TOP|UP n] [TO {BOTTOM|DOWN m}] [byoption [WHEN condition] ...] ]
[WHERE expression_using_dimension_data]
[ON hierarchy_field HIERARCHY [WHEN expression_using_hierarchy_fields;]
[SHOW [TOP|UP n] [TO BOTTOM|DOWN m] [byoption [WHEN condition] ...]]

where:

measure_field

Is the field name of a measure.

BY hierarchy_field HIERARCHY

Identifies the hierarchy used for sorting. The field must be a hierarchy field.

ON hierarchy_field HIERARCHY

Identifies the hierarchy used for sorting. The field must be a hierarchy field. The request must include either a BY phrase or a BY HIERARCHY phrase for this field name.

WHEN expression_using_hierarchy_fields;

Selects hierarchy members. The WHEN phrase must immediately follow the word HIERARCHY to distinguish it from a WHEN phrase associated with a BY option (such as SUBFOOT). Any expression using only hierarchy fields is supported. The WHEN phrase can be on the BY HIERARCHY command or the ON HIERARCHY command, but not both.

SHOW

Specifies which levels to show on the report output relative to the levels selected by the WHEN phrase. If there is no WHEN phrase, the SHOW option is applied to the root node of the hierarchy. The SHOW option can be specified on the BY HIERARCHY phrase or the ON HIERARCHY phrase, but not both.

n

Is the number of ascendants above the set of selected members that will have measure values. All ascendants appear on the report to show the hierarchical context of the selected members. However, ascendants that are not included in the SHOW phrase appear on the report with missing data symbols in the report columns that display measures. The default for n is 0.

TOP

Specifies that ascendant levels to the root node of the hierarchy will be populated with measure values.

TO

Is required when specifying a SHOW option for descendant levels.

BOTTOM

Specifies all descendants to the leaf nodes of the hierarchy will be populated with measure values. This is the default value.

m

Is the number of descendants of each selected level that will display. The default for m is BOTTOM, which displays all descendants.

byoption

Is one of the following sort-based options: PAGE-BREAK, REPAGE, RECAP, RECOMPUTE, SKIP-LINE, SUBFOOT, SUBHEAD, SUBTOTAL, SUB-TOTAL, SUMMARIZE, UNDER-LINE. If you specify SUBHEAD or SUBFOOT, you must place the WHEN phrase on the line following the heading or footing text.

condition

Is a logical expression.

expression_using_dimension_data

Screens the rows selected in the BY/ON HIERARCHY and WHEN phrases based on dimension data. The expression can use dimension properties and hierarchy fields. However, the selection criteria are always applied to the values at the leaf nodes. Therefore, you cannot use WHERE to select rows based on hierarchy field values that occur at higher levels. For example, in a dimension with Continents, Countries, and Cities, your request will not display any rows if you use WHERE to select a Country name, but it may if you use it to select a City name.
Example: Reporting on a Dimension Hierarchy

The following request reports on the entire GL_ACCOUNT hierarchy for the CENTGL2 data source created in How to Specify a Hierarchy in a Master File.

TABLE FILE NEWGL
SUM GL_ACCOUNT_AMOUNT  
BY GL_ACCOUNT HIERARCHY   
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
ENDSTYLE
END

Partial output is shown in the following image. The accounts are indented to show the hierarchical relationships:

The following is the same request using the GL_ACCOUNT_CAPTION field:

TABLE FILE NEWGL
SUM GL_ACCOUNT_AMOUNT 
BY GL_ACCOUNT_CAPTION HIERARCHY   
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
ENDSTYLE
END

Partial output is shown in the following image:
Example: Using WHEN to Select Hierarchy Members

The following request selects certain accounts using the WHEN phrase and populates one level up and one level down from the selected nodes with values. Note that all levels to the root node display on the output for context, but if they are not in the members selected, they are not populated with measure values:

TABLE FILE NEWGL
SUM GL_ACCOUNT_AMOUNT
BY GL_ACCOUNT_CAPTION HIERARCHY
WHEN GL_ACCOUNT GT '2000' AND GL_ACCOUNT LT '3000';
 SHOW UP 1 TO DOWN 1   
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image:
Example: Using WHERE to Screen Selected Hierarchy Members

The following request selects members using the WHEN phrase and then screens the output by applying a WHERE phrase to the selected members:

TABLE FILE NEWGL
SUM GL_ACCOUNT_AMOUNT GL_ACCOUNT_TYPE
BY GL_ACCOUNT HIERARCHY
WHEN GL_ACCOUNT NE '3000';
 SHOW UP 0 TO DOWN 0
WHERE GL_ACCOUNT_TYPE NE 'E'   ;
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image:

WebFOCUS