Report Design Best Practices

Introduction: 

The Report authors face many decisions, from what kind of data region to use to which format to use for report delivery.

Crystal Reports 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.

Report Design Concepts

This section describes the key factors that affect report design and layout.

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 sub reports.
• 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.
• Cross tabs 
  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 Index element in the report definition determines which element overlaps the other. The item with the higher Index 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 Page Height and Page Width properties for the report.
• Interactive rendering formats, such as HTML, do not render physical page breaks. Instead, use the Interactive Height andInteractive Width 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 thePageBreakAtEnd and PageBreakAtStart properties for the item.

Sub reports

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

You should take care when using sub reports for the following reasons:

• Sub reports do not share data with the parent report. The Report Server processes each instance of a sub report as a separate report and this can affect performance.  
• The headers and footers for the sub report are ignored.

Sub reports 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 sub report 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 sub reports. However, they often provide better performance, particularly if the reports share data. Data regions also work better than sub reports 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

Today()	Returns the current date.
Date Add()	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.

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.
Count Distinct	Returns a count of all distinct values from the specified expression.
Count Rows	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.
Step	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.

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 Interactive Height
Excel	Yes	No
CSV	No	No
XML	No	No
Image	Yes	Yes
PDF	Yes	Yes
GDI (Window Forms)	Yes	Uses Interactive Height

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

If you have planned 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.

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.

Following:

Adding Global Values to Headers and Footers

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

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.

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.