Difference between revisions of "Excel Member Set"

Line 6: Line 6:
  
 
You must define the hierarchies as well as the members, and this allows formulae to update the grids layout if required.
 
You must define the hierarchies as well as the members, and this allows formulae to update the grids layout if required.
 
'''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.
 
  
 
==Example==
 
==Example==
Line 17: Line 15:
 
To do this, we follow these simple steps:
 
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"
 
*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.
 
[[Image:Excel Member Set 2.png|500px|centre]]
 
[[Image:Excel Member Set 2.png|500px|centre]]
 
<br>
 
<br>

Revision as of 13:59, 28 June 2022

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.

Excel Member Set 1.png

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.

Excel Member Set 2.png


  • Name your set. Select the hierarchies and members you want.
Excel Member Set 3.png


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

Excel Member Set 4.png


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.