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.