Search code examples
scopessasmdxolapssas-2008

How to correctly replace value of ( [Some Dimension].[All], [Some Measure] )? (MDX, SCOPE())

I'm using SQL Server Analysis Servises 2008 R2.

I need1 to replace the value of [Some Dimension].[All] when calculated against [Some Measure]. Here's what I have tried:

SCOPE( [Some Dimension].[All] );
  [Measures].[Some Measure] = 123; // for simplicity sake
END SCOPE;

This works as expected when I query against [Some Dimension] – the [All] member returns 123, and other members are untouched, showing the actual associated [Some Measure] values.

But for some strange reason, all of the members in all other dimensions now return 123 for [Some Measure]. I am really lost. Please advice me how to fix this, and why this is happening.

1 For the ones who are curious: the reason I want to replace the value of [All] is because [Some Dimension] and [Some Measure] are related as many-to-many. Because of this, tuple ( [Some Dimension].[All], [Some Measure] ) always returns the total count for [Some Measure] in the cube. What I want it to return is the count of [Some Measure] that are actually associated with at least one member of [Some Dimension].

Solution

  • If I understand correctly, you only want to overwrite [Measures].[Some Measure] when [Some Dimension].[All] is selected, but also when the remaining dimensions' currentmember in your cube is the corresponding "All" member. It that's so, then just changing your scope assignment to SCOPE(Root()) should do the trick.