Understanding Groups (Reporting Services)
In Report Designer, a group is a named set of data from the report dataset that is bound to a data region. Basically, a group organizes a view of a report dataset. All groups in a data region specify different views of the same report dataset.
To help visualize what a group is, refer to the following figure that shows the Tablix data region in Preview. In this figure, the row groups categorize the dataset by product type and the column groups categorize the dataset by geographic region and year.
The following sections help describe the various aspects of groups.
A group has a name and a set of group expressions that you specify. The set of group expressions can be a single dataset field reference or a combination of multiple expressions. At runtime, Report Designer combines and applies group expressions to data in a group. For example, you have a group that uses a data field to organize the data in the data region. At runtime, Report Designer first organizes the data by date, and then displays and totals other dataset values for each date.
In most cases, Report Designer automatically creates a group for you when you design a data region. For a table, matrix, or list, groups are created when you drop fields on the Grouping pane. For a chart, groups are created when you drop fields on the chart drop-zones. For a gauge, you must use the gauge properties dialog box. For a table, matrix, or list, you can also create a group manually. For more information, see see How to: Add or Delete a Group in a Data Region (Reporting Services). For an example of how to add groups when you create a report, see Tutorial: Creating a Basic Table Report.
After you create a group, you can set data region-specific properties, such as filter and sort expressions, page breaks, and group variables to hold scope-specific data. For more information, see Working with Group Features (Reporting Services).
To modify an existing group, open the appropriate group properties dialog box. You can change the name of the group. Also, you can specify group expressions based on a single field or multiple fields, or on a report parameter that specifies a value at run time. You can also base a group on a set of expressions, such as the set of expressions that specify age ranges for demographic data. For more information, see Examples of Group Expressions (Reporting Services).
If you change the name of a group, you must manually update any group expressions that refer to the previous name of the group.
Understanding group organization can help you design data regions that display different views of the same data by specifying identical group expressions.
Groups are internally organized as members of one or more hierarchies for each data region. A group hierarchy has parent/child groups that are nested and can have adjacent groups.
If you think of the parent/child groups as a tree structure, each group hierarchy is forest of tree structures. A Tablix data region includes a row group hierarchy and a column group hierarchy. Data associated with row group members expands horizontally across the page and data associated with column group members expands vertically down the page. The Grouping pane displays row group and column group members for the currently selected Tablix data region on the design surface. For more information, see Grouping Pane.
A chart data region includes a category group hierarchy and a series group hierarchy. Category group members are displayed on the category axis and series group members are displayed on the series axis.
Although typically not needed for gauge data regions, groups do let you specify how to group data to aggregate on the gauge.
Data regions that expand as a grid support different groups than data regions that display summary data visually. Thus, a Tablix data region, and the tables, lists, and matrices that are based on the Tablix data region, support different groups than a chart or gauge. The following sections discuss the type of and purpose for grouping in each type of data region.
Although groups have different names in different data regions, the principles behind how you create and use groups are the same. When you create a group for a data region, you specify a way to organize the detail data from the dataset that is linked to the data region. Each data region supports a group structure on which to display grouped data.
Groups in a Tablix Data Region: Details, Row, and Column Groups
As shown earlier in this topic, a Tablix data region enables you to organize data into groups by rows or columns. However, row and column groups are not the only groups available in a Tablix data region. This data region can have the following types of groups:
Details Group The Details group consists of all data from a report dataset after Report Designer applies dataset and data region filters. Thus, the Details group is the only group that has no group expression.
Basically, the details group specifies the data that you would see when you run a dataset query in a query designer. For example, you have a query that retrieves all columns from a sales order table. Thus, the data in this detail group includes all the values for every row for all the columns in the table. The data in this detail group also includes values for any calculated dataset fields that you have created.
The data in a Detail group can also include server aggregates, which are aggregates that are calculated on the data source and retrieved in your query. By default, Report Designer treats server aggregates as detail data unless your report includes an expression that uses the Aggregate function. For more information, see Aggregate.
By default, when you add a table or list to your report, Report Designer automatically creates the Details group for you, and adds a row to display the detail data. By default, when you add dataset fields to cells in this row, you see simple expressions for the fields, for example, [Sales]. When you view the data region, the details row repeats once for every value in the result set.
Row groups and column groups You can organize data into groups by rows or columns. Row groups expand vertically on a page. Column groups expand horizontally on a page. Groups can be nested, for example, group first by [Year], then by [Quarter], then by [Month]. Groups can also be adjacent, for example, group on [Territory] and independently on [ProductCategory].
When you create a group for a data region, Report Designer automatically adds rows or columns to the data region and uses these rows or columns to display group data.
Recursive hierarchy groups A recursive hierarchy group organizes data from a single report dataset that includes multiple levels. For example, a recursive hieararchy group could display an organization hierarchy, for example, [Employee] that reports to [Employee]. Reporting Services provides group properties and built-in functions to enable you to create groups for this kind of report data. For more information, see Creating Recursive Hierarchy Groups (Reporting Services).
The following list summarizes the way you work with groups for each data region:
Table Define nested row groups, adjacent row groups, and recursive hierarchy row groups (such as for an organizational chart). By default, a table includes a details group. Add groups by dragging dataset fields to the Grouping pane for a selected table.
Matrix Define nested row and column groups, and adjacent row and column groups. Add groups by dragging dataset fields to the Grouping pane for a selected matrix.
List By default, supports the details group. Typical use is to support one level of grouping. Add groups by dragging dataset fields to the Grouping pane for a selected list.
After you add a group, the row and column handles of the data region change to reflect group membership. When you delete a group, you have the choice between deleting the group definition only or deleting the group and all its associated rows and columns. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns.
To limit the data to display or use in calculations for detail or group data, set filters on the group. For more information, see Filtering Data in a Report.
By default, when you create a group, the sort expression for the group is the same as the group expression. To change the sort order, change the sort expression. For more information, see Grouping and Sorting Report Data.
Understanding Group Membership for Tablix Cells
Cells in a row or column of a Tablix data region can belong to multiple row and column groups. When you define an expression in the text box of a cell that uses an aggregate function (for example, =Sum(Fields!FieldName.Value), the default group scope for a cell is the inner most child group to which it belongs. When a cell belongs to both row and column groups, the scope is both innermost groups. You can also write expressions that calculate aggregate subtotals scoped to a group relative to another set of data. For example, you can calculate the percent of a group relative to the column group or to all data for the data region (such as =Sum(Fields!FieldName.Value)/Sum(Fields!FieldName.Value,"ColumnGroup")). For more information, see Understanding the Tablix Data Region and Calculating Totals and Other Aggregates (Reporting Services).
Groups in a Chart: Category and Series Groups
A chart supports nested category and series groups. Charts do not display detail data. Add groups to a chart by dragging dataset fields to the category and series drop zones for a selected chart.
Shape charts such as pie charts support category groups and nested category groups. Other charts such as bar charts support category groups and series groups. You can nest groups, but must make sure that the number of categories or series do not obscure the presentation of information about the chart. For more information, see Adding Data to a Chart Data Region.
Groups in a Gauge: Data Groups
A gauge typically does not use groups, but supports specifying group data. When you specify a group in a gauge, the group is used for aggregations which calculate the displayed data values. Add groups to a gauge through shortcut menu for a selected gauge. For more information, see Adding Data to a Gauge Data Region.