XLCUBED - Use & Set and Calculated member.

 XLcubed-main.png

 

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:

1a

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

2a

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”

3a

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

At the end you will have 3 calculated members :

4a

 

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

5a

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

6a

 

  • CREATE and configure the slicer

Now edit the slicer

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

7a

8a

On Behavior check Update range with selection and chose caption

9a

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

10a

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

=CONCATENER("[SET_";G3;"]")

11a

Result :

12a

 

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

13a

Click on Raisons de Ventes and click on Advanced section

14a

Click on member set

15a

Select Manual MDX and click on the Excel cells :

16a

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

17a

18a

Click OK

 

If you come back on member section

19a

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

20a

 

Share: