Grouping Data in a Report (Visual Studio Report Designer)

  • Article

The data within the table, matrix and list data regions can be grouped by fields and expressions. You can use groups inside of a table to provide logical sections of data within the table. You can also and add subtotals and other expressions to the group header or footer. In a matrix, groups are displayed as dynamic columns or rows. You can nest groups within other groups and add subtotals. You can use lists to provide separate groups in a report, or you can place lists within lists for nested groups.

Setting Group Properties on Data Regions

Data regions are data-bound report items that are used to display repeating rows of data. You can set group properties on any data region to organize how the data is presented.

Recursive Hierarchies

A recursive hierarchy is a hierarchy of data in which all parent-child relationships are represented in the data. For example, an organization chart displaying manager-employee relationships can be created using a recursive hierarchy. In such a hierarchy, the table would have columns for employee ID and manager ID. The manager ID would refer to the employee ID of another employee, resulting in a hierarchy of employees.

To build a recursive hierarchy, you must set certain properties for a group within a data region. Use a field containing a unique ID (for example, the employee ID) as the group expression, and then use a field containing the ID of the parent (for example, the manager ID) in the Parent property. A group that is defined as a recursive hierarchy (that is, a group that uses the Parent property) can have only one group expression.

Follow the steps below to create a recursive hierarchy using the Employee table in the AdventureWorks database. This tutorial assumes that you know how to create reports, datasets, queries, and tables. For information about these features, see the documentation about these features.

  1. Create a dataset based on the AdventureWorks database that returns data from the following query:

    SELECT FirstName, LastName, EmployeeID, ManagerID

  2. Add a client report definition (.rdlc) to your project, and then open the report in graphical design mode.

  3. Add a table data region to the report layout.

  4. In the first detail cell of the table, type the following expression:

    =Fields!FirstName.Value & " " & Fields!LastName.Value

  5. Right-click the table corner, and then click Properties.

  6. On the Group tab, click Details Grouping.

  7. On the General tab, in the Expression box, type or select the following expression:

    =Fields!EmployeeID.Value

  8. In the Parent Group box, type or select the following expression:

    =Fields!ManagerID.Value

Level Function

You can use the Level function in text box padding to indent employee names based on their level in the hierarchy. To do so with the table in the above example, use the following expression for the left padding of the text box in the first detail cell:

=Convert.ToString(2 + (Level()*10)) & "pt"

The padding properties all require a string in the format nnxx, where nn is a number and xx is the unit of measure. By default, padding for a textbox is 2pt. The above expression builds a string that uses the Level function to increase the size of the padding based on level. For example, a row with a level of 1 would result in a padding of 12pt (2 + (1*10)), and a row with a level of 3 would result in a padding of 32pt (2 + (3*10)).

For information about the functions you can use, see Built-in Functions for Reports (Visual Studio Report Designer).

See Also

Tasks

How to: Filter Data in a Report (Visual Studio Report Designer)

Concepts

Using Expressions in a Report (Visual Studio Report Designer)