Double-clicking on a Grid's hierarchy label allows you to select the members using the Hierarchy Editor:
The left of the dialog shows the hierarchy members. The tree can display either the members directly, or the members grouped by hierarchy level. The Show Levels toolbar button () can be used to switch between the two modes:
The tree can be expanded to its most detailed level where you can select values such as a specific name of a customer.
To select an item from the tree, either:
- Double click on the item
- Drag and drop the item into the right-hand selection area
All members currently selected and that will show in the report appear on the right hand side.
To create dynamic reports, you can also select members based on an Excel range or XLCubed slicer within the workbook. See the below articles for more details:
To remove an item from the selection area, click on the delete icon at the end of the field. You can also reorder the selected items by dragging the hand icon.
Next to each selected member is a drop down icon.
Children: All direct children of the selected member will be inserted in to the report.
Descendants: All descendants will be inserted.
Lowest Descendants: Insert the lowest level descendants of the selected member.
Descendants at: Choose to display the descendants of the selected member at a particular level. For example, the descendants of 'Bikes' at Product level.
Next/Previous: This will select the current member and the next/previous members in the same level. This is particularly useful for date selections, such as the current month and previous 11 members. This value can also be driven from an excel range.
Clear Selected Members: Clear all currently selected members.
Load Member List: Load a saved member list.
Save Member List: Save the current member selections. This file can then be distributed and used in other grids/reports.
Show Levels: Toggle between the member tree view and the level view.
Find Member: Find the first member matching the search text (see Member Search).
Find Next/Previous Member: Navigate through the search results.
Advanced Search: See Set Searching.
Toggle Single Member Mode: Implements 'select and replace' on hierarchies in the filter area (selecting a member replaces any existing selected member).
Use Account Style Drilling: Drilling a member opens it's children above the parent.
Show Hierarchy Information: Opens the Hierarchy Information dialog.
If you have a large hierarchy you can use the Search toolbar field to allow you to search all levels of the hierarchy:
The search will look down each branch of the tree and stop on the first member containing the search text. You can then navigate on to the next matching member using the navigation buttons.
If many members are return by the search text, it may be better to use the advanced search option which will display all matching members.
Having built a members list you can save it using the Save button. This can then be loaded into other reports.
Group Members in Member Selector
Members can be grouped in the Member Selector by right clicking on the member list (see Grid Calculations), or based on slicer choices or an Excel range. For example, below is a slicer with France, Germany and the United Kingdom selected:
To display this selection as one row on the grid, go into the Hierarchy Editor and select More and then Group Members from the dropdown:
The grid now displays one row for the three slicer choices. Give the slicer a title and it will display that instead of the XL3WithMember.
If you select a measure you get additional options in the member drop down which allow you to add
Copy Existing Member
New in v8.1 is the ability to copy existing current member selections in Member Selector.
Use the CTRL key & drag icon in red border below:
There are now two entries for United States:
This can be particularly useful in certain scenarios. For example, if you are creating a series of in cell charts that are highly-formatted you can format the first chart, copy the existing member selection and then update it to point to another member. This would mean all charts have identical formatting.
Below is an in-cell chart for the United States. To create the same chart for another member, use CTRL and drag to create another entry:
Click the highlighted icon to change the member:
You can then pick another member by clicking the drop-down – alternatively you can point to an Excel range.
Select the desired member – click OK and we can see we now have an entry for an in-cell chart for France as well as United States.
You can make more advanced member selections from the advanced tab of the hierarchy editor. See Advanced Row and Column selections.