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]
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])
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)
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!