Adding Hierarchies to Reports


After completing this lesson, you will be able to:

  • Create a query and include an external hierarchy

Hierarchy Options in Queries

The following options are available when adding hierarchies to reports:

  • Display characteristics and navigation attributes in rows or in columns as a hierarchy (internal hierarchy).

  • Configure a hierarchical display within structures.

  • Select an external hierarchy for a characteristic.

  • Use external hierarchies to make restrictions.

  • Combine different hierarchy options.

Rows or Columns Hierarchy (Internal Hierarchy)

To display characteristics and navigation attributes as hierarchies in rows or columns, go to the General tab of the query and then to the Universal Display Hierarchy screen area. Select Active on Rows or Active on Columns, and in the Expand to Level field, enter the characteristic up to which the hierarchical display should be expanded initially. There must be more than one characteristic or navigation attribute in the rows or columns.


You can also activate and deactivate the hierarchical display for characteristics and navigation attributes in rows or columns directly in Analysis for Office using Compact Display in Rows/Columns.

Hierarchical Structure Display

You can configure a hierarchical display in key figure structures and characteristic structures.

To configure a hierarchical display within a structure:

  1. In a structure, drag the structure members that you want to display as subitems directly over the structure member that you want to display as the parent node for the subitems.

  2. Select the parent node and go to the Properties pane on the right-hand side of the BW Query Designer.

  3. In the Properties pane, go to the Display screen area.

  4. Under Node Status, choose to display the node as Expanded or Collapsed in the initial view of the report.

  5. In the executed report, select the expand and collapse icons (+ and -) to expand or collapse the node.

External Hierarchies

External hierarchies represent master data and are used in reporting for alternative views of data. External hierarchies may be loaded from source systems by using predefined SAP BW/4HANA content data flows or the BW Hierarchy Framework. Alternatively, they may be created directly in SAP BW/4HANA.

External hierarchies comprise a series of nodes that are joined to one another in a tree structure. Usually, the leaves of a hierarchy are represented by characteristic values.

These hierarchies are called external because their data is not part of the InfoProvider on which your query is based, but is master data belonging to the characteristic, independent of whether it is loaded to or created in SAP BW/4HANA.

Creating External Hierarchies

  1. Create external hierarchies for basic characteristics, for example, Product (0D_NW_PRID). A basic characteristic does not refer to other characteristics.

  2. Determine whether a characteristic can have a hierarchy in the InfoObject maintenance.

  3. Define the properties of hierarchies, for example, hierarchy versions or time-dependent hierarchy structures.

  4. The external hierarchies are saved in special master data tables. External hierarchies behave like master data; you can use them in all InfoProviders that contain the hierarchy characteristic.

  5. Define several external hierarchies for a characteristic.


In most cases, external hierarchies are not created directly in SAP BW/4HANA, but rather loaded from source systems.

Adding External Hierarchies to a Report

  1. In SAP HANA Studio, select the characteristic that you want to display as a hierarchy.

  2. Go to the Properties section on the right side of SAP HANA Studio.

  3. In the Properties section, choose the Hierarchy tab.

  4. Under the Hierarchy tab, choose the Change button next to the Name area.

  5. Click Browse … and select a hierarchy from the dropdown list.

  6. Use the other options under the Hierarchy tab to adjust the presentation of the hierarchy.

External Hierarchy Properties

Each hierarchy has properties that were determined in the hierarchy definition.

You can change the following hierarchy properties in the query definition.

External Hierarchy Properties

Position of Child NodesPosition lower-level nodes above or below a higher-level node.
Nodes with Only One Child NodeShow or hide nodes with only one lower level node.
Values of Postable NodesShow or hide the values of postable nodes.
Expand to LevelDetermine the level to which the hierarchy is expanded initially in the report
SortingWithin the hierarchy, sort members in descending or ascending order by key or text. When using the default option Same as in Hierarchy, you cannot sort in descending or ascending order.

Characteristic Restrictions with External Hierarchies

Use hierarchies to select filter values for characteristics by selecting individual hierarchy nodes.

To restrict a characteristic to a hierarchy node:

  1. Open the context menu of the characteristic and choose Restrict.

  2. In the Edit Filter dialogue, select the Hierarchy Nodes tab, then select the desired hierarchy nodes or members.

  3. Choose OK to confirm.

Combination of Hierarchy Options

For the report display, you can combine all hierarchy options (rows and columns hierarchies, hierarchical structure display, and external hierarchies).

External Hierarchy

In the following demonstration, you learn how to use a hierarchy belonging to a characteristic to improve the clarity of a report.

Hierarchy Options

In the following demonstration, we use different options for hierarchical display.

Log in to track your progress & complete quizzes