XLCUBED - Use & Set and Calculated member

 XLCUBED - Use & Set and Calculated member

 

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:

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 Calculated Members

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”

Create a new calculated member with the name Advertising

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

At the end you will have 3 calculated members :

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

 

  • 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”

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”.

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

CREATE and configure the slicer

CREATE and configure the slicer

On Behavior check Update range with selection and chose caption

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 :

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 will create on new Excel Cell an Excel Formula to create the name of the set by concatenation

Result :

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

 

  • 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 :

CREATE XCUBED GRIDS and configure parameters

Click on Raisons de Ventes and click on Advanced section

Click on Raisons de Ventes and click on Advanced section

Click on member set

Click on Raisons de Ventes and click on Advanced section

Select Manual MDX and click on the Excel cells :

Select Manual MDX and click on the Excel cells

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

Select the Excel cell where you display your member set

Select the Excel cell where you display your member set

Click OK

 

If you come back on member section

If you come back on member section

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

 

Conclusion 

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

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

 

Thanks for reading this, if you have any questions we would be happy to discuss further with you.

CONTACT US

 

 

Share: