Wednesday, August 25, 2010

Recursive Hierarchy Group in SSRS 2008

In this article, I am going to explain how to create a Recursive Hierarchy Group in SSRS 2008 to display hierarchical levels. This is helpful when we want to display hierarchical data in a report e.g. employees in an organizational chart as shown below: 

Pre-requisites for this article:
  1. SQL Server 2008 along with SSRS installed
  2. Business Intelligence Development Studio (BIDS)
  3. AdventureWorksDW2008R2 database. Click here to install this database from codeplex.
  4. Familier with SSRS
Below are the stpes to create Recursive Hierarchy Group:
STEP 1: To begin, create new report in your existing solution. Add new Shared Data Source. If you don't know how to create a shared data source, click Creating Shared Data Source in SSRS 2008.

 
STEP 2: Create a new dataset for the report. Just right click on the data source and click Add Dataset... to create new data set as shown below:
In Dataset Properties window, enter dsMain in Name textbox and below T-SQL code in Query textbox:
SELECT [EmployeeKey],[ParentEmployeeKey
,[FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName] EmployeeName],[Title]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
 
Finally click OK to close the window and save changes. The Report Data will look like below image:

STEP 3: Drag and drop Table control and map with dsMain dataset. Select EmployeeName in first column, Title in second column and =Level() expression in the third column. Set following properties for table control:
  • Header Background color = DarkBlue
  • Header Color = White
  • Font = Verdana, 8pt


STEP 4:  Set Group Properties to reference Recursive Parent. Select table control --> click View and check Grouping as shown below:

Open Row Group properties, click on add button and select [EmployeeKey] in Group on box.
 
Click on Advance tab of Group Properties window and select [ParentEmployeeKey] in Recursive parent box. Click OK and save changes.
 
STEP 5: We are done with report development. Click on Preview to generate the report. You can see a report like one shown below:
Here, you can see the Employee Hierarchy but not as per standard. We need to format the text to make it better report. Do below formating for EmployeeName textbox:
1) Enter below expression in FontWeight under Font Property:
=IIF(Count(Fields!EmployeeName.Value, "Details", Recursive) > 1, "Bold", "Normal")

2)  Enter below expression in LeftIndent under Indent Property:
=CStr(2 + (Level()*20)) + "pt"

Again Preview the report, report will look like below image:


Cheers!!!

10 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. THANK YOU THANK YOU THANK YOU!
    I was struggling with this for hours and after finding your post I had it working in minutes!
    Much appreciated.

    ReplyDelete
  3. Do you think we can make this as collapse and expand?

    ReplyDelete
  4. Thanks very much. It's easlier than write new store procedure

    ReplyDelete
  5. hello, i have a similar case but not with customers but with Accounting Items where they have Sale Prices etc.. and i needed to show also a total of each group ,i am really struggling into this for 2 days almost .

    in your example: Roberto must have a TOTAL ROW
    TERRY LEE Must have a TOTAL ROW including Roberto and separate ,then DAVID must have a Total Row, How am i going to do all this grouping with N Level Hierarchy

    ReplyDelete
  6. Hello,
    I have created recursive ssrs report successfully, I want to apply search option of "full name" on top of that report, when I am applying the parameter search then it is giving result but not with hierarchy. can you suggest me how I would get the result with search having hierarchy.

    ReplyDelete
  7. Thanks for sharing this information!

    ReplyDelete


  8. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course

    ReplyDelete

Here are few FREE resources you may find helpful.