Share via

Create a Recursive Hierarchy Group (Report Builder and SSRS)

  • Article

A recursive hierarchy group organizes data from a single report dataset that includes multiple hierarchical levels, such as the report-to structure for manager-employee relationships in an organizational hierarchy.

Before you can organize data in a table as a recursive hierarchy group, you must have a single dataset that contains all the hierarchical data, You must have separate fields for the item to group and for the item to group by. For example, a dataset where you want to group employees recursively under their manager might contain a name, an employee name, an employee ID, and a manager ID.

Note

You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com.

To create a recursive hierarchy group

  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.

    Run the report. The report displays the recursive hierarchy group, although there is no indent to show the hierarchy

To format a recursive hierarchy group with indent levels

  1. Click the text box that contains the field to which you want to add indent levels to display a hierarchy format. The properties for the text box appear in the Properties pane.

    Note

    If you do not see the Properties pane, click Properties on the View tab.

  2. In the Properties pane, expand the Padding node, click Left, and from the drop-down list, select <Expression...>.

  3. In the Expression pane, type the following expression:

    =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 that has a level of 1 would result in a padding of (2 + (1*10))=12pt, and a row that has a level of 3 would result in a padding of (2 + (3*10))=32pt. For information about the Level function, see Level.

  4. Click OK.

    Run the report. The report displays a hierarchical view of the grouped data.

See Also

Creating Recursive Hierarchy Groups (Report Builder and SSRS)
Filter, Group, and Sort Data (Report Builder and SSRS)
Aggregate Functions Reference (Report Builder and SSRS)
Tables (Report Builder and SSRS)
Matrices (Report Builder and SSRS)
Lists (Report Builder and SSRS)
Tables, Matrices, and Lists (Report Builder and SSRS)