Davy's Blog

Reporting Services 2008 is a great reporting tool, but displaying Analysis Services data with Reporting Services doesn’t always display the data as you would expect.

A Parent-child hierarchy is one example of this: When you browse Parent-child hierarchy from an Analysis Services cube with Excel, Excel automatically displays it with nice drill-down functionality.

But when you add it to a Reporting Services report, you get something like this:

That’s not how we like to display a Parent-Child hierarchy to our users!

To display it with the expected drill-down you need to do some extra configuration:

The following example uses the AdventureWorks example cube available on CodePlex:

Create a new report and add a dataset with the following MDX query:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Organization].[Organizations].[Organization Level 01].ALLMEMBERS) ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,

LEVEL_NUMBER ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Add a Tablix to your report and drag the parent-child hierarchy “Organizations” en the fact “Amount” to the Detail Row of the Tablix.

To enable drill-down you need to edit the Group Properties of the Row Group:

Configure the group to iterate on the UniqueName of “Organizations”:

Go to the Advanced options and set the property “Recursive Parent” to the ParentUniqueName of “Organizations”.

Change the visibility of this Group to “Hide” and check the option “Display can be toggled by this report item”. The toggle item should be the Textbox containing the Parent-Child hierarchy.

Now optimize the layout by increasing the indent for the lower levels of the hierarchy:

In the Textbox properties: set the following expression for “Padding – Left”:

=Cstr(Fields!Organizations.LevelNumber * 10) & “pt”

Click Preview to test the report.

Look’s way better isn’t it?