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.
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
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.
» 
Comment and Contribute