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 and Interactive 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 the
PageBreakAtEnd 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
|
Returns the average of all non-null values from the specified
expression.
|
|
Returns a count of the values from the specified expression.
|
|
Returns a count of all distinct values from the specified
expression.
|
|
Returns a count of rows within the specified scope.
|
|
Returns the first value from the specified expression.
|
|
Returns the last value from the specified expression.
|
|
Returns the maximum value from all non-null values of the
specified expression.
|
|
Returns the minimum value from all non-null values of the
specified expression.
|
|
Returns the standard deviation of all non-null values of the
specified expression.
|
|
Returns the population standard deviation of all non-null values
of the specified expression.
|
|
Returns a sum of the values of the specified expression.
|
|
Returns the variance of all non-null values of the specified
expression.
|
|
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
|
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).
|
|
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.
