Start a new topic

EPM Add-In: Expanding Some Or All Children and Descendants

The Expand command on a member selected in a report, drills down to the members below (depending on the expansion relationship behavior chosen) and all occurrences of the same member. You can perform an expand on: 

- the children of the selected parent and the parent itself 

- the children of the selected parent 

- the descendants of the selected parent and the parent itself 

- the descendants of the selected parent

You can also define a custom expand. For example, you can drill down to all members at level 4, or display only members at level 2.

To specify the expansion relationship behavior you want to apply by default, select an option in the Expand Options area in  EPM > Options > Sheet options > General . The option selected is applied by default:When you click the Expand button in the EPM tab. However, using the Expand button list, you can select other behaviors, the same as in the sheet options that are listed above.


Note


When you select Custom Expand, a dialog box opens and you can define the levels you want to display and if you want to include the selected member or not. So that the selection is taken into account, you must select the Apply Custom Expand option. Also, if you select the Enable Double-Click option in the User Options, the fastest way to perform an expand is to double-click on the member you want to expand. This allows you to quickly drill down to find what you are looking for.

The expand action automatically refreshes the report after it has expanded: the types of refresh applied are the worksheet refresh and the workbook refresh.

 

In this example, to expand on the USA member in the row axis, you can select the USA cell and then click Expand, or you can double-click on the USA cell shown here:



This will expand USA to display all of its children like this:



Note that the children are shown indented above their "total" parent. Indentation is an option. Totals are put at the bottom but you can change them into totals at the top.

NoteExpand does not work on a page axis dimension member since it has no place to expand to.

Note

When the data sorting, ranking and filtering are activated, the expand works in the column axis only if the following options are selected in the Expand Options area in the Sheet options dialog box:

Member and Children
Member and Descendants

The Expand does not work in the column axis when the other options are selected.

When the data sorting, ranking and filtering are activated, the Expand never works in the row axis.

When the data sorting, ranking and filtering are activated, the way the Expand works also applies to the Collapse feature.

In Large Reports: Refreshing only Expanded or Inserted Members

If you work with large reports and want to accelerate the refresh time, you can specify that only the new inserted rows or columns are refreshed after a member insertion. The other row and column data is not refreshed. To do so, select the user option Refresh only Expanded and Inserted Members. This behavior also applies to the expanded members.

Caution

Review the following restrictions before using this option:

- Do not use this option if the axis on which the expansion/insertion is performed, contains local members.
- Do not use this option if the axis on which the expansion/insertion is performed, contains EPMInsertCellsBeforeAfterBlock functions.This option can cause some formatting settings to not be properly applied.


Note

This option enhances performances but note that not all displayed data is refreshed in the report after a member insertion.
ExampleThe Refresh only Expanded and Inserted Members option is selected. In a report, the following members are displayed for the Time dimension: 2010, 2012. You insert the 2011 member (right-click in a cell that belongs to the axis that contains the Time dimension and select  EPM  Insert Members ). The following members are now displayed: 2010, 2011, 2012. The data is refreshed only for the members you have just inserted: 2011.


Expand Options

 

This option controls what scope of member expansion occurs when you perform an expand on a member.

The options are as follows:

Member and Children. This is the default option.

Children 

Member and Descendants

Descendants

Custom Expand. When you select Custom Expand, a dialog box opens and you can define the levels you want to display and if you want to include the selected member or not. For example: only level 4, the selected member and down to level 3, etc. The Custom Expand selection parameters are considered as user options and not as sheet options.

The option you select for the sheet applies by default when you perform an expand on a member in a report. However, using the Expand list in the EPM tab of the ribbon, you can apply another option.

Login or Signup to post a comment