Using Recursive Hierarchy Group in SSRS 2012

Introduction

In this post I will show you how to perform recursive heirarchies in SSIS, it's useful when you want to show multi-level hierarchical data in your report, such as Employee-Manager, Category-Subcategory relationships in self referenced tables.

Background

I used AdventureWorkDW2012 in this example, you can download it from:

• https://msftdbprodsamples.codeplex.com/downloads/get/165399

In this article I picked up DimEmployee table in AdventureWorkDW2012 database because we have Parent-Child relationship in this table between EmployeeKey (child) and ParentEmployeeKey (parent/manager).

I'll assume that you created already SSRS project using SSDT, if not, please read the following articles:

• Create SSRS Report in SSDT: http://www.sqldeft.com/?p=357
• Define Data Source and Dataset for SSRS Report: http://www.sqldeft.com/?p=385

Using the code

Step 1: Open your SSRS report and create Embedded Connection of type Microsoft SQL Server and set it up to connect to AdventureWorkDW2012 in your local machine.

Step 2: Add Data Set and write query that return EmployeeKey, ParentEmployeeKey, FirstName, LastName, Title from DimEmployee table, as shown below:

SELECT        EmployeeKey, ParentEmployeeKey, FirstName, LastName, Title
FROM            DimEmployee

Step 3: From Menu bar select View >> Toolbox or click Ctrl+Alt+X to show Toolbox pane.

Step 4: Drag Table item from Report Items (Toolbox) to report designer:

• First column (Level): Right Click >> Expression on data cell for this column, paste this expression =Level()
• Second column (Full Name): Right Click >> Expression on data cell for this column, paste this expression =Fields!FirstName.Value+" "+Fields!LastName.Value
• Third column (Job Title): select Title column for report dataset.

Step 5: Right Click >> View >> Grouping on reports body.

Step 6: Grouping option will show up Row Groups and Column Groups at the buttom of the designer, from Row Groups section Right Click >> Group Properties on Details group.

Step 7: From Group Properties' General tab select EmployeeKey from Group on option.

Step 8: Go to Advanced tab and set:

• Recursive parent: select ParentEmployeeKey.
• Document map: paste this expression =Fields!FirstName.Value+" "+Fields!LastName.Value

Step 9: Go back to your table, and single click on Full Name data cell, from Properties edit LeftIndent, use this expression =CStr(2 + (Level()*20)) + "pt"

Step 10: select the entire data row and edit the background color from properties pane to take this expression:

=SWITCH(
Level()=0, "Tomato",
Level()=1, "Gray",
Level()=2, "Silver",
Level()=3, "LightGrey",
Level()=4, "PaleTurquoise")

Step 11: Finally, run the report in preview mode, the report should look like this:

Points of Interest

In this article we handle Parent-Child relarionship in singe dataset by using SSRS recursive feature, it's always recommended to use Document Map with Recursive in SSRS, it does make your report easier to navigate.

I hope these tips was useful for you, if you have any questions please don't hesitate to ask in comments box.

History

Keep a running update of any changes or improvements you've made here.