Parent-Child hierarchies in Reporting Services

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:

clip_image002

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”:

clip_image004

clip_image006

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

clip_image008

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.

clip_image010

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”

clip_image012

clip_image014

Click Preview to test the report.

clip_image016

Look’s way better isn’t it?

Share
  1. Yun Chen
    May 24th, 2010 at 22:07 | #1

    I tried but did not get the same result. The text was indented but the small click box could not be indented.

  2. Pritam Wadke
    Sep 29th, 2010 at 10:44 | #2

    @Yun Chen
    Did you got chance to get resolution on indenting the +/- expander/collapser controlles in the hierarchical report?

    Thanks in advance.

    -Pritam

  3. Rangnath Gondkar
    Sep 29th, 2010 at 13:10 | #3

    Please install SP1 to fix this issue.

  4. Jan 8th, 2011 at 00:40 | #4

    Great article! You really got me out of a jam as there really isn’t much info on this topic.

  5. Lars
    May 6th, 2011 at 13:59 | #5

    Awesome… You’re my Hero :-)

  6. Oct 14th, 2011 at 13:40 | #6

    Nice one Davy, works perfectly!

    BTW: if you don’t want the top-level item to jump in that much, change the Padding-Left expression to:

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

  7. Shyam
    Oct 19th, 2011 at 10:31 | #7

    Good one. Its really help me a lot. Thanks.

    Regards,
    Shyam

  8. Anthony
    Jun 20th, 2012 at 01:38 | #8

    Hi Davy,

    Have you ever tried to control the toggle state of a recursive item independently? That is, can we elect to expand or collapse individual levels based on a data-driven element, such as a boolean Expanded field passed in the data? I attempted this without any luck. I also tried using level comparisons within the scope of the group but since there is only one group visibility setting, if you want to collapse a level you unfortunately have to make it invisible (which means you’ll never have an opportunity to expand it) so that doesn’t work. Thanks for your posts and keep up the good work!

    Anthony

  9. Jul 10th, 2012 at 13:47 | #9

    I don’t think this is possible.

  10. Jose Barreto
    Sep 19th, 2012 at 14:42 | #10

    Thank you very much. It works for me.

    Saludos

  11. Andrew
    Jan 29th, 2013 at 23:11 | #11

    Thank you! This post has restored my faith in SSRS and Report Builder 3.0! Why was this information so hard to find?

  12. Sree
    Apr 15th, 2013 at 11:49 | #12

    Hi Davy,

    I have doubt please clarify me , i have created cube on adventure works db
    i sorted month wise and deployed it when i am creating Report on top of Cube by draging month name its working fine (sorting like Jan,Feb,Mar)
    but when apply grouping to month name its not coming in sorted , how to get in month wise sorted order ?
    Please help me :)

    Regards
    Sree

  13. Peter
    May 10th, 2013 at 00:53 | #13

    Thanks Davy !! Works a treat.

    rgds

  14. Frederik De Jaegher
    May 21st, 2013 at 16:04 | #14

    Thanks for this great article. I was searching for something like this.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>