SSAS MDX Script - Scope to all members of a hierarchy except the [All] member

3
votes

Is there anyway to write a scope statement that would scope on all members of a date hierarchy except the [All] member?

I have tried many many ways using FILTER, EXCEPT, DESCENDANTS, ect but every time, I get the error message saying: An arbitrary shape of the sets is not allowed in the current context.

I need to do some calculations at levels lower than the ALL member and then, let it rollup contextually (according to the excel pivot table selection)

Any ideas?

This has been on my plate for almost a week now and I've found no way around it other than writing multiple scope statements for each level of the hierarchy but in the end I will have to repeat this many times as we are doing this for many date dimensions

There has to be a simpler way, right?

2
scopessasmdx
Did you ever get this resolved? – GregGalloway

2 Answers

1
votes
Scope([Date].[Date].Members, [Date].[Year].[Year].Members)

Date.Date is the key attribute in the Date dimension. [Date].[Date].Members means all the dimension including the lowest level and above. [Date].[Year].[Year].Members means and up to the year level but not the grand total.

1
votes

The best way I have found is to use the Descendants function with the AFTER description flag:

SCOPE([Date].[Date].[All], , AFTER);
// Calculations here.
END SCOPE;