In this section:
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.
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.
Alternatively, if you have the Expression Builder open you can access the SHOW Builder by clicking Show Builder.
The Show Builder dialog box opens.
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.
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.
Shows warnings and miscellaneous information about the Show Builder range that you are entering.
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.
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.