SQL Server Magazine

The report design environment in Business Intelligence Development Studio (BIDS) for SQL Server 2005 provides properties that let you control the location, appearance, and behavior of each element of your report. But the sheer number of properties can overwhelm new report developers. Although most users quickly become familiar with the properties they need to produce basic reports, the purpose of many of the remaining properties remains a mystery. As a result, report developers typically rely on the subset of properties they understand to try to produce specialized reports, often with less-than-stellar results. So here’s an opportunity to improve your report-designing expertise by learning about properties that automatically refresh and paginate onscreen reports, keep data regions together on a page when possible, and produce multicolumn reports.

Online Reports
When you design a report that will be viewed primarily online, you should consider whether to periodically refresh the report on screen, whether to display the report as a single page, and whether to repeat items on each page for reports with multiple pages. To achieve those effects, use the AutoRefresh, InteractiveHeight, and RepeatWith properties.

Automatic refresh. For a report that a user will open once and manually refresh frequently throughout the day as the source data changes (e.g., an operational dashboard), you can configure an automatic refresh interval. By default, the AutoRefresh property is set to 0, which disables the automatic refresh. To set a refresh interval for a report, open the report’s Properties pane in BIDS (if the Properties pane is not visible, press F4). Select Report in the report item drop-down list, and in the AutoRefresh property box, type a positive integer value to indicate the number of seconds between refreshes.

If the report is designed to toggle the visibility of items, those items will return to their original state when the report refreshes. For example, if you have a row hidden when the document opens, and the user clicks an item to show the hidden row, the row will revert to its hidden state each time the report refreshes. Consequently, you should consider limiting the use of this feature to reports that display all content on a single page without toggling visibility.

Online pagination. The InteractiveHeight property of the Report item controls the length of a report when viewed as HTML. If your regional setting is U.S., this property defaults to a value of 11", which results in online pagination of a report when it exceeds a length of approximately 11" on your screen and doesn’t contain explicit page breaks. When a report contains many rows of data, this logical pagination improves performance because the first page is rendered immediately for viewing, while the remaining pages render in the background.

Whether a user has to scroll to view the full length of the page depends on the user’s screen size and resolution and the size of the browser window in which the report is viewed. If you want to minimize vertical scrolling, you can reduce the InteractiveHeight value, but doing so also increases the number of pages in the report. Be aware that you might experience inconsistent online page lengths when you have one or more groups defined in a data region, so be sure to test reports to ensure the maximum page length within the report meets your vertical height requirements.

Alternatively, you might want to eliminate paging altogether and display the entire report on a single page. For this scenario, simply change the InteractiveHeight value to 0. Rendering a large report as a single page might take longer than a paginated report, so be sure to set user expectations accordingly.

To change the value of the InteractiveHeight property, open the Properties pane and select Report in the drop-down list at the top of the pane. Locate the InteractiveSize property, click the plus sign to expand the property, and type a new value in the Height property box. Although the property field is labeled Height, the report definition language (RDL) refers to it as InteractiveHeight. When specifying a value, you can also use units of measurement such as cm for centimeters, mm for millimeters, pt for points, or pc for picas. You might also notice an InteractiveWidth property and be tempted to experiment with it. However, the rendering engine currently ignores this property, and changing it has no effect.

Repeated item with a data region. Suppose you have a report that contains a table that spans multiple pages when viewed on screen. You probably already know that you can set the RepeatOnNewPage property to include the table header or table footer on each page on which the table displays, but what if you also want to include other items on each page alongside the table? Let’s say you want to repeat a text box to the right of a table on all pages, for example, but only on the pages containing the table.

You can use the RepeatWith property of three types of report items: text box, rectangle, and line. A valid value for the RepeatWith property is the name of a data region—table, matrix, list, or chart—on the report that shares the same parent as the item to be repeated. For example, if you have a table in a report and add a text box next to the table, both the table and the text box have the same parent: Body. You won’t see the text box repeated with the table in Preview mode within BIDS, so you must deploy the report to the report server to test the results. If you specify the RepeatWith property for a rectangle that contains a data region, deployment of the report will fail. By the way, this property also works for reports that you export to PDF.

Paginated Reports
Paginated reports are reports exported to PDF or TIFF format. When you know a report will usually be exported into one of these formats, you’ll want to think about whether to allow a data region that could fit on one page to span two pages or whether to render data in multiple columns.

Single-page data region. When you design reports for printing, you can define explicit (physical) page breaks by setting page breaks for a data region or for groups within the data region. In addition to physical page breaks, the rendering engine will add a logical page break when the data between physical page breaks can’t fit within the available page space. SQL Server 2005 Books Online (msdn2.microsoft.com/en-us/library/ms130214.aspx) explains that under certain conditions, you can set the KeepTogether property of a table, matrix, or list to True to change the placement of logical page breaks. However, my testing shows that the Keep-Together property currently works only with a list.

   Prev. page   [1] 2     next page