Working with Hierarchies

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.

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.

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 Hierarchy → Expand to Level. The selected node is expanded to the level you select. To collapse the subnodes of a selected node, choose Hierarchy → 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.

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

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.

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.

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.

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

If you wanted to display the dimensions hierarchically in the crosstab, you choose Hierarchy → Compact 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.

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.

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