Expand and Collapse Form Rows and Columns

Author: Ian Britz

Introduction

When large sets of Dimension Members are used on the rows and columns of a Form, it's helpful to have the ability to expand and collapse sections of hierarchy, to make navigating the data easier. The article will explore four main topics:

• Enabling expand and collapse
• Setting a default expand and collapse configuration
• Preserving user expanded and collapsed rows and columns
• Expand and Collapse right-click settings

 

 

Before You Begin

To enable this feature, you first need to have a Dimension organized into either a Parent-Child Hierarchy or Leveled Hierarchy. The ordering of the Dimension, and the expandable and collapsible sections in the Form, are driven by the definition of the hierarchy in the Dimension.

 

 

 

Enable Expand and Collapse

Step 1. First, you need to have the target Dimension applied to either to the rows or columns of a Form.

 

 

Step 2. Then, you need to add a display property to the relevant axis, with the indent hierarchy option checked.

 

 

This will display the hierarchy structure on the Form.

 

 

Step 3. To open the Advanced Settings menu, select the gear icon in the ribbon.

 

 

All the expand and collapse options are located here. Let's select the first option, Enable expand and collapse.

 

 

Upon refreshing the Form, we can see that the hierarchy levels are now expandable and collapsible.

 

 

 

Set Default Expand and Collapse

In the previous example, notice that the Form always begins fully expanded. However, this isn't necessarily the default state. The initial configuration of the Form is driven by the definition of the rows in the ROW tab.

 

 

As the current definition is A([Income Statement]), the Form begins by displaying all members beneath Income Statement in the hierarchy. Then it allows you to expand and collapse the hierarchy from there.

 

 

If we wanted the reverse setup, where the Form begins fully collapsed, we could specify a single parent member, as shown below.

 

 

Then the hierarchy would begin fully collapsed, and we could expand the members from there.

 

 

Through this method, we can configure any set of members to customize the default look of the report.

 

 

In this example, the report now defaults to Total Operating Expenses collapsed and all other parent members expanded.

 

 

 

Preserve User Expand and Collapse Selection

Once the default configuration of the hierarchy is determined, the Form will always revert back to this configuration whenever it is refreshed. This means you have to find your place in the hierarchy again, every time the filter selection is changed.

 

 

In particularly complicated hierarchies, this can sometimes make the end-user experience tedious. To ease this problem, Kepion has the option to preserve the user expanded and collapsed rows and columns when changing filter selections.

 

 

When this option is selected, the set of visible members will not change when a different member is selected in a filter.

 

 

Note: As this feature overrides the normal settings that control which Dimension Members are visible on the rows and columns, it's not supported to use this with other features that also affect the visible Dimension Members (e.g., filtering the rows or columns with attribute hierarchies).

 

 

Right-Click Settings

The expand and collapse functionality also comes with a few right-click options, that make navigating complex hierarchies easier.

 

 

These options are useful for when you have a large hierarchy, or the same member is repeated multiple times on an axis.

• Collapse - Collapse the selected member and all hierarchy levels below it.
• Expand to Children - Expand the selected member to the next level of the hierarchy.
• Expand to Leaves - Expand the selected member and all hierarchy levels below it.
• Expand All 'Member Name' to Children - Expand the selected member to the next level of the hierarchy, everywhere it appears.
• Expand All 'Member Name' to Leaves - Expand the selected member and all hierarchy levels below it, everywhere they appear.
• Collapse All 'Member Name' - Collapse the selected member and all hierarchy levels below it, everywhere they appear.
• Reset - Reset the hierarchy to the default configuration.

 

To explain the Expand All options in more detail, take a look at the following report where Total Cost of Goods Sold is repeated in each Entity.

 

 

If you wanted to expand this member everywhere it appears on the Form, you could select the option Expand All 'Total Cost of Goods Sold' to Children.

 

 

Note: These right-click options are only available in the HTML5 version of Kepion.