Report Definition and Design
Reporting
Services includes a professional report design tool called Report
Designer that is hosted in Visual Studio 2005 or SQL Server Business
Intelligence Development Studio. Report Designer provides rich graphical
query tools, report layout design surfaces, and a built-in report
viewer. This tool is geared to programmers and skilled reporters.
Business analysts and
users can make their own reports using a client-side ClickOnce
application called Report Builder. Data models hide the underlying
tables and joins so reporting becomes a drag-and-drop affair. Report
Builder definitions use the same format as Report Designer and can be
used as a starting point for more complex reports.
Hardcore programmers
and third-party vendors can embrace the Reporting Services architecture,
XML file structures, and SDK tools to create their own report
development suite. Microsoft and many independent software vendors
(ISVs) are integrating Reporting Services into their products.
All these tools combine
some data source query capability with report layout and design. In some
shops, these tasks might be performed by separate individuals or teams.
Report Designer can be used in this environment, but doing layout and
design in one setting is more common. We will first investigate data
sources and then explain how the two main reporting tools use them in
the design process.
Data Sources
Data sources can be
specific to a single report or shared across multiple reports. Reports
can contain one data source or many. Specific data sources are defined
in the RDL file for that report. Shared data sources are stored in a
separate file structure in the development environment and on the report
server.
The three
primary sources for report data are relational tables, multidimensional
cubes, and hierarchical XML structures. Each has syntax and graphical
tools that enable quick and efficient selection, ordering, and grouping
of data. You can combine all three data types in one report, but each
must expose a tabular dataset. The resulting datasets appear in Datasets
Explorer next to the toolbox, as shown in Figure 1. They are used to supply data to report controls and parameters lists.
Datasets
include a list of fields that appear under them in Datasets Explorer.
Fields can be mapped directly to a query column or a calculation.
Calculations can be simple concatenations or complex mathematics. You
can change the field names to make them more readable to the report
developer.
Relational Data Sources
The most common
data source is the relational table based on SQL query text or a stored
procedure. The data connection information specifies a server, database
name, and credentials. The specifics depend on the type of database.
Reporting Services supports SQL Server, Analysis Services, Object
Linking and Embedding Database (OLE DB), Open Database Connectivity
(ODBC), and Oracle. Underlying OLE DB and ODBC drivers provide
connectivity to a wide range of data sources. If your data source is not
covered by this list, consider writing a custom data processing
extension to gather the data and package it in the format required by
Reporting Services interfaces.
Multidimensional Data Sources
Analysis
Services cubes and data mining predictions are the second source of
report data. You can use Multidimensional Expressions (MDX) to slice and
dice summarized data from processed cubes. Data Mining Prediction (DMX)
queries use advanced analytics to produce data by probability. Any
multidimensional query must produce a flattened tabular dataset. This
appears in Datasets Explorer, just like a relational query.
XML Data Sources
XML
documents store data in a hierarchical format. Reporting Services can
connect to a Web service, a Web-based application, a well-formed
document available through a URL address, or an embedded XML. It cannot
use native XML data types stored in the SQL Server 2005 database engine
unless they are exposed through a Web service or Web application. Note
that only integrated security and anonymous authentication are
supported. You cannot pass credentials to a Web service.
The XML query syntax
uses XPath and can optionally include a namespace-independent element
name. Where an element name is not specified, the first available leaf
node collection of elements is used. Web services require a SoapAction element with at least one method. Add any required SOAP parameters to the dataset definition.
Report Layouts
You place datasets and
fields into design elements in the Report Designer toolbox. Several
controls display data: table, matrix, list, and chart. Each control can
have one data region. The data region is associated with one dataset.
Some reports have only one data region and data control. More complex
reports mix these elements in any combination to create free-form
content.
Table Control
Table controls display
data in a row-and-column format. The column count and order are static.
The number of rows depends on the record count returned by the
underlying dataset. You can use grouping and sorting options to create
banded reports. Each band can have its own row header and footer. An
example of a table control is shown in Figure 2.
Table
cells contain text box controls by default. You can change this to
another type of control, such as an image. You can merge cells to create
more interesting layouts or add detail rows to display multiple rows of
data for each dataset row.
You add subtotals by
inserting text box controls to group header or footer rows. An aggregate
expression tallies the data for the associated group using a formula
like this one:
=Avg(Fields!ListPrice.Value)
Matrix Control
Matrix controls summarize data, as shown in Figure 3. They are often referred to as crosstabs.
The rows and columns expand to accommodate the summarized data. A
matrix contains four cells by default. The upper-left cell is called the
corner and is typically used to display a label. The upper-right cell
is the column header. The lower-left cell is the row header. You can use
multiple grouping levels for the rows and columns to create a rich,
interactive report. The columns expand to the right depending on the
number of values in the dataset group. The rows expand downward as each
page fills.
The detail cells store one
or more aggregate values. The summary value is calculated for the
intersection of each row and column. If there are multiple aggregates,
each appears in a separate column.
Matrix data regions
work well with Analysis Services cube data. The cubes already have
aggregate totals, so there is little query work for the data engine to
process. You can add drill-down capabilities to make this even more
interesting (as explained shortly).
List Control
Lists
provide free-form arrangement of discrete controls. Each dataset row is
rendered according to the layout defined in the list definition. Lists
can include text boxes, images, tables, matrixes, charts, subreports, or
other lists. The nesting feature allows virtually unlimited design
flexibility. The layout view of a free-form report appears in Figure 4.
Chart Control
Charts display a graphical representation of a dataset, as shown in Figure 5.
Chart types include pie, bar, line, and 3-D charts. Charts have areas
that are similar to tabular groups. Value series are displayed as a
single-line point or bar for each data element. If groups are defined, a
line or bar is displayed for each group. The value series can be
optionally described in the legend.
The chart tool
included with Reporting Services is from Dundas Software and has a good
set of basic features. For more demanding output, Dundas and other third
parties offer richer charts that are compatible with Reporting
Services.
Subreport Control
Many
reporting chores are repetitive. Once a good matrix or chart is
working, you can easily insert it into other, more complex reports. A
subreport owns datasets and layout controls. Each time a subreport is
rendered, the underlying queries execute and the content is rendered.
The result is passed back to the parent report, which inserts the
content into the finished report. Figure 5 shows a sample subreport. The subreport is rendered inside of the parent report in Figure 6.
A
library of smaller report parts can be extremely useful. You can add
these parts to the report server and link them to larger master reports.
If you never want to run the smaller parts on their own, you can hide
them from view but still call them into action for a larger master
report.
Rendering and Pagination
The task of placing
the data onto pages is carried out by the rendering engine. This is a
two-step process. The report datasets are filled based on the query
definition and optional parameters. Sometimes the data is cached and
therefore retrieved quickly. If no cached copy exists, the query is run
and all rows are returned before rendering continues. The resulting data
is stored in temporary tables while the engine attempts to fill each
data region control. The report and page headers are rendered according
to their visibility. As each record is added, the engine checks for the
height and width of the new data. A row that fits is added to the
current page. If the row exceeds the page height, a new page is
triggered and the footer is applied. This process continues until all
datasets and controls are rendered. Because data regions can nest, this
process can take some time and can result in hundreds or thousands of
pages.
The actual
rendering occurs whenever report output is requested. The pagination
results might differ if the report is rendered once in HTML and again in
PDF. Some output formats support a limited set of features, so portions
of a report might not render at all (as explained in more detail
later).
Reports can include
headers and footers that run along the top and bottom of the page,
respectively. They can include page numbers, text, images, and other
design controls. Data regions cannot be placed into headers and footers.
If you want dataset fields in headers and footers, you must write an
expression that references the intended field.
Multiple-column
output is supported in PDF and TIFF output mode. The designer displays
only a single column, but rendered reports will snake data across
multiple columns.
