Detecting the root member

The root member is the topmost member of a hierarchy. It is present in all hierarchies (in user hierarchies as well as in attributes hierarchies) as long as the IsAggregatable property is enabled, as in its default state.

The root member represents the highest level of granularity within a hierarchy the data can be aggregated up to. When calculating the percentage of a total, we need to detect whether the current member in the query context is pointing to the root member of a hierarchy or not. Based on the detection result, we can make our calculation of the percentage of a total response different to that of the root member.

Although we have the real-world application of the root member detection in mind, this recipe shows ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.