Modifying the Report - Adding a Data Field, Subtotals and a Total
We
added a column to the report in our practice in Part I. We need to repeat that action to create a place to
put our new Line Total field.
1.
Right-click
the gray / blue column handle atop the List Price column.
2.
Select Insert
Column to the Right from the context menu that appears.
A new,
empty column appears on the far right of the table.
3.
Click Line
Total to select it in the Fields pane.
4.
Drag the
selection to the Details row (between the header and footer row
we created, and in the same row occupied in the other columns).
5.
Drop the
selection to place it in the new column.
The
new column appears in our table.
6.
Click the Preview
tab, once again, to review the results of our work.
Processing
begins, and the Preview appears. It includes our new data field, as
partially represented in Illustration 25.
Illustration 25: Preview
with New Line Total Column (Partial View)
Now that
we have a numerical value that makes sense, from a business perspective, to
summarize, let's create an aggregate of the Line Total values at the Subcategory
and Category levels.
7.
Click the Layout
tab, once again, to return to the table layout.
8.
In the third
column of the table, second cell from the top, type (or cut and paste) the
following:
=Fields!Category.Value & ": " &Fields!SubCategory.Value
We are simply creating a title, a concatenation of Category
and SubCategory, to appear at each "break" in SubCategory
group. We are placing this in the Group Header for SubCategory,
which serves as a convenient temporary place for such a heading - we will move
it later.
9.
Right-click
the fifth cell from the top in the Line Totals (the right-most) column
(the cell corresponding to the row below the already populated Details
row).
10.
Select Expression
from the context menu that appears.
The Expression
Editor appears once more.
11.
Expand the Datasets
item in the Fields list at the left of the dialog.
12.
Expand AdventureWorks2000
immediately under the expanded Datasets.
13.
Select Sum(Line
Total) in the items that appear under AdventureWorks2000.
14.
Click Insert
to add the Sum(Line Total) item in the Expression list on the
right.
15.
Remove the
comma after Line Total.Value, together with "AdventureWorks2000"
from the expression, so that only the following remains:
=Sum(Fields!LineTotal.Value)
The Edit
Expression dialog should appear in the Expression list as shown in Illustration
26.
Illustration 26: The
Edit Expression Dialog with Our Expression
NOTE: The above might just as easily have been typed in; this
is only an attempt to show that the Expression Editor can be used to
create the expression, as well.
16.
Place the same
expression in the two cells below the cell we have just populated (that is, the
sixth and seventh rows of the Line Totals column).
The
expressions can be cut and pasted, typed, or created using the Expression
Editor, as we have seen in the above steps. We are simply specifying that
we wish to add totals for the Line Totals value, to appear in the
footers for the groups SubCategory and Category, as well as in
the footer for the report (the bottom row). The expression for the totals is
the same because Reporting Services will return the respective totals
based upon the context of their placement.
Once
the two additional cells are populated, the table should resemble that shown in
Illustration 27.
Illustration 27: The
Table with our Subtotal and Total Expressions Added
Let's
check our work with the built-in Preview function once again.
17.
Click the Preview
tab.
Once
the report preview has processed (we can see the page counter at the top
of the Preview tab - it should reach approximately 405 pages), we will "fast
forward to the last page. Here we will be able to see all three totals (the Subcategory
and Category subtotals, and the report total), in one convenient
place.
The page
counter (the Last Page selector button appears circled), is
depicted in Illustration 28.
Illustration 28:
The Page Counter, with Last Page Button Circled
18.
Click the Last
Page button to go to the final page of the report.
19.
Scroll to the
bottom of the last page.
The
report should resemble that partially depicted in Illustration 29. (You
may need to compact the columns on the Layout to condense it to a
similar view).
Illustration 29: The
Last Page of the Report (Partial, Compacted View)
Now
let's clean up the appearance a bit with some formatting.
» 