Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 29, 2004

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

By William Pearson

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:


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM