|Executive Summary: Business Intelligence Development Studio (BIDS) for Microsoft SQL Server 2005 lets you control several properties that affect the location, appearance, and behavior of different elements within your report. Are you using every trick in the BIDS toolbox to get the most out of your business intelligence (BI) reports?|
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.
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 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.
To understand how KeepTogether works, let’s start by looking at Figure 1, which shows two pages from a report that displays data from the sample database AdventureWorksDW rendered to PDF format. For this example, I set KeepTogether to False for the table and defined no physical page breaks. The first page renders a matrix, then a chart, which is followed by part of a table. These report elements fit within the defined report size of 8.5" × 11", less the space allocated to margins, the page header, and the page footer. The rendering engine inserted a logical page break and rendered the remainder of the table on the second page.
If you want to keep the table together on one page, one option is to define a physical page break by setting the table’s PageBreakAtStart property to True. But what if you want to keep the table on the first page if the user filters the report to include only a single category? If you move the table into a list, as I describe in the following paragraph, and set its KeepTogether value to True, you can achieve your goal. If the table doesn’t fit on the same page as the preceding items, the rendering engine pushes it to a new page, as Figure 2 shows.
However, if the table is small enough, the rendering engine keeps the table on the same page as the preceding items, as in Figure 3. In this case, if the table doesn’t fit onto its own page, the rendering engine will produce a layout similar to Figure 1 by rendering as much of the table as possible on the first page and continuing on subsequent pages.
To fit a table (or a matrix) on a single page, add a list to the report layout and drag the table into the list. Be sure to set the Location property of the table to 0,0 to eliminate white space that might prevent the table from rendering correctly on one page. Next, in the Properties pane, select the list item you just added (e.g., list1 if it’s the first list in your report). In the DatasetName drop-down list, select the data set you used to create the table. Click the Grouping property box, click the ellipsis button that appears, and then, in the first row in the Expression grid, type “=1” (without the quotation marks), as Figure 4, page 18, shows. Click OK. If necessary, resize the list to the same size as the table by clearing the Size property value and pressing Enter. The report designer will recalculate the Size property based on the list’s contents. Finally, set the list’s KeepTogether property to True.
You must render the report in a paginated format to see the result of configuring the list with KeepTogether set to True. You won’t see the effect of this property when you view the report in HTML or Excel format.
Multicolumn report. Sometimes you might want to display data in newspaper-style columns (aka snaking columns). After you define multiple columns for your report, all data regions added to the report will render in columns: There is no way to exclude some data regions from the columnar layout. Consequently, multi-column layouts are typically used with a fixed-width data region, such as a table or list.
Let’s look at the report in Figure 5, page 18, which displays an employee directory in two columns based on data from the AdventureWorksDW database. To create this layout, open the Properties pane and select Body in the report items drop-down list. Set the Columns property to 2. You can also adjust the ColumnSpacing property from its default value of 0.5 to increase or decrease the space between columns. Next, expand the Size property and change Width to a new value such as 3, to set the width of each column. When you press Enter to confirm these changes, the report designer will display a design layout for the first column and a placeholder for the second column. Add a table to this design layout and set the Location property to 0,0.
In the sample report, the table has a table header row, two detail rows per record, two columns, and no table footer row. Multiple detail rows let you present more information related to each record. To add a detail row, select the table, right-click the row handle for the detail row (the handle with three bars), and click Insert Row Above or Insert Row Below. To display the table header row at the top of each column, select the row handle for the table header, then in the Properties pane set the RepeatOnNewPage property to True. If you omit this step, the rows in each column on the first page won’t align properly across the page.
Multiple detail rows in a table might also cause alignment problems in the rendered report. For example, the employee name and phone number might be rendered in the last row of the first column and the corresponding employee title might be rendered in the first row of the second column. To avoid separating a record’s detail rows, you can define the grouping criteria for the table to keep multiple detail rows together in the same column, as Figure 5 shows. Select the table, right-click the row handle for the detail row, and click Edit Group. In the first row in the Expression grid, you can type an expression directly into the box, select a field from the data set, or select Expression to open the Edit Expression dialog box. For example, you can type the expression =Fields!LastName.Value + "," + Fields!FirstName.Value to produce a string that displays each employee name by last name followed by first name.
To view the report’s multiple columns, export it to PDF or TIFF format. The HTML and Excel versions of the report display a single column only.
Improve Report Presentation
By delving into some of the lesser-known properties of BIDS, you can put some punch into your everyday reports. With very little effort, you can design reports for online viewing, for export to a paginated format, or other uses. You’ll have to make some decisions about report layout, but now that I’ve demystified several of these properties for you, you’re ready to bring your reports to the next level.