Working with Hierarchies


After completing this lesson, you will be able to:

  • Work with predefined hierarchies
  • Create hierarchical displays in Analysis for Excel

Hierarchical Display

Dimensional hierarchies are created or imported in the SAP source systems for Analysis for Office. A dimension can contain multiple hierarchies. In a hierarchy, dimension members are organized in a tree structure, similar to a corporate organizational chart or a family tree.

Hierarchical display of dimension members

If a dimension has hierarchies associated with it, you will see Hierarchies when you expand the dimension in the Data Source area of the Design Panel's Analysis tab. Expand Hierarchies and drag the appropriate hierarchy name to wherever the dimension is in the crosstab (Rows/Columns.) The Flat Presentation removes any hierarchical display of the dimension members.

Displaying hierarchies in the Analysis tab of the Design Panel

You can expand or collapse a single node of a hierarchy. Use the [+]/[-] symbols to expand and collapse a single node. Or you can select a node cell in the crosstab that you want to expand and from the Analysis ribbon choose HierarchyExpand to Level. The selected node is expanded to the level you select. To collapse the subnodes of a selected node, choose HierarchyCollapse.

Options in the Hierarchy selection in the Analysis ribbon
You can also right-click on a node and use the context menu to expand and collapse.

The Show Levels . . . option allows you to bypass certain levels of the hierarchy that are displayed in the crosstab. Only the selected levels will appear. The total is not affected by this selection because the bypassed levels are still part of the total.

Typically, hierarchies expand from the top down or from left to right, and the totals for a level appear after the details that make up the total. You can change this behavior using the Expand Upwards (Rows)/Expand to Left (Columns) option. This action will position the totals before the details that make up the totals.

Break Hierarchies

When you are displaying a hierarchy and sort measure values, the values are sorted within their levels by default.

Data sorted within each level of a hierarchy

To sort members independently of their group, choose Sort in the Analysis ribbon and choose Break Hierarchies. The figure Break Hierarchies activated shows the crosstab with all levels and details combined, sorted, literally, by the measure value.

The Break Hierarchies options active in the Sort by dialog and the results in the crosstab sorted literally, not within hierarchy levels

Hierarchy and Node Filters

When using dimensional hierarchies, you can filter on hierarchy nodes as well as members. In the Filter By Member dialog, choose Hierarchy as the Display option and select the specific level or levels you want to filter.

Filter by Member dialog displaying hierarchy nodes rather than dimension members

A blank check box indicates the node is not selected; a checked box indicates the node is selected. A square in the check box indicates that not all children of the parent node are selected.

You can also filter using only a specific hierarchy. Right-click the hierarchy in the Rows area of the Analysis pane and choose Only Use Hierarchy Hierarchy Name for Filter.

Access the Only Use Hierarchy option from the context menu of the dimension in the Design Panel

Compact Display in Rows/Columns

You can display multiple dimensions hierarchically without using a dimensional hierarchy.

If you wanted to display the dimensions hierarchically in the crosstab, you choose HierarchyCompact Display in Rows from the Analysis ribbon.

This selection combines the two dimension columns into one, nesting the second column under the first. Such a hierarchical view is called a display hierarchy since the data is simply displayed as a hierarchy, but there is no underlying hierarchy defined in the SAP source system.

You can use display hierarchies and dimensional hierarchies in parallel to create a very robust hierarchical view of data for analysis.

Working with Hierarchies

As an analyst, you have been asked to display different hierarchy levels in the workbook and be able to choose different hierarchies using drag and drop. You have also been asked to use the compact display feature and filter members when a hierarchy is being used.

Group Functionality

You can create member groups to group dimension members into categories in your crosstab, in the same way that hierarchies group members into nodes. Select the members you want in a particular group, right-click, choose Group Members from the context menu, and name the group. This process creates a custom hierarchy. You can create multiple levels of the custom hierarchy. All hierarchy features are available, but the hierarchy exists only in the workbook. The figure Group members shows Vehicles and Pumps grouped into Machines and Computers and Monitors grouped into Technical Products.

Dimension members grouped in the crosstab
Only visible members are part of the hierarchies.

Ungroup members removes the node (group) but not the members. Ungroup All deletes the custom hierarchy.

Grouping Members

You want to create your own hierarchical display by grouping specific members.

Log in to track your progress & complete quizzes