Error: The MDX function ROOT failed because...

I'm having problem calculating sales for all customer minus certain occupations.

This Adventureworks query is something like what Excel 2003 produces.

Code Snippet

WITH MEMBER [Customer].[Occupation].[Exclude Occupations] AS

'AGGREGATE({

[Customer].[Occupation].&[Clerical],

[Customer].[Occupation].&[Management]

})'

MEMBER Measures.[Other Occupation Sales] as (ROOT(Customer), [Measures].[Internet Sales Amount]) - [Measures].[Internet Sales Amount]

SELECT

{Measures.[Other Occupation Sales]} ON COLUMNS,

[Product].[Category].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE

([Customer].[Occupation].[Exclude Occupations])

Error: The MDX function ROOT failed because the coordinate for the 'Occupation' attribute contains a set.

If you delete this line, the query works!

[Customer].[Occupation].&[Clerical],

Does anyone know what this means? Is there another way to get Excel to formulate an equivalent query?

The problem is because there is a set in the current coordinate, and Root has problem dealing with it (although it shouldn't!). The best solution is to replace Root(Customer) with [All Customers] - which will have exactly same functionality, better performance, and won't generate the error that started this thread.|||

Yes, I replaced Root(Customer) with [Customer].[Occupation].[All Customers] and it works. The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All". I thought Root did precisely that. If I can't use Root() then I will need to remember to update this calculation every time an attribute is added or removed from the customer dimension.

Thanks for the input.

|||

> The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All"

[All Customers] does exactly that ! There is no need to update your calculation every time new attribute is added, [All Customers] (in any hierarchy of customers dimension) will automatically move coordinate to All member in every attribute.

|||

Try my AdventureWorks repro with [All Customers] instead of Root(). It doesn't work. It doesn't replace the aggregate set of occupations with the All tuple. I end up with zeros in the results because [All Customers] is doing absolutely nothing for me.

The only way I got it to work was with [Customer].[Occupation].[All Customers]. This member does, in fact, replace my aggregate set of occupations.

Put that in your pipe and smoke it!

I'm curious how [All Customers] is defined to work (vs. Root()).

|||Of course - you are right. I don't know what I was thinking. What I said is only true when dimension has single hierarchy which includes all attributes, which is obviously not the case for most of the dimensions, especially Customer.|||Thanks for the help. At least I have a work-around for now. It would be nice if Root(dimension) did the same thing as change coordinates to [All Customers] on every attribute. Maybe in the next version...