Click here to Skip to main content
15,667,864 members
Articles / Recursion
Posted 5 Jan 2015


2 bookmarked

Using Recursive Hierarchy Group in SSRS 2012

Rate me:
Please Sign up or sign in to vote.
4.71/5 (5 votes)
5 Jan 2015CPOL2 min read


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.


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:

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:

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.


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


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

Written By
Database Developer
Canada Canada
My name is Ahmad Ahmad, I’m a SQL Server developer and a Microsoft Certified Professional, I have 5+ years hands-on experience in SQL Server Reporting Services, SQL Server Integration Services and SQL Database Development.

Until recently I built my own blog and published articles on SSIS, SSRS, T-SQL and DW, it's still growing, since I have had some free time I decided to dedicate my time for writing and sharing knowledge with others.

I would welcome any connect invitation on LinkedIn.

Comments and Discussions

QuestionHow can I show the serial numbers for recursive hierarchy? Pin
Member 1457611131-May-20 8:54
Member 1457611131-May-20 8:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.