Grouping Data in a Report

Artykuł
12/03/2008

The data within each of the data regions (table, matrix, list, and chart) 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.

To view instructions about grouping data in data regions, see:

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.

Use the following tutorial 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.

In Data view, create a dataset based on the AdventureWorks database.

In the dataset, use the following query:

SELECT FirstName, LastName, EmployeeID, ManagerID
FROM   HumanResources.Employee E INNER JOIN
       Person.Contact C ON  E.ContactID=C.ContactID

In Layout view, create a table data region.
In the first detail cell of the table, type the following expression:
```
=Fields!FirstName.Value & " " & Fields!LastName.Value
```
Right-click the table corner, and then click Properties.
On the Group tab, click Details Grouping.
On the General tab, in the Expression box, type or select the following expression:
```
=Fields!EmployeeID.Value
```
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, select the text box in the first detail cell. Open the properties window for the text box. Expand the Padding node and click the Left property. Choose <Expression…> from the drop-down list and enter the following:

=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 Level function, see Level Function (Reporting Services).

Change History

Release	History
14 April 2006	Changed content: Corrected query to work with AdventureWorks database instead of AdventureWorks2000.

Udostępnij za pośrednictwem

Grouping Data in a Report

Recursive Hierarchies

Level Function

See Also

Concepts

Help and Information

Change History

Dodatkowe zasoby