SQL Server 2005 : Report Definition and Design (part 1) - Data Sources, Report Layouts

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.

Figure 1. Datasets Explorer with three datasets and associated fields

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.

Figure 2. Table control with groups and subtotals in the preview pane

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.

Figure 3. Matrix control with expanded columns and rows in the preview pane

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.

Figure 4. Free-form report with a list and subreport

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.

Figure 5. Chart control in the preview pane

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.

Figure 6. Free-form report with rendered subreport in the preview pane

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.