Export (0) Print
Expand All
Automate Testing of Your Stored Procs
Streamline Your Database Setup Process with a Custom Installer
Stop SQL Injection Attacks Before They Stop You
Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
AMO Lets You Dig Deeper into Your Data from Your Own Applications
Make Sense of Your Web Feedback using SQL Server 2005
Fuzzy Lookups and Groupings Provide Powerful Data Cleansing Capabilities
Unearth the New Data Mining Features of Analysis Services 2005
Using XQuery, New Large DataTypes, and More
Expand Minimize

Report Design: Best Practices and Guidelines

Published: June 1, 2005 | Updated : November 15, 2005

Writers: Brian Welcker and Chris Hayes  

Project Editor: Diana Steinmetz

Applies To: Microsoft® SQL Server™ 2005 Reporting Services

Summary: This paper consolidates general information, best practices, and tips for designing Microsoft® SQL Server™ Reporting Services reports. It is intended to provide a starting point for design questions and an overview of some of the capabilities of Reporting Services.

On This Page

About This Document
Other Sources of Information
Product Versions
Introduction
Authoring Tools and RDL
Report Design Concepts
Report Rendering Considerations
Useful Tips for Report Design
Conclusion

About This Document

The report author faces many decisions, from what kind of data region to use to which format to use for report delivery. Microsoft® SQL Server™ Reporting Services provides support through a wide range of report designs and formats, from paper-based tabular reports to interactive reports with images and drill-through capabilities.

This document consolidates guidelines, suggestions, and tips for report authoring and design. The intent of this document is to address common questions and concerns and to serve as a starting point for further investigation. The Reporting Services Books Online document offers a rich and comprehensive source of information on report authoring and design, and also detailed descriptions of report options and available functions and parameters.

The document is intended for both beginning and more experienced report authors. The beginning author may find the Report Design Concepts section specifically helpful.

Other Sources of Information

This white paper is not intended to be an exhaustive source of information on Reporting Services. For detailed information on the product, see the product documentation and also the available online resources at http://www.microsoft.com/sql/reporting/.

Product Versions

Although this paper is written for Microsoft t® SQL Server™ 2005 Reporting Services, most of the information about report design is also relevant to earlier versions of Reporting Services.

Introduction

Microsoft® SQL Server™ Reporting Services is a comprehensive reporting tool. In using Reporting Services, you can design and deliver a wide range of reports, from simple tables to online free-form reports with graphical items and interactive links. Reporting Services can render reports for a variety of client-viewing formats, including HTML, PDF, Excel, and image formats.

The wide variety of options and choices can be problematic for report authors who must make numerous decisions when choosing how to lay out a report. This paper provides a few guidelines to make these decisions easier and answers common questions.

Authoring Tools and RDL

There are many ways to author a report for Reporting Services. Both SQL Server and Microsoft® Visual Studio® 2005 include a graphical Report Designer tool. SQL Server 2005 Reporting Services offers a Report Builder tool to help business users design reports. There are also several other development tools capable of creating the Reporting Services report format.

Whichever authoring environment you choose, all report definitions are stored in report definition language (RDL), which is a published XML format for defining reports. It’s possible to write RDL directly or create custom tools that generate the format. Report authors can also use graphical authoring tools and then edit the resulting RDL files.

When providing examples to show specific techniques, this paper displays extracts of the resulting RDL file to illustrate how options are used.  

The full RDL specification is available on the Reporting Services Web site at http://www.microsoft.com/sql/reporting/.

Report Design Concepts

This section describes the key factors that affect report design and layout. This content may be helpful if you are just starting to work with Reporting Services. All these topics are covered in more detail in SQL Server Books Online.

In designing a Reporting Services Report, you define the data and then arrange the items on the page. Although this process may sound basic, Reporting Services includes the following features:

  • It supports four kinds of data regions: lists, tables, matrices, and charts.

  • It offers a range of other items such as text boxes, images, links, rectangles, and subreports.

  • User interactivity is available through parameters, links, conditional display, end-user sorting, drill-down, and a document map.

  • Reports may be rendered in a variety of formats, including HTML, Excel, PDF, image files, XML, and comma-separated values (CSV).

The decisions you make about each of these key areas can affect the final display and functionality of the report. The following sections offer more information about some of the factors that affect design and layout.

Data Regions

One of your first critical design decisions is which type of data region to use in your report. Reporting Services offers the following types of data regions:

  • Tables
    A table represents data, row by row. The columns are static in that they do not expand, but the rows expand downward. Thus, as a table grows, it pushes the data beneath it downward. You can also group data within the table.

  • Matrices
    A matrix, or crosstab, is like a table, but both columns and rows can expand to accommodate data. A matrix can push other report objects downward and outward on the report page. You can group data and also nest columns and rows within the matrix.

  • Lists
    A list is a freeform data region that is often used for creating forms. You can nest lists within lists to group data.  

  • Charts
    A chart is a graphical representation of data. Reporting Services offers a wide range of chart formats.

Your choice of data regions may depend on the type of data you are using, what you want the report to look like, and how you are rendering the report. For example, reports rendered to Microsoft® Excel® tend to work better when using a table instead of a list.  

Item Positioning and Sizing

A report consists of three main areas: the page header, page footer, and the body. Reporting Services supports the freeform placement of report items in a report. Data regions can be side-by-side with other regions, or nested within groups.  

When designing reports, it is important to understand how items will behave if they grow. The items in a report may grow either horizontally or vertically, depending on section repeats, content size, and on such rendering-specific reasons as font substitutions.

When an item grows, such as a table, it pushes peer items out of the way. Specifically, peer items are those items within the same parent container. Following are the two ways in which peer items are shifted:

  • Each item moves down to maintain minimum spacing between itself and all the items ending above it.  

  • Each item moves to the right to maintain minimum spacing between itself and the items ending to the left of it.

If an item grows so that it would extend beyond the bounds of the containing item, the container grows to accommodate the contained item.

If an item overlaps another item, the ZIndex element in the report definition determines which element overlaps the other. The item with the higher ZIndex value is rendered on top of the item with the lower value.

There also are techniques you can use to control the way items grow and move other report items. For information about using rectangles to group items, see the section “Useful Tips for Report Design” later in this paper.

Pagination

One key design issue for long reports is controlling where the page breaks occur. Page breaks are controlled by two factors:

  • Page size

  • Page breaks that you specifically include before or after objects

Page Size

To control page size, set the page height and width properties for the report by using the following guidelines:

  • For rendering formats that render physical pages, use the PageHeight and PageWidth properties for the report.

  • Interactive rendering formats, such as HTML, do not render physical page breaks. Instead, use the InteractiveHeight and InteractiveWidth properties to create a soft page break.

  • Some rendering formats, such as Excel, do not support page size. For these reports, you will have to specifically include page breaks to break the report into multiple pages.

Note that if the report itself is wider than the defined page width, the report will break across multiple pages horizontally.

Page Breaks

You can apply page breaks at the beginning or end of a rectangle, table, matrix, list, chart, or group. Reporting Services tries to keep all the data within the item or grouped together on the same page.

To include page breaks specifically before or after items, use the PageBreakAtEnd and PageBreakAtStart properties for the item.

Subreports

A subreport is a report item that points to another report. Any report can be used as a subreport, and you can set up the parent report to pass parameters to the subreport.

You should take care when using subreports for the following reasons:

  • Subreports do not share data with the parent report. The Report Server processes each instance of a subreport as a separate report and this can affect performance.  

  • The headers and footers for the subreport are ignored.

Subreports are useful in the following situations:

  • When you need to nest groups of data from different data sources within a single data region.

  • When the report has multiple one-to-many relationship sections.

  • When you need to reuse a subreport in multiple parent reports.

  • When you want to display a standard, stand-alone report within another report.

Data regions, such as tables, matrices, lists, and charts, provide much the same functionality as subreports. However, they often provide better performance, particularly if the reports share data. Data regions also work better than subreports in side-by-side layouts.

Expressions and Functions

Report authors can use expressions in reports to change the appearance of data, change properties of items, or control data retrieval. The Tips section in this paper provides some examples of designs that use expressions.

You can include Microsoft® Visual Basic® functions within expressions. Some commonly used functions are included in Table 1.

Table 1

Visual Basic Function

Effect

Today()

Returns the current date.

DateAdd()

Supplies a range of dates, based on a single parameter.

Year()

Displays the year for a particular date. Use it to group dates or display the year as a label for a set of dates.

Month()

Displays the month for a set of dates.

Format()

Formats strings. Can be used to format dates and numbers within strings.

Right(), Left(), and InStr()

Returns a substring from a string.

Iif

Returns one of two values, depending on whether an evaluated expression is True. To return one of three potential values, you can nest Iif functions.

You can use any .NET function within an expression, providing the report references the appropriate assembly. You can also use custom assemblies. For more information about this, see the Reporting Services Books Online topic, “Using Custom Assemblies with Reports.”

Aggregate Functions and Scope

Reporting Services provides a number of aggregate functions that can be used to create running totals or other aggregations within the report.

Each aggregate function supports a scope parameter, which defines the scope for the aggregate. The scope might be indicated by the name of a grouping, data set, or data region. The data region or grouping must contain the item with the aggregate function, either directly or indirectly.

If you omit scope, the aggregate scope is the innermost grouping or data region to which the report item belongs.

Reporting Services supports the aggregate functions that are shown in Table 2.

Table 2

Function

Description

Avg

Returns the average of all non-null values from the specified expression.

Count

Returns a count of the values from the specified expression.

CountDistinct

Returns a count of all distinct values from the specified expression.

CountRows

Returns a count of rows within the specified scope.

First

Returns the first value from the specified expression.

Last

Returns the last value from the specified expression.

Max

Returns the maximum value from all non-null values of the specified expression.

Min

Returns the minimum value from all non-null values of the specified expression.

StDev

Returns the standard deviation of all non-null values of the specified expression.

StDevP

Returns the population standard deviation of all non-null values of the specified expression.

Sum

Returns a sum of the values of the specified expression.

Var

Returns the variance of all non-null values of the specified expression.

VarP

Returns the population variance of all non-null values of the specified expression.

Reporting Services also provides functions that are used to provide running aggregate capabilities. These are shown in Table 3.

Table 3

Function

Description

RowNumber

Returns a running count of all rows in the specified scope. When used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. Use it to number rows in a table or provide page breaks. The Nothing keyword indicates that the function begins counting at the first row in the outermost data region, RowNumber (Nothing).

RunningValue

Uses a specified function to return a running aggregate of the specified expression.

Reporting Services offers the following functions for navigating scope. These are shown in Table 4.

Table 4

Function

Description

InScope

Determines whether the current instance of an item is within the specific scope. It takes the name of the dataset, grouping, or data region as an argument and returns a Boolean.

Level

Returns the current level of depth in a recursive hierarchy.

Previous

Returns the previous row.

Report Rendering Considerations

The Report Server provides a default set of rendering extensions. You can remove extensions from the Report Server or add custom extensions to support new rendering formats.

The default set of rendering extensions includes HTML, Excel, comma-separated values (CSV), XML, Image, and PDF.

Note:  These default rendering extensions are available to reports rendered on the Report Server. Locally generated reports that use the SQL Server 2005 Reporting Services Report Viewer controls can support HTML report rendering, but do not necessarily have access to other extensions.

The rendering extension you choose will affect the report layout. For example, not every rendering extension supports forced page breaks or page breaks due to the page size settings. Table 5 highlights some of the differences.

Table 5

Rendering extension

Page break on item or group

Page size

HTML

Yes

Uses InteractiveHeight

Excel

Yes

No

CSV

No

No

XML

No

No

Image

Yes

Yes

PDF

Yes

Yes

GDI (Window Forms)

Yes

Uses InteractiveHeight

If you know how the report will be rendered before you design it, you can optimize the report layout for the target format. If not, you should test the report in the different possible extensions to find and correct formatting problems or anomalies.

The following subsections contain suggestions for using Excel, Image, PDF, and HTML rendering extensions.

Excel

Reporting Services supports rendering reports for Microsoft® Excel® 1997 and later versions. If you plan to render reports to Excel, you should be aware of some of the unique attributes of the Excel rendering extension. For example:

  • Each page in the report becomes an Excel worksheet. Excel does not support the concept of page height and width, so only explicitly defined page breaks will occur.

  • Reporting Services does not support specifying worksheet names.

  • The rendering extension builds a tabular structure out of the report.

  • Excel does not support background images for individual cells.

  • Excel does not support nested containers other than lists.

  • Charts are rendered as pictures, not as Excel charts.

  • Rectangles are converted to groups of cells. If rectangles contain other items, the rectangle becomes a region of cells, and the border and background color of the rectangle are applied to the region of cells.

  • Subreports are rendered as rectangles in the current report on the same worksheet as the parent report.

Using Tables Instead of Lists for Excel-specific Reports

A table uses a fixed column width. This matches very well with the tabular format of Excel reports. The items in the report table will line up as you expect them to when rendered in Excel.

In contrast, a list is a freeform style. Items in the list are positioned in the worksheet relative to their location in the report. This can lead to unexpected results. If your report uses a list, be sure to check the rendering to Excel to see if the results are acceptable.

Even with tables, if you have a header that spans multiple columns in a report, the Excel rendering extension may need to merge cells or introduce new columns. This can affect the ability to sort and manipulate data in the Excel spreadsheet. If you are planning to render to Excel, try to ensure that the left or right edges of the report item line up in order to minimize cell merging.

Maximum Number of Pages in Long Reports

To prevent Excel from generating an error, you should keep track of the number of pages in lengthy reports. Specifically, each page in a report becomes a worksheet in Excel. However, Excel can only support a maximum number of worksheets per workbook, limited by available memory. If the report pages exceed that limit, Excel generates an error.

Color Differences in Rendering to Excel

Excel supports a predefined set of colors. When you render a report, the Excel rendering extension maps the report colors to the best match in the natively supported colors in Excel.

Image

The Image rendering extension renders reports in bitmap or metafiles. By default, it renders data in TIFF format. However, it can generate files in any format supported by GDI+, including BMP, EMF, GIF, JPEG, and PNG.  

An image rendering extension on the Report Server processes the report on a virtual page and creates the image from that virtual page. As a result, the image-based report will look the same, in terms of font and layout, on every client.

When you are working with TIFF files, they can be viewed in multiple pages. However, other image formats generate one file for each report page.

Image rendering also supports page height, page width, and margins. Any headers and footers contained in the report are rendered inside the margins of the report.

Using Image Rendering to Create the Same Reports on All Clients

If you want your reports to look the same on every client, you should use image rendering. Specifically, HTML reports use the client font and browser settings when rendering reports. This means that the layout can change when different browser clients are used. Because image files are formatted on the Report Server, they are rendered to an image file that should appear the same on every client.

Installing the Appropriate Fonts on the Report Server

You should make sure that the fonts you need are installed on the Report Server. This is because the report is actually rendered on the Report Server and it uses the fonts that are installed on the Report Server.

Portable Document Format

The portable document format (PDF) rendering extension creates reports that can be viewed with Adobe Acrobat readers. The PDF extension is similar to the image extension in many ways, except for the following important differences:

  • Fonts are not embedded in the PDF reports.  

  • Document maps are rendered as PDF bookmarks.

  • You can specify page width and height, margins, and resolution of the PDF, among other device options.

  • The rendering extension creates PDF 1.3 files that are compatible with Adobe Acrobat 4.0 and later versions.

  • The PDF rendering extension does not support RepeatWith.

Installing the Appropriate Fonts on the Client Computer

The PDF extension does not embed the fonts in the report. In order to view a report in the correct font, you need to make sure that the fonts are not only installed on the Report Server, but also on the client computer that is used to view the report. Otherwise, font substitution will most likely occur.

HTML

The HTML rendering extension generates HTML 4.0 pages that are compatible with Microsoft® Internet Explorer, Mozilla Firefox, and Apple Safari.

There are several differences between HTML rendering and other renderings, including the following:

  • The HTML rendering extension builds a table in HTML to contain each set of report items. Items are positioned in the table to preserve the report layout.

  • Locations and sizes are expressed in millimeters (mm). Differences of less than .2 mm are rendered as 0 mm.

  • HTML does not support item overlap. This can result in layout changes as the report is displayed.

  • A subreport is rendered as a DIV tag in the HTML report.

Client Variations

When displayed on the client, an HTML report uses the browser-specific settings. In addition to possible font substitutions, other browser settings can likewise produce changes. If you want to precisely control the layout of the report on all clients used to view the report, consider using the image rendering extension.

Differences Between Browsers

SQL Server 2005 Reporting Services includes support for other browsers in addition to Microsoft Internet Explorer, including Mozilla Firefox and Apple Safari. Although most report features are supported on these browsers, you should test your reports on targeted browsers to ensure that they meet your requirements.

Useful Tips for Report Design

This section provides some tips and techniques for using expressions to manipulate report format and layout.   

Using Rectangles to Keep Objects Together

Rectangles in Reporting Services can be used either as graphical elements or as containers of objects. As object containers, they keep objects together on a page and control how object move and push each other.

To keep multiple objects together on a page, put the objects within a rectangle. You can then put a page break before or after the rectangle by using the PageBreakAtStart or PageBreakAtEnd properties for the rectangle.

Using Rectangles to Control Item Growth and Displacement

Items within a rectangle become peers of each other and are governed by the rules of how peer items are positioned on the page as they move or grow. For example:

  • Items will push or displace each other within the rectangle.

  • Items will not push or displace items outside the rectangle, because they are not their peers.

  • If necessary, a rectangle will grow to accommodate the items it contains.

You can use this logic to your advantage when dealing with objects that expand. For example:

  • If you want to leave a blank space in your report for a table to expand into, group the blank space and the table in the same rectangle. When the table grows, it will push the blank space.

  • If you want to prevent a matrix from pushing items off the right edge of the page, put the matrix within a rectangle with blank space to its right. Now, the matrix is no longer a peer to the other item on the page and will not be able to push it until the matrix can no longer be contained within its rectangle.

Avoiding Blank Pages

Sometimes, you will see blank pages when you output reports to a physical page format such as PDF or print. Generally, this will happen when the size of the report body exceeds the size of the page.

To ensure that all the contents fit on a single page, the body width plus the margins should be less than the defined page width. A textbox or other report item can cause the width of the body to exceed the page width, even when the portion of the item that exceeds the width has no visible contents. In addition, report items growing horizontally (matrix data regions and images set to automatically Autosize or Fit) can also cause the body to grow.

Using Page Breaks to Improve Performance for Large Reports

If you do not specify a page size or page breaks for a report that returns a large amount of data, some report formats will try to render the report as a single page.

For example, Excel has no default notion of a fixed page size. As a result, if you have a very large report, Excel will try to render it as a single worksheet. In general, using page breaks improves the performance for the users accessing the report, because they can view the first page while the rest of the report is being rendered.

Using Filters Instead of Query Parameters

Reporting Services has several methods for dynamically filtering report contents, including the following:

  • Query parameters filter data at the source as it is retrieved.

  • Report filters, applied to a dataset or data region, limit the data that is displayed from a generated report.

Using filters retrieves all data, but only data that is relevant to the user is displayed. This may be less efficient on an individual report basis than filtering at the source. However, it lets you retrieve the data once from the source and store in it a snapshot to serve many different user communities. On the other hand, when using query parameters, you must revisit the data source for each new value of the query parameters. Filters enable you to use execution snapshots and still get full parameterization.

Adding Alternating Bars to a Table

It is possible for you to create a report that contains a table or matrix in which every other row is shaded. This bar effect makes it easier to visually track the different rows across a page.

To more closely simulate the old “green bar” paper that was used at one time to run large reports on high-volume data center printers, you can make the alternating bars green.

To achieve this effect, use the Iif function to assign the background color conditionally, based on whether the row number is odd or even. For example:

=iif(RowNumber(Nothing) Mod 2,"PaleGreen","White")

Within the context of the RDF file, the function would resemble the following:

Note: Some of the lines of the following code have been displayed on multiple lines for better readability.

<TableCell> 
  <ReportItems> 
    <Textbox Name="SalesOrderID"> 
      <Style> 
        <PaddingLeft>2pt</PaddingLeft> 
        <Format>d</Format> 
        <BackgroundColor>=iif(RowNumber(Nothing) Mod 2,"#c0ffc0","White") 
</BackgroundColor> 
        <TextAlign>Right</TextAlign> 
        <PaddingBottom>2pt</PaddingBottom> 
        <PaddingTop>2pt</PaddingTop> 
        <PaddingRight>2pt</PaddingRight> 
      </Style> 
      <ZIndex>4</ZIndex> 
      <rd:DefaultName>SalesOrderID</rd:DefaultName> 
      <CanGrow>true</CanGrow> 
      <Value>=Fields!SalesOrderID.Value</Value> 
    </Textbox> 
  </ReportItems> 
</TableCell>

Inserting A Page Break Following A Specific Number of Rows in A Table

You can use the Ceiling function to group the rows within a table and insert a page break at the end of each group. The Ceiling function returns the smallest number that is not less than the argument. For example, to add a page break after every 30 rows, you would use the following expression to group the rows:

=Ceiling(RowNumber(Nothing)/30)

The RDL that describes the group would resemble the following:

<TableGroups>       
   <TableGroup> 
     <Grouping Name="PageGroup"> 
       <GroupExpressions> 
          <GroupExpression>=Ceiling(RowNumber(Nothing)/30)</GroupExpression> 
        </GroupExpressions> 
      <PageBreakAtEnd>true</PageBreakAtEnd> 
    </Grouping> 
  </TableGroup> 
 </TableGroups>

Adding Global Values to Headers and Footers

The members of the Globals object collection, shown in Table 6, are available for use within report expressions.

Table 6

Name

Type

Description

PageNumber

Integer

Current page number. Available only in the page header and footer of the report.

TotalPages

Integer

Total number of pages in the report. Available only in the page header and footer of the report.

ExecutionTime

DateTime

Date and time that the report began executing.

ReportFolder

String

Path of the folder that contains the report, such as salesreports\budgeting or c:\sales\budget.

ReportName

String

Name of the report, such as currentbudget.

You can use an expression to include the name and execution time of the report in the report header or footer. The time is formatted with the .NET formatting string for short date. For example:

=Globals.ReportName & ", dated " & Format(Globals.ExecutionTime, "d")

Likewise, to put the page number and total pages in the report footer, include a text box that has the following string:

=Globals.PageNumber & " of " & Globals.TotalPages

Displaying Report Items in the Page Header

For long reports, you may want to include text from the body of the report in the header. For example, a directory listing could list the first and last occurrence of the last name field in the header to indicate the range of names included on the page.

To do this, you would include an expression using the First function in a text box in the page header. To display the first occurrence of the LastName value on the page, the expression would resemble the following:

=First (ReportItems!LastName.Value)

Likewise, use an expression with the Last function to provide the last value of the LastName text box on the page. For example:

=Last (ReportItems!LastName.Value)

Creating Drill-Down Links with Conditional Formatting

Reporting Services lets you put hidden items on a report page and make the items visible, based on the user interaction with the report.

You can use this capability to create a drill-down link within a report. For example, by clicking in a certain area, the user could drill down from a summary view of data to detailed information.

To do this, first select the group, column, or row of a table to hide and set its Hidden element to True. Next, create a toggle item for a text box in a containing group. Then, when a user clicks the text box, hidden data becomes visible or visible data disappears.

Conclusion

Microsoft SQL Server 2005 Reporting Services offers a wide range of report design options. This paper has provided general guidance and useful tips for report design, but it addressed only a small part of the product’s capabilities. For more information about design options and capabilities, see SQL Server 2005 Books Online.

For more information:

http://www.microsoft.com/technet/prodtechnol/sql/

Download

Report Design: Best Practices and Guidelines
139 KB
Microsoft Word file

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft