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.