This week I discovered an incredibly useful MDX function, the function is called Root.  Full documentation can be found here.

In the past, I have encountered scenarios where I want to override filtering and use the All member for specific attribute hierarchies.  I have usually accomplished this by hard-coding the All member in an MDX expression, something similar to:

([Measures].[All Sales], [Customer].[Market].[All]).

But this approach becomes unwieldy if you need to override a large number of attribute hierarchies (or possibly even all attribute hierarchies in a dimension or cube).  It also can be problematic if the cube is updated and a new attribute hierarchy is added, in which case you may need to update your expression to account for the new attribute hierarchy.  The Root function helps avoid these pitfalls.

The Root function returns the All member for each attribute hierarchy within the current scope in a cube, dimension or tuple (If an attribute hierarchy does not have an All member, then the default member is used).  So, it eliminates hard-coding the All member for multiple attribute hierarchies in an MDX expression.

There are three syntaxes for the Root function:

**Cube syntax:  Root(). ** The Root function in this case will return the All member (or default member if the All member does not exist) from each attribute hierarchy in the cube.

**Dimension syntax: Root(Dimension_Name). ** In this scenario, the Root function will return the All member (or default member if the All member does not exist) for each attribute hierarchy in the specified dimension.

For example, if you want to override all filters applied to any attribute hierarchy in the Customer dimension the syntax is:

Root([Customer])

**Tuple syntax: Root(Tuple_Expression). ** When a tuple expression is specified, then the Root function will return a tuple that contains the intersection of the specified tuple and the All members of the other dimension attributes not included in the specified tuple.

For example, if you wanted to override all filters on a specific dimension except those applied to two attribute hierarchies (in this example, Market and Dealer Level) the syntax would be similar to:

Root(([Customer].[Market].CurrentMember,[Customer].[Dealer Level].CurrentMember))

You can also specify a specific member (instead of the CurrentMember) as shown below:

Root([Date].[Jan 1, 2013])

comments powered by Disqus