How to: Create a Recursive Hierarchy Group

  • 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, with a separate field for the item to group and for the item to group by. For example, a dataset might contain a name, an employee name, an employee ID, and a manager ID where you want to group employees recursively under their manager.

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.

    Note

    If the Grouping pane is not visible, click Grouping on the View menu.

  3. In the Row Groups pane, right-click Details, and then click Edit Group. The Group Properties dialog box opens.

  4. In Group expressions, click Add. A new row appears in the grid.

  5. In the Group on list, type or select the field to group, for example, the employeeID.

  6. Click Advanced.

  7. In the Recursive Parent list, type or select the field to group on, for example, the managerID.

  8. Click OK.

    Run the report. The report displays the recursive hierarchy group, although there is no indent to show the hierarchy. To format the hierarchy, follow the steps in the next procedure.

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 Window on the View menu.

  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 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. 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

Concepts

Creating Recursive Hierarchy Groups (Reporting Services)

Grouping and Sorting Report Data

Using Built-in Report and Aggregate Functions in Expressions (Reporting Services)