Working with a Recursive Hierarchy in SSRS 2008

This article explains how to create a recursive hierarchy group, which organizes data in a report to include multiple hierarchical levels. This is helpful when you want to display hierarchical data in a report. For example: employees in an organizational chart, or product subcategory in a product list. The image below is an example:

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 3.0 is installed. Note: We are using Report Builder 3.0 for this example.
• AdventureWorks database
• Experience working with Reporting Services

Steps Taken:

1. Create a new report
2. Add a Data Source and Dataset
3. Add a Table to the report and include dataset field
4. Edit Group properties and reference a Recursive Parent
5. Add custom format expressions
6. Run code

Create a new Report
1) Open Report Builder 3.0 and create a new report.
Note: If you have not done this before, please view my previous article "Creating a Report" for more information.
2) Save the report to your desired Report Server location and name it "RecursiveHierarchyGroup".
Add Data Source and Dataset
Creating the Data Source
1) From the Report Data pane, click New and select Data Source

the data source dialog will open...

Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection.
2) Provide a name for your data source. We will use the default value provided in this example.
3) Select "Use a connection embedded in my report" option
4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.
5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.

6) Enter the same values in the property fields, found in the image above.

1. Test the connection when you are done.
2. If test is successful, click OK.
3. Click OK to close the Connection Properties window.
4. Click OK to close the Data Source Properties window.
5. You should now see your data source name on the left Report Data pane.

Note: the value in Server Name property is a period "."

Creating the Dataset
1) From within the Report Data pane, select New >> Data Set
2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "Organization" in the Name field.
3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.
4) Copy and paste the following T-SQL script to the Query textbox:
select hr.EmployeeID, hr.ManagerID, c.FirstName + ' ' + c.LastName as 'Name', hr.Title
from HumanResources.Employee as hr inner join
Person.Contact c on hr.ContactID = c.ContactID
you should have something similar to below:

5) Click OK to save changes and close the window.
Add Table to Report
1) In Design view, add a table, and drag the following dataset fields to display:

1. Name
2. Title

2) Insert a new column to the right of the Title field and name it "Level".
3) Click in the "Click to add Title" field and enter "Organizational Hierarchy"
Your design view should look similar to the image below:

Edit Group properties and reference a Recursive Parent
4) Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. Optionally, you can select View from menu tab and check Grouping. In the Row Groups pane, right-click Details, and select Group Properties. The Group Properties dialog box opens.
5) In Group expressions section, click Add. A new row appears in the grid.
6) In the Group on list, type or select the field to group - EmployeeID.
Your Group Properties General tab should look similar

7) Click the Advanced tab, from within the left pane of the Group Properties dialog.
8) In the Recursive Parent list, type or select the field to group on - ManagerID.
9) Click OK.
10) Run the report. You will notice the list of Employee names and titles. However, we need to do some formatting to indent the hierarchy accordingly.
Add custom format expressions
Indent Hierarchy
1) Right click on the [Name] field and select Text Box Properties...
2) From the left property pane, select the Alignment tab.
3) From under the Padding options section, select the Left expression button.

After clicking the expression button, you should see the following expression dialog.

4) Enter the following script in the expression textbox:
=CStr(2 + (Level()*10)) + "pt"
The Padding properties all require a string in the format nnyy, where nn is a number and yy is the unit of measure. The example expression builds a string that uses the Level function to increase the size of the padding based on recursion level. For example, a row with a level of 1 would result in a padding of (2 + (1*10))=12pt, and a row with a level of 3 would result in a padding of (2 + (3*10))=32pt.
Set Font Style
1) From the left property pane, select Font.
2) Under the Style section, click Bold expression button.
3) Enter the following script in expression textbox:
=IIF(Count(Fields!Name.Value, "Details", Recursive) > 1, "Bold", "Normal")
Essentially the Count function is searching the Fields!Name.Value, from within the "Details" group, recursively. If the recursive parent has children, then the parent is set to Bold, else Normal.
4) Click OK.
5) Right click on the Level field and click Expression...
6) Enter the following script:
=Level()
The Level() Function returns the current level (Integer) of depth in a recursive hierarchy.
7) Click OK.
5) Run the report. You should see similar results.

That's all there is to it.