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 the Describing Data With TIBCO WebFOCUS® Language manual.
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:
