SSRS - How to Create Recursive Report

In Reporting Services paginated reports, a recursive hierarchy group organises data from a single report dataset that includes multiple hierarchical levels, such as the report-to structure for manager-employee relationships in an organisational hierarchy.

In this tutorial, we are going to learn how to use SSRS to report on recursive data. If you have a self referencing table or even a Parent-Child hierarchy setup in an SSAS multidimensional cube you could find these techniques useful. In our example below we want to display the data within a multi-level hierarchy. We will be working with HR data and we want to see the CEO at the top of hierarchy and then see who reports to who after that.

To create a recursive hierarchy group - There are very simple steps to create a recursive hierarchical report as given below: 

1. In Design view, add a table, and drag the dataset fields to display. Typically, the field that you want to show as a hierarchy is in the first column.
2. Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. In the Row Groups pane, right-click Details, and then click Edit Group. The Group Properties dialog box opens.
3. In Group expressions, click Add. A new row appears in the grid.
4. In the Group on list, type or select the field to group.
5. Click Advanced.
6. In the Recursive Parent list, enter or select the field to group on.
7. Click OK.
8. Run the report. The report displays the recursive hierarchy group, although there is no indent to show the hierarchy

For demo of this report, you can visit our YouTube channel as well -