AddCalculatedMembers in MDX

Friends,

AddCalculatedMembers function helps you in retrieving all the calculated members data in the result set. We have to pass a Set_Expression as parameter to this function and this returns  a set generated by adding ALL the calculated measures to the set specified in Function. PFB the syntax of the same –

AddCalculatedMembers(Set_Expression)

Lets see a simple query which returns complete measures data in the result set.

select {Measures.members} on Columns,
[Dim Time].[Calendar Year].&[2004] on Rows
from [Adventure Works]

In the above query we specified {Measures.members} on Columns and hence it returns all the measures EXCEPT Calculated Measures. To confirm the same please check the below screenshot in which it is clear that all the measures are pulled as part of result set EXCEPT Calculated Measures.

Now lets use the same query by adding AddCalculatedMembers Function to it. PFB the updated query –

select AddCalculatedMembers(Measures.members) on 0,
[Dim Time].[Calendar Year].&[2004] on 1
from [Adventure Works]

Now the calculated member which was not part of last output will also be retrieved as shown below.

By default, MDX excludes calculated members when it resolves set functions. The AddCalculatedMembers function examines the set expression specified in Set_Expression,and includes calculated members that are siblings of the members contained within the scope of that set expression.

Note that it is not mandatory that you have to use retreive all MEASURES in order to pull calculates members. In simple itis not mandatory to use Measures.Members as parameter of the function. You can simply use any valid SET expression and in the below query I am demonstrating the same with ONE Measure.

select AddCalculatedMembers([Measures].[Sales Amount]) on 0,
[Dim Time].[Calendar Year].&[2004] on 1
from [Adventure Works]

The above query pulls ALL the calculated members along with “Sales Amount” measure as shown below.

Tip – There is a way to PULL ONLY  calculated members excluding all the measures and PFB the query sample for the same.

select AddCalculatedMembers(Measures.Members) – Measures.Members on 0,
[Dim Time].[Calendar Year].&[2004] on 1
from [Adventure Works]

In the above query in the first set we are pulling all the measures and calculated members and we are excluding the measures from the resultant set. The below query also helps you in acheiving the same but for the below query You need to know atleast one measure name in the cube.

select AddCalculatedMembers([Measures].[Sales Amount]) – [Measures].[Sales Amount] on 0,
[Dim Time].[Calendar Year].&[2004] on 1
from [Adventure Works]

I am pulling all the calculated members + Sales Amount and then removing Sales Amount measure from the result set.

Alert – Instead of this function you can use another function to retrieve the same result as AddCalculatedMembers function i.e AllMembers. PFB the query for the same.

select Measures.AllMembers on 0,
[Dim Time].[Calendar Year].&[2004] on 1
from [Adventure Works]

That’s it and hope you understood the concept and Happy Coding !!

Regards,

Roopesh Babu V