Expand - Collapse

Automatically expand levels

With multi-level hierarchies, it is possible to expand and collapse rows in a Pivot Table level by level. See the difference between expand, drill down, drill in and drillthrough for disambiguation.

When there are no more levels to expand, the Pivot Table allows you to select another hierarchy/level.

Depending on analysis and data exploration requirements, multiple hierarchies can be created in the same cube. An example from a FRTB cube is shown below:

In this example, "Legal Entities" and "LegalEntityHierarchy" are hierarchies that exist inside the same dimension:

• "Legal Entities" has only one level - "Legal Entity" - a flat list of Legal Entity Names.
• "LegalEntityHierarchy" consists of five levels. "Measure" at the group level (level 1) may be decomposed further into sub-levels, subtotals and further down to level 5, which contains the same data as the "Legal Entity" level.

These two hierarchies overlap - both of them contain the list of "Legal Entities". This is not a problem because hierarchies are designed for analysis purposes. It is possible to rearrange levels and duplicate them across multiple hierarchies in different orders or categories, to match the requirements of your business.

Expand any levels in pre-defined order

In addition to automatically expanding multi-level hierarchies, it is possible to have a fine-grained control on the expansion order in a Pivot Table.

Using the Pivot Table context menu

From the Pivot Table context menu you can:

• Click on Remember expansion order so that future expansions in your pivot table directly use the order in which the levels are currently expanded, without needing to ask you again.
• Click on Forget expansion order to reset the order in which levels should be expanded in this Pivot Table.

Using the State

This section contains instructions enabling advanced users to configure the way expand/collapse behaves, using the State widget.

By default, Pivot Tables expand to include the next level of the hierarchy. However, you may want to disable this behavior, and let users choose the next expansion level themselves. This can be controlled using the state widget and defining the configuration for the pivot table as shown below:

"cellFactory": {
  "key": "treeCells",
  "args": {
    "automaticExpansion": false
  }
}

When automaticExpansion is explicitly set to false, levels "forget" their children and "ask" which hierarchy to expand.

In addition to changing the automaticExpansion argument, you can also pre-define what hierarchy/level a certain level will expand to (i.e. defining pairs of levels) through expansionPairs.

For example, if an analyst always starts with totals by currency, then usually checks breakdown by RiskFactor for some of the currencies, they may also check what Desks those RiskFactor exposures belong to.

The analyst can define the default expansion order explicitly by listing the pairs in State, as shown below:

"cellFactory": {
  "key": "treeCellSelection",
  "args": {
    "expansionPairs": {
      "[Currencies].[Currency].[Ccy]": "[Risk].[Risk Factors].[Risk Factor]",
      "[Risk].[Risk Factors].[Risk Factor]": "[Booking].[Desks].[Desk]"
    }
  }
}

With these properties set in State, currencies will be automatically expanded into Risk factors, and Risk factors into Desk.

Note that this is what the Remember/Forget expansion order action does behind the scenes: this state could have been obtained by adding levels Ccy, Risk Factor, and Desk on the Pivot Table rows (in that order), and then clicking on Remember expansion order in the context menu.

Compare these before and after behaviors.

Before

Currency is a one-level hierarchy, that's why it does not "know" how to expand further. Click "+" and select a hierarchy/level to expand:

After

Click each level to drill down further, using a pre-defined hierarchy/level expansion order:

Restrict expansions

As explained in the sections above, it is possible to control the expansion order by combining the automatic expansion mechanism and explicitly defining expansion pairs. When the user has expanded all the levels configured this way, it may be necessary to prevent them from performing any further expansion.

To achieve this, simply set the restrictedExpansion flag to true in the State:

"cellFactory": {
  "key": "treeCells",
  "args": {
    "restrictedExpansion": true
  }
}