locked
Error: The MDX function ROOT failed because... RRS feed

  • Question

  • 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?

     

     

     

    Wednesday, April 11, 2007 11:30 PM

Answers

  • 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.
    Thursday, April 12, 2007 2:22 AM

All replies

  • 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.
    Thursday, April 12, 2007 2:22 AM
  • 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.

     

    Thursday, April 12, 2007 12:37 PM
  • > 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.

    Thursday, April 12, 2007 2:02 PM
  • 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()).

     

    Thursday, April 12, 2007 11:09 PM
  • 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.
    Friday, April 13, 2007 3:11 AM
  • 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...
    Friday, April 13, 2007 12:50 PM