AddCalculatedMembers in MDX

By
roopesh.valluru
-
4501
29229
AddCalculatedMeasures Function
AddCalculatedMeasures Function

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.

AddCalculatedMembers Function
AddCalculatedMembers Function

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.

Add Calculated Members Function
Add Calculated Members Function

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.

Calculated Members Function
Calculated Members Function

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

RELATED ARTICLESMORE FROM AUTHOR

4501 COMMENTS

  5. Свадьба –один из самых счастливых моментов нашей жизни. Так хочется чтобы теплые воспоминание об этом дне остались на вечно. Поэтому [url=https://vk.com/weddingspbvideo]свадебная видеосъемка[/url] организовывая свою свадьбу молодожены должны задуматься о качественной видео и фотосъемке, https://vk.com/weddingspbvideo чтобы спустя много лет просматривая свой свадебный фильм и фото погрузится в атмосферу этого счастливого дня. На сегодняшний день [url=https://vk.com/phvideowedding]фото и видеосъемка свадеб[/url] – это одно из составляющих этого главного дня в создании молодой семьи.

  97. ed pills cheap [url=https://cheapdr.top/#]viagra without doctor prescription[/url] buy ed pills online

  272. Здравствуйте! Позвоните пожалуйста, интересует товар с вашего сайта.
    89686803080