MSSQL Server Reporting Services: The Authoring Phase: Overview Part II - Page 3

March 29, 2004

Performing Grouping and Sorting

Let's look first at an important capability in any reporting application, Grouping. We can group our data by fields or by expressions, within our selection of the table, list, and matrix data regions (we will work with other regions as the series progresses). Groups provide the dual benefit of allowing us to establish sections within our table to meet the business objectives of the report, and to establish header and footer regions, where we can insert subtotals and / or expressions, as we shall see.

Let's add a logical grouping to our report whereby the Products we present are grouped by Category, then by Subcategory, to meet a hypothetical business requirement that has been described by the intended audience. We will proceed by taking the following steps:

1.  Click the Table (at any point inside it) on the Layout tab, so that column and row headings (called "handles" in the Report Designer) appear at the top and left of the Table, as shown in Illustration 6.

Click for larger image

Illustration 6: The Report Definition File in the Solution Explorer Pane

The row handles appear on the left of the table, while the column handles cross the top. A corner handle, at the point where the row and column handles intersect, also exists, to allow us to adjust the table as a whole.

In Part I, we added a column to make a place for a fourth data element we wished to appear, the List Price. We will find that the same right-click action we used at that point comes to our assistance here, offering us the grouping capability we need within the context menu that it affords.

2.  Right-click on the handle of any of the three rows in the table.

The context menu appears.

3.  Select Insert Group from the context menu, as depicted in Illustration 7.


Illustration 7: Select Insert Group

The Grouping and Sorting dialog appears, defaulted to General tab.

4.  Type Category into the Name box.

This will name the Group we are creating.

5.  Click the first row in the Group On box, just beneath the Name box, to the right of the asterisk ("*") symbol.

Clicking this row enables a selector, from which we can select the data element upon which we wish to group.

6.  Select =Fields!Category.Value from the drop down selector.

The General tab of the Grouping and Sorting dialog appears as depicted in Illustration 8.


Illustration 8: The Grouping and Sorting Dialog - General Tab with Our Additions

7.  Click the Sorting tab.

8.  Click the first row in the Sort On box, within the Expression column.

Clicking this row enables a selector, just as we saw with on the General tab, from which we can select the expression upon which we wish to sort.

9.  Select =Fields!Category.Value from the drop down selector.

10.  Leave the Direction value at Ascending, the default.

We have established sorting on Product name, in alphabetical order.

The Sorting tab of the Grouping and Sorting dialog appears as shown in Illustration 9.


Illustration 9: The Grouping and Sorting Dialog - Sorting Tab with Our Additions

11.  Click OK.

The Grouping and Sorting dialog closes, and we are returned to the table, shown in Illustration 10.


Illustration 10: Table, with Group Header and Footer

We note that both a group header and footer have been added to the table (compare it to the "handle" view in Illustration 6, and notice that two new rows have been added to accommodate the header and footer).

Let's add another group for SubCategory, which will be subordinate to the Category group we have created in the last few steps.

12.  Right-click on a row handle once again.

The context menu appears.

13.  Select Insert Group from the context menu.

The Grouping and Sorting dialog appears, defaulted to General tab.

14.  Type SubCategory into the Name box, to name the new group.

15.  Click the first row in the Group On box, just beneath the Name box, to the right of the asterisk ("*") symbol.

16.  Select =Fields!SubCategory.Value from the drop down selector.

The General tab of the Grouping and Sorting dialog appears.

17.  Click the Sorting tab.

18.  Click the first row in the Sort On box, within the Expression column.

19.  Select =Fields!SubCategory.Value from the drop down selector.

20.  Leave the Direction value at Ascending, the default.

21.  Click OK.

The Grouping and Sorting dialog closes, and we are returned to the table. Let's take a look at our progress so far with a preview of the report.

22.  Click the Preview tab, next to the Layout tab

The report generates and presents a preview, similar to that partially shown in Illustration 11.


Illustration 11: Report Preview (Partial View) with Groupings Apparent

We can easily see that the Products are grouped by Category and SubCategory. To proceed with our next steps, we will need to make our data source richer. While we have a "List Price" for the Products, it is unlikely that adding together the list prices of the products we sell would serve a typical business purpose. However, if we could present the total sales of each of the SubCategories and Categories of Products in our report, this would be a "value add" to present to the information consumers.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers