XLCUBED - Use Calculated Set and Calculated member to Create groups with existing Dimension members.



Use Calculated Set and Calculated member to Create groups with existing Dimension members. 

Often during your XLCUBED experience you can be affected by a inappropriate dimension hierarchy organization. Each level doesn’t contain the good members. The biggest frustration is how to provide the solution when you are not BI developer.

If you want create a custom group without to use the actual hierarchy you can follow this study case:

During this tutorial we will create 3 news groups and choose each dimension member we need to reorganize. We will create a slicer associated to these new group and display the aggregation result of each group on a XLCUBED Grid.

We used Adventure Works Cube to create our study case:


Our requirement:

We want “Television Advertisement” and “Manufacturer” on a new group call “Advertising

We want “Review” and “Quality” on a new group call “QA

We want “Price” on a new group call “Business Development


  • Create Calculated Members


Click on Custom Calculation


Create a new calculated member with the name Advertising

- Choose the Parent Hierarchy “Raison des Ventes

- Choose The parent member: [Sales Reason].[Sales Reasons].[All Sales Reasons]

Type on the expression input:Advertising”


Repeat the same for the next new value “QA”, “Business Development”

At the end you will have 3 calculated members :



  • Create calculated Set


Create A new Calculated Set with the name :” SET_Advertising” and choose each member to drag and drop in the expression of ”SET_Advertising”


We can repeat the process for “SET_QA”, “SET_Business Development”.



  • CREATE and configure the slicer

Now edit the slicer

Drag and drop the dimension Raison de Ventes to the slicer Hierarchy



On Behavior check Update range with selection and chose caption


You can see appears at the right of the slicer the SET associated to the group member selected :


Now we will create on new Excel Cell an Excel Formula to create the name of the set by concatenation.



Result :



  • CREATE XCUBED GRIDS and configure parameters

Now we are ready to display each member associated to each new group.

Open the XLCUBED Grids

Put the measures on columns and Raisons de ventes on rows :


Click on Raisons de Ventes and click on Advanced section


Click on member set


Select Manual MDX and click on the Excel cells :


Select the Excel cell where you display your member set: [SET_Advertising] and OK



Click OK


If you come back on member section


We can see the [SET_Advertising] is accepted as traditional member.



Now Your slicer drive the XLCUBED Grids with only the groups we need to in this turorial. The aggregation can be automaticly apply