MDX Calculated member Sum of selected members rollup

1
votes

I'm trying to write a calculated member that will roll up along a dimension based on the selected values provided on the axes.

Product -> subcategory -> category lexus -> sedan -> car lexus-coupe -> coupe -> car bmw -> sedan -> car bmw-coupe -> couple -> car ford -> pickup -> truck chev -> pickup -> truck ford-suv -> suv -> truck lincoln-nav -> suv -> truck

[Calculated measure] = [measures].[a]+[measures].[b]

suppose the user wants to see [Calculated measure] at the car level for just lexus-coupe and bmw. How do you create a calculated measure that will roll up at the car level based on what has been selected in the dimension?

Thank you in advance!

1
ssasmdxolap
What do you mean by "has been selected" ? Do you have an example of your MDX request ? By the way, you can have a look to a documentation about calculated member here – Marc Polizzi
Sorry for the delayed response - been out of town. So the syntax of the MDX request... would be something like SELECT [Measures].[MyMeasure] on columns, filter(descendants(product.sedan, product.category, self_and_before), <some filter criteria that removes some leaves>) on rows from mycube) .... where [measures].[MyMeasure] is defined as the tuple of (product.CurrentMember, OtherDimension.ParticularMember, measures.SomeOtherMeasure. Yes, i know it's silly, but the customer wants a measure field for SomeOtherMeasure/ParticularMember tuple that will pivot on other dimensions. – feemurk

1 Answers

1
votes

I have previously written MDX to check what dimensions are being used on rows (or columns) and change the way a measure is being calculated. It is a bit messy.

This example looks to see if axis 1 (rows?) is using any Measures, and can change the calculation accordingly: 

IIF(InStr(1, SetToStr(StrToSet("Axis(1)")), "[Measures].") = 0, blah, blah)

Can't remember why I did StrToSet and then SetToStr, but it worked!

You may be able to use VBA string functions like InStr() to examine what the user has selected on rows and columns, and fiddle with your calculated members accordingly...