Databases Reference
In-Depth Information
Note the page header containing the date at the top of this page, the repeated table header, and the table
footer showing the continuation of the CategoryName group, and then the page footer with the page
number and page count.
There are some restrictions regarding the content you can include in page header and footer sections that
can be easily worked around. Field references are not allowed in these areas because the page header
and footer are added to the final report output after the data is processed. Pagination is a feature of each
report format-rendering extension so the report engine has no knowledge of page placement at the time
of the initial report content rendering. You do have access to several resources such as global variables,
parameters, and report items. A simple technique for including data in the page header and footer is to
place a text box in the report body with the desired field reference and then refer to this report item from
the header or footer.
Add a page header and footer by selecting Page Header and Page Footer from the Report menu while
the report is open in Layout view. Select Report Properties from the Report menu. This is where you can
optionally leave a page header or footer off the first or last page of the report.
Now that the page header and footer are visible in the report designer, drag the report title text box into
the header area. You'll also replace the header row of the table with text boxes in the page header. Add
text boxes, and label them with the same text as the columns in the table header row. To separate the
headings from column values, add a line, and place it immediately below the row of text boxes. Resize
the page header area as needed.
In the report body, click on the table and then right-click the row selector to the left of the table header.
Choose Delete Rows to get rid of the header row. Resize the report body as needed. Finally, in the report
footer section, place a horizontal line and a text box item below it.
Aggregate Functions and Totals
Reporting Services supports several aggregate functions, similar to those supported by the Transact-SQL
query language. Each aggregate function accepts one or two arguments. The first is the field reference or
expression to aggregate. The second, optional argument is the name of a data set, report item, or group
name to indicate the scope of the aggregation. If not provided, the current item scope is assumed. The
following table lists the aggregate functions available in Reporting Services.
Function
Description
The average of all non-null values.
AVG()
The count of values.
COUNT()
The count of distinct values.
COUNTDISTINCT()
The count of all rows.
COUNTROWS()
Returns the first value for a range of values.
FIRST()
Returns the last value for a range of values.
LAST()
Returns the greatest value for a range of values.
MAX()
Table continued on following page
Search WWH ::
Custom Search