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

Advanced Row and Column selections

Revision as of 13:40, 22 April 2021 by Support (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

The advanced tab of the Hierarchy Editor offers more ways to make member selections.


Named Sets

A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. Named sets can be created at cube level or within an application such as XLCubed. They can be created combining cube data, arithmetic operators, numbers and functions.

For more information on creating Named Sets see Creating Named Sets to continue.

From v6.5 two new options, Between and Manual MDX , are available on Advanced tab > Member Set (MemberSetIcon.png) when editing hierarchies.


This option allows the user to report for a particular range on that hierarchy.

The only criteria is that both members have to be at the same level.

For example, we can choose to report all data between 2002 and 2004 as below:


There is also an option to enter an Excel range as below – we will run the grid report based on values in $I$3 and $I$4. The start range value is in $I$3 and the last range value is $I$4.


Another feature of using the range option is that you can choose to leave one of the ranges blank.

Value in $I$3 Value in $I$4 What will be reported
CY2002 CY2004 Grid report will report data starting CY 2002 through to CY 2004
CY2004 Grid report will report data starting from earliest date through to CY 2004
CY2003 Grid report will report data from CY 2003 through to latest date

Manual MDX

This option allows you to use MDX code – click on the ellipse to the right of the Manual MDX radio button and you will be able to enter MDX code in the following window:


For example if you only want to report CY 2002 and CY 2004 you would enter {[CY 2002], {CY 2004]} in the Manual MDX window. You can also specify an Excel cell range as the MDX source. Once you have defined your named set you can then select to report Descendants, Lowest Descendants or Descendants At.

Additional Member Lists

You can add an additional member list, make any selections as usual, and then choose how to combine the two lists:

  • Add: left and right sides combined
  • Common: must exist on left and right side
  • Subtract: left side minus right side

For examples, see:


Hide Levels

This option allows you to select which levels are shown when drilling the hierarchy. Deselect a level to skip that level when drilling.