Click here to Skip to main content
14,163,580 members
Click here to Skip to main content
Add your own
alternative version

Stats

23.5K views
2 bookmarked
Posted 5 Jan 2015
Licenced CPOL

Using Recursive Hierarchy Group in SSRS 2012

, 5 Jan 2015
Rate this:
Please Sign up or sign in to vote.

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.

 

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.

License

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

Share

About the Author

Ahmad A.A. Ahmad
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 SQLDeft.com 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.

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190518.1 | Last Updated 5 Jan 2015
Article Copyright 2015 by Ahmad A.A. Ahmad
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid