LearnNowOnline

Blog

Microsoft SQL Server 2008: Creating Groups

Excerpt by Paul Litwin | April 01, 2014

It's difficult to create a very useful report without needing to group the data in some way. A report without any groups is either very simple-and there's nothing wrong with a simple report-or very disorganized. Groups are a great way to organize data in a report into a more manageable assemblage of information. If you need to create subtotals or other statistics you will likely need to create groups.

NOTE While the focus of this chapter-and all of the examples-is tabular reports and row groups created within a Table data region, some of the principles also apply to matrix and list reports, as well as hybrid reports that have attributes of tabular, matrix, and list reports.

The Grouping Pane

While previous versions of Reporting Services supported grouping, SQL Server 2008 Reporting Services has brought report groups to the forefront with the addition of the Grouping pane to the design surface. From the Grouping pane, you can easily view and manage your groups. You can see the Grouping pane at the bottom of the report design surface in Figure 1.

Figure 1. The Grouping pane appears at the bottom of the report design surface.

NOTE This chapter will focus on the row groups that are part of tabular reports. Elsewhere in this course you will find a discussion of Column Groups that are used on matrix reports.

The Details Group

By default, Reporting Services adds a details group-labeled (Details) in the Row Groups pane-to every Table and List data region. (Matrix data regions do not contain a details group and Chart and Gauge data regions do not use the Grouping pane.) The details group is unique in that it is a group that is not based on a grouping expression. Instead, it represents the detail rows in a Table or List data region.

Adding a Row Group

You can add a new row group to a Tablix data region either by dragging dropping a field from the Report Data window and dropping it on the Grouping pane or by using the Grouping pane's popup menu.

Dragging and Dropping

Drag a field from the Report Data window and drop it onto the Row Groups area of the Grouping pane to create a new group. The key to getting the group into the correct place in the group hierarchy for the report is to carefully position the mouse cursor before letting go of the mouse button. As you hover over the existing groups, Reporting Services will draw a blue line to indicate where the new group will be inserted as shown in Figure 2.

Figure 2. The new group will be created as a child of the Country group and as a parent of the details group.

When you create a group using drag and drop, you can only create parent or child groups; you cannot create an adjacent group using this technique. Nor can you control the presence of group header and footer rows, or create a group based on an expression. If you need any of these group options, you'll want to employ the Grouping pane menus to create your group.

Using the Grouping Pane Menus

To add a row group to a report using the Grouping pane menus, click on the down arrow to the right of an existing group and select Add Group from the menu as shown in Figure 3. A submenu will present several choices including Parent Group, Child Group, Adjacent Before and Adjacent After. (Creating adjacent groups will be discussed in more detail in the next section.)

Figure 3. Adding a row group using the Grouping pane.

After selecting the type of group that you want to create, Reporting Services displays the Tablix group dialog box that is shown in Figure 4.

Figure 4. The Tablix group dialog box.

To finish creating the group, select the Group by field using the drop-down list or click the fx button to group on an expression instead. Don't forget to check the Add group header and Add group footer check boxes as appropriate before clicking OK, because Reporting Services makes it difficult to recreate the group header and footer rows once you have dismissed this dialog box.

Adding Row Groups without the Grouping Pane As an alternative to using the Grouping pane, you can right-click on a tablix row to add groups to a report. Just click on a row selector of a detail or existing group row and select Add Group from the popup menu. Depending on the context when you right-click on a row, some grouping options may be disabled or invisible. In general, you'll have better success creating groups using the Grouping pane.

Adding an Adjacent Row Group

Rather than add a group that is a child or parent of an existing group, you can add a group that is adjacent (that is, a sibling of) an existing group by selecting Adjacent Before or Adjacent After from the Add Group popup menu (see Figure 3). Adding an adjacent row group is similar to adding a second tablix region to your report. The major difference is that each tablix region can be bound to a different dataset, whereas all of the groups within a tablix share the same dataset. When you add an adjacent row group, you may be surprised to find that the new group will not have any detail rows. Fortunately, you can add a child details group to the adjacent row group.

This post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Reporting Services course written by expert Paul Litwin.