Recently I got a forum question that I thought would be a good blog entry for others to share.

The question was

“Adam, Is there a way to get the ultimate parent of a member when you have no idea what level it is at, how many levels separate it from the ultimate parent? Will just adding a large levels parameter value be valid even if that value is greater than the actual levels?”

To Find the parent at any specific level, you can not only use member functions such as parent, prevmember, etc.., but we can also use the Levels function.

The Levels Function allows you to return the value from a specific level of the hierarchy starting with 0 and ending with the number of levels in the hierarchy. So for example, you may not know how many levels are between you and the top level of the hierarchy, but you can run a query like the one below from my example and return each level.

If you walk through this code in AdventureWorks you can.

Select [Customer].[Customer Geography].Levels(0) on Columns From [Adventure Works];

Select [Customer].[Customer Geography].Levels(1) on Columns From [Adventure Works];

Select [Customer].[Customer Geography].Levels(2) on Columns From [Adventure Works];

Select [Customer].[Customer Geography].Levels(3) on Columns From [Adventure Works];

Select [Customer].[Customer Geography].Levels(4) on Columns From [Adventure Works];

Select [Customer].[Customer Geography].Levels(5) on Columns From [Adventure Works];

It will allow you to traverse the Customer Geography hierarchy and return any level counting from the top, instead of trying to traverse upward. See Screenshot for some more help