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:
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”
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 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”.
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.
=CONCATENER(« [SET_ »;G3; »] »)
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
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