XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Hierarchy Editor"

(→‎Group Members in Member Selector)
(→‎Selecting Members)
 
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
==Hierarchy Editor==
+
==Selecting Members==
 
Double-clicking on a Grid's hierarchy label allows you to select the members using the Hierarchy Editor:
 
Double-clicking on a Grid's hierarchy label allows you to select the members using the Hierarchy Editor:
 
[[File:HierarchyEditorDialog.png|300px|center]]
 
[[File:HierarchyEditorDialog.png|300px|center]]
  
−
The dialog shows the hierarchy and its members. The tree can display either the members directly, or the members grouped by hierarchy level. The {{Menu|Show Levels}} toolbar button ([[File:MembersLevelsButton.png]]) can be used to switch between the two modes:
+
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 {{Menu|Show Levels}} toolbar button ([[File:MembersLevelsButton.png]]) can be used to switch between the two modes:
 
{| align="center"
 
{| align="center"
 
| [[File:HierarchyEditor(Members).png|thumb|Members]]
 
| [[File:HierarchyEditor(Members).png|thumb|Members]]
Line 14: Line 14:
 
* Double click on the item
 
* Double click on the item
 
* Drag and drop the item into the right-hand selection area
 
* 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:
 +
*[[Driving Grids from an Excel Range]]
 +
*[[Dimension Slicers]]
 +
*[[Mdx:]]
 +
 +
===Removing selections===
  
 
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.
 
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.
  
 +
You can clear all selections by using the {{Menu|Clear Selected Members}} toolbar button ([[File:ClearAllIcon.PNG]]).
 +
 +
==Children and Other Member Navigation==
 +
 +
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.
 +
 +
[[File:MemberDecendantsAt.PNG|centre]]
 +
 +
==Toolbar==
 +
 +
[[File:HierarchyEditorToolbar.PNG]]
 +
 +
'''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 [[Hierarchy Editor#Member Search|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 its children above the parent.
 +
 +
'''Show Hierarchy Information''': Opens the [[Hierarchy Information]] dialog.
 +
 +
==Member Search==
 
If you have a large hierarchy you can use the Search toolbar field to allow you to search all levels of the hierarchy:
 
If you have a large hierarchy you can use the Search toolbar field to allow you to search all levels of the hierarchy:
 
[[File:HierarchyEditorSearch.png|300px|center]]
 
[[File:HierarchyEditorSearch.png|300px|center]]
 +
 +
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 [[Set Searching|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.
 
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#Member_Selector_and_Adhoc_Groupings|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:
 +
 +
[[File:gm1.png|300px|center]]
 +
 +
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:
 +
 +
[[File:gm2.png|450px|center]]
 +
 +
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.
 +
 +
{| align="center"
 +
|  [[File:gm4.png|400px]]
 +
|  [[File:gm3.png|400px]]
 +
|}
 +
 +
==Measure Calculations==
 +
 +
If you select a measure you get additional options in the member drop down which allow you to add
 +
* [[In-Cell_Charts_in_Grids|In-Cell charts]]
 +
* [[MDX_formula_wizard|MDX Calculations]]
 +
 +
[[File:measurememselectorcalc.png|600px|center]]
 +
  
 
==Copy Existing Member==
 
==Copy Existing Member==
Line 34: Line 116:
 
[[File:CopyMember2.png|300px|center]]
 
[[File:CopyMember2.png|300px|center]]
  
−
This can be particularly useful in certain scenarios.  For example, if you are creating 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 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.
  
−
As all the formatting would be in place you will not need to do it all again.
+
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:
−
 
 
−
So I have created and formatted an in-cell chart for the United States.  I would now like to do the same for another member. Using CTRL and drag I create another entry:
 
  
 
[[File:CopyMember3.png|300px|center]]
 
[[File:CopyMember3.png|300px|center]]
  
  
−
Click highlighted icon to change the member:
+
Click the highlighted icon to change the member:
  
 
[[File:CopyMember4.png|300px|center]]
 
[[File:CopyMember4.png|300px|center]]
Line 51: Line 131:
 
[[File:CopyMember5.png|300px|center]]
 
[[File:CopyMember5.png|300px|center]]
  
−
I am going to pick France – click OK and I now have an entry for an in-cell chart for France as well as United States.
+
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.
  
 
[[File:CopyMember6.png|300px|center]]
 
[[File:CopyMember6.png|300px|center]]
  
−
==Group Members in Member Selector==
+
==Advanced Tab==
−
 
 
−
New in v8.1 is the ability to group members in the Member Selector.  This grouping can be done based on slicer choices or an Excel range.  In the screenshot below we have used a slicer and selected France, Germany and the United Kingdom:
 
−
 
 
−
[[File:gm1.png|300px|center]]
 
−
 
 
−
We would like to group all three as one row so select More and then Group Members from the dropdown:
 
−
 
 
−
[[File:gm2.png|300px|center]]
 
−
 
 
−
 
 
−
The grid now displays one row for the three slicer choices:
 
−
 
 
−
 
 
−
[[File:gm3.png|300px|center]]
 
−
 
 
−
 
 
−
Give the slicer a title and it will display that instead of the XL3WithMember as highlighted above.
 
  
 +
You can make more advanced member selections from the advanced tab of the hierarchy editor. See [[Advanced Row and Column selections]].
  
−
[[File:gm4.png|300px|center]]
+
[[File:AdvancedTab.png|center]]
  
 
==See Also==
 
==See Also==

Latest revision as of 15:34, 25 February 2022

Selecting Members

Double-clicking on a Grid's hierarchy label allows you to select the members using the Hierarchy Editor:

HierarchyEditorDialog.png

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 (MembersLevelsButton.png) can be used to switch between the two modes:

Members
Levels

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:

Removing selections

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.

You can clear all selections by using the Clear Selected Members toolbar button (ClearAllIcon.PNG).

Children and Other Member Navigation

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.

MemberDecendantsAt.PNG

Toolbar

HierarchyEditorToolbar.PNG

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 its children above the parent.

Show Hierarchy Information: Opens the Hierarchy Information dialog.

Member Search

If you have a large hierarchy you can use the Search toolbar field to allow you to search all levels of the hierarchy:

HierarchyEditorSearch.png

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:

Gm1.png

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:

Gm2.png

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.

Gm4.png Gm3.png

Measure Calculations

If you select a measure you get additional options in the member drop down which allow you to add

Measurememselectorcalc.png


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:

CopyMember1.png

There are now two entries for United States:

CopyMember2.png

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:

CopyMember3.png


Click the highlighted icon to change the member:

CopyMember4.png

You can then pick another member by clicking the drop-down – alternatively you can point to an Excel range.

CopyMember5.png

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.

CopyMember6.png

Advanced Tab

You can make more advanced member selections from the advanced tab of the hierarchy editor. See Advanced Row and Column selections.

AdvancedTab.png

See Also

Retrieved from ‘https://help.xlcubed.com/index.php?title=Hierarchy_Editor&oldid=10710’