SQL Server 2005 report design: Best Practices and guidance

This article includes tips and best practices for Microsoft®sql server™ Reporting Service report design. This article provides some basic design issues and the functionality of some report services.

About this document

From using data controls, to what formats to use, to how to distribute reports, report makers face a variety of options. Microsoft®sql Server™ Reporting Service provides rich report design and formatting support, from paper-based Media tabular reports to interactive reports with pictures and drillthrough features

This document includes guidance, advice, and techniques in report making and design. The purpose of this document is to explain general issues and provide guidance to beginners. Report Services Books Online provides rich resources for the production and design of reports, and describes the settings and available features and parameters of reports in detail.

This article is intended for beginners and some experienced report makers. Report-making beginners can also get help from the report design chapters.

Other sources of information

This article does not contain information about all of the report services. If you want detailed product information, check the product documentation and the online resources available in http://www.microsoft.com/sql/reporting/.

Product version

Although this article is based on the Microsoft T®sql server™2005 Reporting Service, information about report design applies to earlier versions of Reporting Services.

Brief introduction

Microsoft®sql Server™ Reporting Service is a complete reporting tool. In the use of report services, you can design and publish multiple styles of reports, from simple tables to online free-form forms with graphics and interactive connections. Report services can render a report as a user-specified format, including HTML, PDF, Excel, and image formats.

For a variety of settings and choices for report authors to choose Report Layout is a problem to solve. This article provides some guidelines for users to make choices that are easier and answer some common questions.

Making Tools and RDL

Report services provide a variety of ways to make reports. Both SQL Server and microsoft®visual studio®2005 include graphical report design tools. SQL Server 2005 Reporting Service provides the Report Builder tool to help business people design reports. There are other development tools that can create report formats for report services.

Regardless of which production environment we use, all report definitions are stored in Report Definition Language (RDL) format, which is a public format for XML definition reports. This allows you to write RDL directly or create a custom tool to generate the appropriate format. Report makers can use graphical production tools to edit an existing RDL file.

When a particular technique is presented in the sample, the article extracts part of the RDL that implements the feature.

http://www.microsoft.com/sql/reporting/can get the full RDL specification on the Report Services Web site.

Report Design Concepts

This section describes the key factors that affect report design and layout. If you are just contacting the reporting service, the content here will be helpful to you. All of these topics are described in detail in SQL Server Books Online.

When you design a report in a report service, you define the data and make an arrangement on the page. Although this process sounds very basic, Reporting Services provides the following features:

Supports 4 different data regions: lists, tables, matrices, and graphs.

Some column controls such as text boxes, pictures, joins, rectangles, and subreports are provided.

Users can implement interactive functions through parameters, conditional filtering, user-side sequencing, drillthrough, and document structure diagrams.

Reports can be rendered into various formats, including html,excel,pdf, images, XML, and comma delimiters (CSV).

The decisions you make about each critical area will affect the maximum display and report functionality. The following sections will provide the factors that affect the design and layout effects.

Data area

Our first choice in our report is to select the type of data area. The report service provides the following types of data regions:

Table

Tables represent data by one line at a row. The column is fixed you cannot expand, but the line can be expanded downward. Therefore, when the table grows, it expands the data downward. You can also group data in a table.

Matrix

A matrix or a crosstab table is similar to a table, but both rows and columns can be expanded and populated with data. Matrices can add additional report objects outward and downward. You can nest groups in rows and columns in a matrix.

List

The list applies to the Free form data region where the form is created. You can use nested lists to group data.

Chart

A chart is a graphical representation of the data. The report service provides a variety of chart formats.

The range of data you choose may depend on the type of data you use, how you want the report to look, and how to display the report. For example, a report that is to be presented as a microsoft®excel® is more appropriate to use a table than to use a list.

Location and size of report items

The report has 3 main area components: headers, footers, and body text. Report services support any placement of report items in a report. Data regions can be nested in parallel with other areas or by grouping.

When you design a report, it is important to understand how it is handled when the report item grows. Report items can grow horizontally or vertically, depending on repeating fragments, the size of the content, and factors such as the rendering of reports such as fonts.

When a report item grows, such as a table, it generates a number of related report items. These report items are in the same parent container. There are 2 ways to control the movement of report items:

Each report item moves down in order to maintain the minimum distance between the items in the top report.

Each report item moves to the right in order to maintain the minimum distance between the report items on the left.

If a report item grows, it expands the bounds of the container that contains it, and the container adapts to the size of the included report item.

If the report item overlaps with other report items, the ZIndex element in the report definition determines which element is at the top level. A report item with a ZIndex value is placed on the upper level.

These are the techniques you can use to control the growth and mobility of your report items. If you need to use the information for grouping rectangles and report items, refer to the "Practical Report Design Tips" section later in this article.

Paging

The key problem for the field report is how to control the segmentation of the page. The page controls the split by 2 factors:

Page size

Page segmentation before and after user-specified objects

Page size

To control the size of the page, you can set the height and Width properties of the page by following these guidelines:

The rendered physical page format can be set through the PageHeight and PageWidth properties of the report.

Interactive rendering formats such as HTML do not render physical page splits. Instead, you use the InteractiveHeight and InteractiveWidth properties to create a logical page split.

There are rendering formats, such as Excel, that do not support page size. For these reports, you will have to split the report into multiple pages using page breaks.

Note that if the report has a larger width than the page width, the report will cross multiple pages horizontally.

Page segmentation

You can add page breaks at the beginning or end of rectangles, tables, matrices, lists, graphs, and groupings. The report service attempts to keep all report items and data grouped on the same page.

You can use the PageBreakAtEnd and PageBreakAtStart properties in a report item to specify that a page break be inserted before or after a report item.

Sub-report

A subreport refers to another report in a report item. Any report can be used in a subreport, and you can set parameters that the parent report will pass to the subreport.

But when you use subreports, you should be aware of the following prerequisites:

The subreport cannot share data with the parent report. The report server processes each subreport instance for a split report, but this can affect performance.

The headers and footers of the subreport are ignored.

Sub-reports are useful in the following environments:

When you need to nest groups from different data sources in a data area.

When a report has multiple parts of a one-to-many relationship.

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

When you need to embed a standard, stand-alone report inside other reports.

If a report shares data, data areas such as tables, matrices, lists, and charts provide the same functionality as subreports. However, they provide better performance. Data regions provide a better way of working than subreports in a parallel layout.

Expressions and Functions

Report makers can use the appearance of expression data in a report, change the properties of a report item, or control the . The Tips section of this article provides examples of designs that use expressions.

You can use the Microsoft®visual basic® function in an expression. Some of the commonly used functions are shown in table 1.

Table 1

Visual Basic functions	Results
Today ()	Returns the current date.
DATEADD ()	Provides a date based on a single parameter.
Year ()	Displays the year in the specified date. Use it to group dates or labels as a series of dates.
Month ()	Display the month in a date
Format ()	format string. can be used to format days and numbers.
Right (), left (), and INSTR ()	Returns a portion of the string after the interception.
Iif	Returns 1 of 2 values according to the evaluated expression. In order to return 1 of the 3 values, you can nest the IIf function.

You can use it in an expression by referencing the appropriate assembly. NET functions. You can also use custom assembly pieces. If you need more detailed information about this, see the "Using Customer Assemblies with Reports" section of the report Services Books Online.

Aggregate functions and scopes

The report service provides many aggregate functions that you can create totals or other aggregations in a report.

Each summary function supports a range parameter that defines a rollup range. Scopes can be specified by grouping names, datasets, or data regions. The data region or grouping must contain the report items specified by the aggregate function, directly or indirectly.

If you ignore the scope, the aggregation scope is the group or the innermost grouping in the data area where the report item is located.

The aggregate functions supported by the report service are shown in table 2:

Table 2

Function	Describe
Avg	Returns the average of all non-null values from the specified expression.
Count	Returns the number of values in the specified expression.
CountDistinct	Returns the number of all different values in the specified expression.
CountRows	Returns the number of rows in the specified range.
The	Returns the first value from the specified expression.
Last	Returns the last value from the specified expression.
Max	Returns the maximum value in all Non-null values from the specified expression.
Min	Returns the minimum value from all Non-null values from the specified expression.
StDev	Returns the standard deviation in all non-null values from the specified expression.
StDevP	Returns the total standard deviation in all non-null values from the specified expression.
Sum	Returns the sum of the values from the specified expression.
Var	Returns the variance in all Non-null values from the specified expression.
VarP	Returns the population variance in all non-null values from the specified expression.

The report service provides functions to support rollup at run time. The related functions are shown in Figure 3.

Table 3

Function	Function
RowNumber	Returns the number of all rows generated at run time within the specified range. When you use a text box in a data region, displays the number of rows per text box instance in the Expression Display section. Used to specify the number of table rows or to provide page breaks. In RowNumber (Nothing), the keyword nothing specifies that the first row starts counting in the most outer data area.
RunningValue	Returns the aggregation of the runtime from the specified expression using the specified function.

The report service provides the following navigation scope functions. These functions are shown in table 4.

Table 4

Function	Describe
InScope	Determines whether the current report item instance is in the specified range. It uses the dataset, the name of the grouping or data region as a parameter and returns a Boolean value.
Level	Returns the depth of the current level in the recursive hierarchy.
Previous	Returns the previous row.

Considerations when rendering a report

The report server provides a set of torture rendering extensions. You can remove extensions from report Services or add custom extensions to support new rendering formats.

The default rendering extensions include Html,excel, comma delimiter (CSV), XML, images, and PDFs.

Note: The default rendering extension can be used in report services to render reports. Reports generated locally by using SQL Server 2005 Report Viewer controls can support HTML report rendering, but you do not need to access other extensions.

The rendering extension you choose affects the layout of the report. For example, not all render extensions support forced page separators or page delimiters applied to page size settings. Some differences are summarized in table 5.

Table 5

Render extension	Based on report items or grouped pagination	Page size
Html	OK	Using the InteractiveHeight property
Excel	OK	No
Csv	No	No
Xml	No	No
Image	OK	OK
Pdf	OK	OK
GDI (Window form)	OK	Using the InteractiveHeight property

If you know how the report will be rendered before you design the report, you can optimize the layout of the report for the target format. Otherwise, you should test your report for various possible rendering extensions and find and resolve all kinds of problems or anomalies.

The next section contains some suggestions for using Excel, images, PDFs, and HTML rendering extensions.

Excel

The report service supports rendering the report as microsoft®excel®1997 and later versions. If you plan to render the report as Excel, you should know some of the special properties of Excel rendering extensions. For example:

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

The specified sheet name is not supported by the report service.

The rendering extension builds the table structure outside the report.

Background pictures in individual cells are not supported.

Excel has nested containers outside the list.

The chart is rendered as a picture, not as a chart in Excel.

The rectangle is rendered as a cell group. If you hold another report item, the rectangle changes to a range of cells, and the rectangle's border and background color apply to the table area.

The subreport will be rendered as a rectangle in the worksheet in the half report.

Use a table in place of a list in a specified Excel report

The table uses a fixed column width. This is a good fit for the table format in Excel reports. When rendered as Excel, the report items in the report are arranged as we expect.

A list is a free form style compared to a table. The report items in the list are placed in the worksheet as if they were in the report. This can lead to unexpected results. If you use a list in your report, it is acceptable to make sure that the result is rendered to excel.

Even if you use tables, Excel rendering extensions may merge cells or introduce new columns if the header spans multiple columns in your report. This will affect the sorting and operation of the data in the Excel spreadsheet. If you plan to render the report as Excel, try to confirm that the report item arrangement minimizes the cell merge.

Maximum pages for long reports

To prevent errors from generating Excel, you should pay attention to the number of pages in a lengthy report. Each page in the report becomes a worksheet in Excel. However, in each workbook, there is a maximum number of worksheets to limit the use of memory. If the report page exceeds this limit, Excel generates an error.

Color differences when rendering to excel

Supports a set of predefined colors. When you render the report, the Excel rendering extension sets the color in the report to the closest color in Excel's support color.

Image

Image rendering extensions can render a report as a bitmap or metafile. By default, it renders the data in TIFF format. However, he can also generate any format supported by GDI +, including Bmp,emf,gif,jpeg and PNG.

Image rendering processing is performed on a virtual page in the report server, and images are created on this page. As a result, an image-based report looks the same in both font and layout on any client.

When you use TIFF files, you can view multiple page reports. However, other image formats will generate an image file for each report page.

Image rendering also supports page heights, page widths, and margins. Headers and footers that are included in any report are rendered within the report boundary.

Create the same report for all clients using image rendering

If you want to see the same report on all clients, you should use image rendering. Especially when the report is rendered, the HTML report uses the client child and browser settings. This means that the layout of the report differs when the client uses a different browser. Because image files are formatted on the report server, the images they render are displayed the same on any client.

Install the appropriate fonts on the report server

You should confirm that the correct fonts 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

A report created by a Portable Document Format (PDF) rendering extension can be opened for viewing using Adobe Acrobat readers. In many ways, a PDF extension is similar to a picture extension, except for the following important differences:

Fonts are not embedded in the report in the PDF report.

The document map is rendered as a PDF bookmark.

You can specify the width of the page, the margins, and the resolution of the PDF in the device options.

The PDF1.3 version of the file created by the render extension is compatible with the Adobe Acrobat 4.0 machine version later.

The Repeatwith property is not supported for PDF rendering extensions.

Install the appropriate fonts on the client computer

The PDF extension cannot embed fonts in the report. To properly view the font for a report, you need to confirm that the font is installed on the report server, as well as the fonts that are installed correctly in the client that is viewing the report. Another font substitution phenomenon is likely to occur.

Html

HTML extensions can generate HTML 4.0 pages that are compatible with Microsoft®internet Explorer,mozilla Firefox and Apple Safari.

HTML rendering and other rendering have the following differences:

The HTML rendering extension generates a table in HTML that contains all the report control items. The position of the control item when the report is laid out.

The position and size are expressed in millimeters (mm). The smallest unit rendered is 0.2 mm.

HTML does not support overlapping of objects. This causes the layout to be changed when the report is displayed.

In an HTML report, a subreport is rendered in a div tag.

Client changes

When displayed on the client side, the HTML report is set using the browser's attributes. In addition to the font changes, other browser settings can be done to deal with the corresponding changes. If you want to properly control the layout on all clients, consider using an image rendering extension.

Differences in different browsers

SQL Server 2005 Reporting Services includes support for different browsers such as Microsoft Internet Explorer, Mozilla Firefox, and Apple Safari. Although most of the report's features are supported on these browsers, you should also test the browser to verify that it meets your needs.

Practical Report Design Skills

This section provides tips and techniques for using expressions to skillfully handle report formatting and layout.

Use rectangles to keep objects compact

A rectangle can be used in a report service for a graphic element or a container for an object. As an object container, it can be the place where objects on one page remain at the appropriate distance and control the movement of object items.

To keep multiple objects on the same page, you can place objects in a rectangle. You add a page break around the rectangle by setting the PageBreakAtStart or PageBreakAtEnd property of the rectangle.

Use the rectangle to control the increase position of the report item move

Controls in the same rectangle are equal to each other and are arranged in a page by uniform rules when the control grows or moves. For example:

Report items are pushed and replaced by their respective rectangles.

Report items cannot be pushed or replaced with rectangular report items because they are not at one level.

If needed, the rectangle can grow to fit the contained controls.

You can take advantage of the functionality of this logic when dealing with objects that will expand. For example:

If you set aside some space for your report extension, you can stop the table and the corresponding blank space in the same rectangle. When the table grows, it is placed in a reserved blank space.

If you prevent the matrix from extending to the right edge of the page, you can put the matrix and some whitespace in the same rectangle.

Avoid blank pages

Sometimes when you output a report to a physical page such as a PDF or a printer, you may see a blank page. Typically, this occurs when the body of the report exceeds the paper size.

To make sure that all content is suitable for a single piece of paper, the text plus the margins of the page should be less than the predefined paper width. Text boxes and other report items can cause the body width to exceed the paper width, even if the contents of the outside section are not visible. In addition, the increase in the level of report items (The matrix data area and the picture automatically set to AutoSize or fit) can cause the body width to grow.

Using paging to improve the performance of large reports

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

For example, Excel does not have a default fixed page width. So, if you have a large report, Excel will try to render it as a worksheet. In general, using page breaks improves the performance of users when they access reports, because users can view the first page of a report when the rest of the report is rendered.

Using filters instead of query parameters

Report services have several ways to filter the contents of a report dynamically:

The query parameter is used to filter the data when the data source for the bundle is returned to fetch data.

A report filter applied to a dataset or report area that restricts the data that is displayed in the report.

Use a filter to get all the data, but only the data associated with the user is displayed. This may be less efficient than filtering data in the data source. However, this allows us to get only one data at a time and store the data in a snapshot to serve different user requests. In other words, when we use query parameters, each new parameter query you have to access the data source. The filter allows us to execute the snapshot and get the results of all parameters.

Add a rotation bar to a table

Maybe you need to create a report with a table or matrix with an interlaced gradient. This kind of column visually makes it easier to track different rows on one page.

To better simulate vintage green strips in a high-speed data printer that generates a large number of reports, you can set the rotation bar to green.

To accomplish this effect, you can use the IIf function to conditionally assign a background color based on the parity of the number of rows. For example:

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

The description in the RDL file should resemble the following code:

<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>

Add a page break after a specified row in a table in a report

You can use the ceiling function to group rows in a table and insert page breaks after each group.

The ceiling function returns a minimum value that is not less than the input parameter. For example, if you add a page break every 30 rows, you should use the following expressions to group:

=Ceiling(RowNumber(Nothing)/30)

The description of groupings in RDL should resemble the following description:

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

Add global variable values in headers and footers

The members of the Globals object are shown in table 6, which can be used in the expression of the report.

Table 6

Name	Type	Describe
PageNumber	Integer	The current number of pages. Used only in headers and footers in reports.
TotalPages	Integer	The total number of pages in the report. Used only in headers and footers in reports.
ExecutionTime	Datetime	Date and time when the report began to execute.
Reportfolder	String	The path to the folder that contains the report, such as Salesreportsudgeting or C:salesudget
ReportName	String	The name of the report, such as Currentbudget.

You can use an expression in the header or footer of a report to display the report name and execution time. Time passed. NET provides a short date format. For example:

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

You can also use a text box to display the current page number and total number of pages in a header:

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

Displaying report items in a header

In long reports you may want to add text in the body of the report in the header. For example, a name address is recorded in the header important display when the first and last names appear on the page.

To implement this feature, you can add a text box to the header and use the function one. To display the first occurrence of the LastName value on the page, the expression should resemble the following:

=First (ReportItems!LastName.Value)

Similarly, using the expression function can provide the last value that displays the LastName text box on the page. For example

=Last (ReportItems!LastName.Value)

To create a drillthrough connection with conditional formatting

Reporting Services can hide or display report items in a report through advocacy and reporting interaction.

You can use this feature to create a drillthrough connection in a report. For example, by clicking on an area, the user can drill through the details of the data from the summary view.

Next, create a trigger switch entry for the text box that contains the group. Then when the user clicks on the text box, the implied data becomes visible or the data is not displayed.

Conclusion

Microsoft SQL Server 2005 Reporting Service offers a variety of report design choices. This article provides some general guidelines and report design techniques, but only covers some of the functionality of the product. For additional design information and product features, refer to SQL Server 2005 Books Online.