Export (0) Print
Expand All

Creating Recursive Hierarchy Groups (Report Builder and SSRS)


Applies To: SQL Server 2016

To display recursive data in Reporting Services paginated reports (where the relationship between parent and child is represented by fields in the dataset), set the data region group expression based on the child field and set the Parent property based on the parent field.

Displaying hierarchical data is a common use for recursive hierarchy groups, for example, employees in an organizational chart. The dataset includes a list of employees and the managers, where the manager names also appear in the list of employees.

System_CAPS_ICON_note.jpg Note

You can create and modify paginated report definition (.rdl) files 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.

To build a recursive hierarchy in a tablix data region, you must set the group expression to the field that specifies the child data and the Parent property of the group to the field that specifies the parent data. For example, for a dataset that includes fields for employee ID and manager ID where employees report to managers, set the group expression to employee ID and the Parent property to manager ID.

A group that is defined as a recursive hierarchy (that is, a group that uses the Parent property) can have only one group expression. You can use the Level function in text box padding to indent employee names based on their level in the hierarchy.

For more information, see Add or Delete a Group in a Data Region (Report Builder and SSRS) and Create a Recursive Hierarchy Group (Report Builder and SSRS).

Aggregate Functions that support Recursion

You can use Reporting Services aggregate functions that accept the parameter Recursive to calculate summary data for a recursive hierarchy. The following functions accept Recursive as a parameter: Sum, Avg, Count, CountDistinct, CountRows, Max, Min, StDev, StDevP, Sum, Var, and VarP. For more information, see Aggregate Functions Reference (Report Builder and SSRS).

Tables, Matrices, and Lists (Report Builder and SSRS)
Tablix Data Region (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)

Community Additions

© 2016 Microsoft