Selecting Records in a Hierarchy

In this section:

How to:


You can select records for both parent/child hierarchies and level hierarchies. For both of these hierarchy models, you can use only Where statements to select data in the hierarchy.

If you are reporting with a parent/child hierarchy, you can display specific members of a hierarchy using a When condition. In addition, you can use a Show command to specify the data to show in the report output relative to the hierarchy members selected in the When condition. If there is no When condition, the Show command is applied to the root node of the hierarchy.

Top of page

Procedure: How to Select Data in a Parent/Child Hierarchy
  1. In Report Painter, right-click a Hierarchy sort field and select When from the context menu.

    The Expression Builder opens. The hierarchy you are working with is automatically selected and expanded. You can only create a When expression with fields in a hierarchy. Characteristics, properties, or measures cannot be used.

    Alternatively, if you have the SHOW Builder dialog box open you can access the Expression Builder by clicking When.

  2. Create an expression.


    • You must manually enter a value for hierarchy fields since the Expression Builder cannot retrieve values for parent/child hierarchies.
    • Use only the Value or Parameter Compare Types with hierarchies.
    • If you use the Parameter Compare Type, when you enter the compare value using the Variable Editor you cannot use the Variable Type Range since this does not apply to hierarchies.

Top of page

Procedure: How to Show a Range of Data in a Parent/Child Hierarchy
  1. In Report Painter, right-click a Hierarchy sort field and select Show from the context menu.

    Alternatively, if you have the Expression Builder open you can access the SHOW Builder by clicking Show Builder.

    The Show Builder dialog box opens.

  2. In the From-To Options field, select your From and/or To options. These specify the range of values in the hierarchy that you want to show in the report output. You can select an actual value or you can select Variable, which opens the Variable Editor.

    Note: If the Warning icon on the lower-right corner is activated, there is an error in the Show condition you are creating. Click the icon for details.

  3. Click OK.

Top of page

Reference: SHOW Builder Dialog Box

Show Builder

From-To Options

Enables you to select the range of values to display in the hierarchy. You can use From without To and To without From.


Select either TOP or a level number. TOP specifies that measure values for the ancestors of the selected node to the root node appear in the report.


Select either BOTTOM or a level number. BOTTOM specifies that measure values for the descendants of the selected node up to the leaf nodes of the hierarchy appear in the report.


This option appears when you have defined a variable for the From or To option. Click this button to edit the variable.


Opens the WHEN Expression Builder dialog box.

Warning icon Warning icon

Shows warnings and miscellaneous information about the Show Builder range that you are entering.

Top of page

Screening Data

Once hierarchy members are selected, you can screen the retrieved data by applying Where criteria to the selected members. Where criteria are applied to the leaf nodes and are processed after the phase of the request that selects hierarchy members (those members specified in the When phrase). Therefore, Characteristic Properties can be used in Where tests.

These tests can also reference hierarchy fields. However, since the selection criteria are always applied to the values at the leaf nodes, they cannot select data based on values that occur at higher levels. For example, in a characteristic with Continents, Countries, and Cities, your request will not display any rows if you use Where to select at the Country level, but it may if you use it to select at the City level.

Top of page

Screening Based on Aggregated Values

Since Measures are summarized values they should be referenced in Where Total tests and Computes, which are processed after the hierarchy selection and aggregation phases of the request.