Expand and Collapse Form Rows and Columns

Author: Ian Britz

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 a Hierarchy, to facilitate data navigation. This 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 the expand/collapse feature, you first need to have a Dimension organized into either a Parent-Child Hierarchy or Leveled Hierarchy. The Dimension's order and the expandable/collapsible sections in the Form are driven by the Hierarchy definition.

 

 

 

Enable Expand and Collapse

1. In the Form Editor, go to the Layout tab and configure the rows, columns, and Filters of your Form. 

 

 

Our Form looks like this: 

 

 

2. Go to Properties and click Add to create a display property. 

 

 

3. Select the relevant axis and click Add. 

 

 

4. Select the Indent Hierarchy.

 

 

This will display the hierarchy structure on the Form.

 

 

5. In the General tab, select Settings .

 

 

All the expand and collapse options are located here.

 

 

6. Select Enable expand and collapse for now. 

 

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, that doesn't have to be the default state. The initial configuration of the Form is driven by the definition of the rows. 

 

As the current definition is D([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 would specify a single parent Member. 

 

 

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

 

 

Through this method, we can change the row definitions to customize the Form's default look.

 

 

 

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 user experience tedious. To mitigate this problem, the Form Editor has the Preserve the user expanded and collapsed rows and columns options 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 does not work with other features that 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:

 

Option	Function
Collapse	Collapse the selected Member and all hierarchy levels below it.
Expand to Children	Expand the selected Member to the next hierarchy level.
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 hierarchy level, everywhere it appears.
Expand All <Member Name> to Leaves	Expand the selected Member and all hierarchy levels below it, wherever it appears.
Collapse All <Member Name>	Collapse the selected Member and all hierarchy levels below it, wherever it appears.
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, select the option Expand All 'Total Cost of Goods Sold' to Children.

 

 

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