Excel Member Set

Creating a member set in Excel allows you to define the members returned on an Axis based on cell contents.

This can offer more flexibility that defining the members for a hierarchy in Excel, as it allows you to chose which combinations of members are displayed instead of the grid doing a crossjoin which is the standard behaviour.

You can leave a cell blank to get the default member of a hierarchy.

You must define the hierarchies as well as the members, and this allows formulae to update the grids layout if required.

Example

Here we want to display the Actuals for 2020 and display the budget for 2021.
This will give us the relevant numbers for the past year and avoid showing the empty columns for the year to come.
We have set up an Excel range that contains the memebers we want returned. This will serve for later selections.

To do this, we follow these simple steps:

• Go to the XLCubed Grid Ribbon option. Find the "Define Member Set in Excel" option in "Keep"

Note The position of the selected Excel cell controls the axis that is being defined. If the cursor is not in the Column or Rows members area then columns will be chosen as the default.

• Name your set. Select the hierarchies and members you want.

The grid is now setup to display the members you define in Excel.

You can change the members to display by over typing the excel cells. Formula are supported so you can setup calculations to automatically adjust the members displayed.