Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / recursion

Using Recursive Hierarchy Group in SSRS 2012

4.71/5 (5 votes)
5 Jan 2015CPOL2 min read 34.7K  

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:

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:

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.

Image 1

 

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

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

 

Image 2

 

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.

Image 3

 

 

 

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.

Image 4

 

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

Image 5

 

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"

Image 6

 

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")

Image 7

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

Image 8

 

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)