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 can allow you to select another hierarchy/level.

Depending on analysis and data exploration requirements multiple hierarchies can be created in the same cube. Below is an example from a FRTB cube:

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

• "Legal Entities" has only one level - "Legal Entity" - it is 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 fine since hierarchies are designed for analysis purpose: one can rearrange levels and duplicate them across multiple hierarchies in different order or categories as your business requires.

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:

• 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 asking you again.
• Click on "Forget expansion order" in order to reset the order in which levels should be expanded in this Pivot Table.

Using the State

This section contains instructions for advanced users to configure how the expand/collapse behaves using the State.

Pivot Tables by default expand by 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 below configuration for pivot table:

"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:

"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 drilldown further by a pre-defined hierarchy/level:

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, you may want 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
  }
}