Hrvoje Piasevoli – BI Blog

SQL Server BI Quiz – 2011- Display "Breadcrumbs" for members of a user hierarchy

Recently, I’ve been provided with a generous offer to participate in the SQL Server BI Quiz – 2011 (http://beyondrelational.com/quiz/SQLServer/BI/2011/default.aspx) as a Quiz Master.

You can read my full question here http://beyondrelational.com/quiz/sqlserver/bi/2011/questions/152/display-breadcrumbs-for-members-of-a-user-hierarchy.aspx.

The task was to fill in the MDX expression for a calculated measure ‘Parent Breadcrumbs’ that should display a string consisting of all parent names separated by a ‘ –> ’ for a user hierarchy. So for example for product member “Fender Set – Mountain” (user hierarchy [Product].[Product Categories]) the measure value should be the following string “All Products -> Accessories -> Fenders”.

WITH
MEMBER [Measures].[Parent Breadcrumbs] AS
/*
TODO:
*/

SELECT
{
    [Measures].[Order Count],
    Measures.[Parent Breadcrumbs]
} ON 0,
TopCount
(
    [Product].[Product Categories].[Product]
    ,10
    ,[Measures].[Order Count]
) ON 1
FROM [Adventure Works]
;

Here is the screenshot of the desired result:

Sadly, my question received only a single answer and now that the Quiz is closed I wanted to comment both on the provided answer and my solution for it. Thank you Guenter for participating and providing a very good answer and explanation.

Here is the proposed answer to the question by Guenter but I recommend you read his whole answer as he provided a very good explanation:

member Measures.[Parent breadcrumb] as
generate(
  generate(
    ascendants([Product].[Product Categories].currentmember) as X,
    X.Item(X.Count-X.CurrentOrdinal)
  ),
  [Product].[Product Categories].currentmember.Name,
  " -> "
)



The answer provided was quite satisfactory and contained only a minor difference to the task definition (the returned string would contain the current member’s name also) and a pair of curly braces were missing in the inner generate.

Here is my preferred solution:

MEMBER [Measures].[Parent Breadcrumbs] AS
Generate
(
	Hierarchize
	(
		Ascendants([Product].[Product Categories].CurrentMember.Parent)
	)
	,[Product].[Product Categories].CurrentMember.Name
	,' -> '
)



As Guenter noted in his answer, the right way to get the parent members is to use the Ascendants() function. However, the order of members returned is reversed and we have to order the set somehow. The difference here is I am using the Hierarchize() function to order the set in hierarchical order while he is using another approach that is commonly used to reverse a set in MDX. Another difference is that I am returning the Ascendants of the current member’s parent to fulfill the request accurately (only parent “path” should be displayed in the returned cell) as Ascendants() function returns both the specified member and it’s ascendants in the result. Generate(set, <iteration value>, <separator string>) is used to concatenate the parent member names into the requested format.

MDX is very rich and powerful language and there is always more then one way to achieve the same thing. For illustration I’ll provide another method that uses Exists() function instead of Ascendants():

MEMBER [Measures].[Parent Breadcrumbs] AS
Generate
(
	Head
	(
		Hierarchize
		(
			exists
			(
				[Product].[Product Categories].MEMBERS,
				[Product].[Product Categories].CurrentMember
			)
		)
		,[Product].[Product Categories].CurrentMember.Level.Ordinal
	)
	,[Product].[Product Categories].CurrentMember.Name
	,' -&gt; '
)



This may be a little harder to understand. Exists() function used on a user hierarchy this way will return a set that contains: same member, parent members from all levels above, and child members from all levels below the specified member. Therefore we need to take just a certain number of hierarchically ordered elements from this set. This is achieved by using Head() function with a parameter derived from the current member’s level – the Ordinal of the level. As the Ordinal function is 0-based it will not only get the parent members from this set.

So to wrap it a bit, the question and the solution should introduce you to three MDX functions: Ascendants(), Hierarchize() and Generate().

Finally, I want to thank SQL Server MVP Jacob Sebastian (Blog | Twitter) both for providing me with the opportunity to participate in the SQL Server BI Quiz and for his tremendous efforts in the SQL community and to my dear friend Jason Thomas (Blog | Twitter) for introducing me to the SQL Server BI Quiz on http://beyondrelational.com. I’ll also use the opportunity to congratulate Jason for the update in his marital status – Congratulations and best of luck to both of you!

Hrvoje Piasevoli

,