MDX, SSAS, SSRS

Showing an SSAS Hierarchy as Delimited String

I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors.

Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make this possible. Here an example I put together with the Adventure Works 2008 R2 cube so you can play along.

WITH MEMBER [Measures].[Management Structure] AS
            GENERATE(
            EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All]),
            [Employee].[Employees].CURRENTMEMBER.Name, "  >  ")

Select {[Measures].[Management Structure],
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Order Quantity]} on 0,

NON EMPTY(DESCENDANTS([Employee].[Employees],10,LEAVES)) ON 1

From [Adventure Works]

Where [Employee].[Title].&[Sales Representative]

Pay attention to the calculate member [Measures].[Management Structure]. The way the Generate function works is that it takes one set and applies that set to each Member in another set. In this case, our first set is the ancestor from each level in our hierarchy of the current member, excluding the All member.

So this our first set:

EXCEPT(({Ancestors([Employee].[Employees].CURRENTMEMBER,6),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,5),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,4),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,3),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,2),
                  Ancestors([Employee].[Employees].CURRENTMEMBER,1)}),[Employee].[Employees].[All])

Our second set is the Name property of the CurrentMember of the Employee hierarchy being displayed:
 
[Employee].[Employees].CURRENTMEMBER.Name
 
The Generate function actually also excepts a third argument: A delimiter. We can specify a delimiter. In my example, I use “  >  “ to make the string a bit easier to read. If we execute the query we can see the results of our calculated member.

10-10-2011 9-47-39 PM

Pretty cool, huh? So now that we have each employees management hierarchy in sweet, little string, we can easily display this in a report as a tool tip.

I added the tool tip to the cell in my report’s tablix that contains the employee name. Here’s the expression I used:

=REPLACE(Fields!Management_Structure.Value,">",VBCRLF)

This expression replaces the “>” with a carriage return, line feed. Here’s what our tool tip looks like:

10-10-2011 10-02-24 PM

Using this method, we can easily display the managers for each employee in a tooltip without having to waste any real estate with additional columns.

I hope somebody finds this useful. I’m out!