Modifying the Report - Adding a Data Field, Subtotals and a Total
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.
the gray / blue column handle atop the List Price column.
Column to the Right from the context menu that appears.
empty column appears on the far right of the table.
Total to select it in the Fields pane.
selection to the Details row (between the header and footer row
we created, and in the same row occupied in the other columns).
selection to place it in the new column.
new column appears in our table.
Click the Preview
tab, once again, to review the results of our work.
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)
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.
Click the Layout
tab, once again, to return to the table layout.
In the third
column of the table, second cell from the top, type (or cut and paste) the
=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
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
from the context menu that appears.
Editor appears once more.
Expand the Datasets
item in the Fields list at the left of the dialog.
immediately under the expanded Datasets.
Total) in the items that appear under AdventureWorks2000.
to add the Sum(Line Total) item in the Expression list on the
comma after Line Total.Value, together with "AdventureWorks2000"
from the expression, so that only the following remains:
Expression dialog should appear in the Expression list as shown in Illustration
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.
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).
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.
the two additional cells are populated, the table should resemble that shown in
Illustration 27: The
Table with our Subtotal and Total Expressions Added
check our work with the built-in Preview function once again.
Click the Preview
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
counter (the Last Page selector button appears circled), is
depicted in Illustration 28.
The Page Counter, with Last Page Button Circled
Click the Last
Page button to go to the final page of the report.
Scroll to the
bottom of the last page.
report should resemble that partially depicted in Illustration 29. (You
may need to compact the columns on the Layout to condense it to a
Illustration 29: The
Last Page of the Report (Partial, Compacted View)
let's clean up the appearance a bit with some formatting.