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!!!