Writing Multi-Column Reports

You can design a report that uses a multi-column layout, similar to a traditional newspaper column where data flows down multiple adjacent columns. A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report. When you specify a multi-column layout, the report server creates each column as a series of very narrow pages that are rendered in close sequence, giving the appearance of multiple columns. Properties that you set at the page level are applied to each column in the report. You can define as many columns you want.

For best results, use data regions that provide repeating rows of data (for example, table or list box). A list box placed within a multi-column report will display data from the top left of the page to the bottom left of the page, and then continue the list in the adjacent column at the top of the page. If you want to use text boxes or images, put them in a list so that they repeat in each column.

If you are accustomed to using subreports to embed a separate report within a parent report, be aware that you cannot use subreports to get the same outcome in a multi-column layout. In a multi-column report, a subreport inherits the column settings of the parent report. This means that if you define a multi-column layout on a subreport, the subreport ignores the column settings that are specified for it. It also means that you cannot use subreports to create a free-form or single column layout within the multi-column report. Subreports that you include in a multi-column report always use the column settings of the parent report.

To limit the amount of data that is contained within each column, define a group expression and then set a page break on the group. For example, you might add an expression that limits the number of rows per column. You can use the example provided further on in this topic to see how this is done.

Not all rendering extensions support a multi-column layout. You can use the following rendering extensions to view a multi-column:

• PDF
• TIFF

You cannot view a multi-column report in Report Designer preview and in most rendering extensions. In both preview and the rendering extensions that produce HTML, Web archive, Excel, CSV, and XML output, a multi-column layout is displayed as a single column.

Viewing a Multi-column Report

To preview or view a multi-column report, you must export the report as a PDF or IMAGE file, and then open the file from the file system. Because multi-column report layout is not supported in HTML, you might want to avoid running the report from Report Manager or in SharePoint Web parts that use HTML as the default output format. For best results, consider the following alternate deployment options for multi-column reports:

• Create subscriptions that deliver reports as .pdf or .tiff files to a shared folder location.
• Use URL access to deploy the report. You can specify PDF or IMAGE rendering extension on the report URL, ensuring that the report always opens in an output format that supports multiple columns.

To hide reports so that they are not visible in report viewing tools, publish the reports to a report server folder, and then create role assignments that exclude View folder access to that folder. For more information about folder security and role assignments, see Securing Folders and Creating, Modifying, and Deleting Role Assignments.

Example: Designing a Multi-column Report

You can use the following example to learn how to create a multi-column report. This example assumes that you already know how to create a report, define a dataset, and create a basic report layout. If you are not sure how to create a report and a dataset, see Tutorial: Creating a Basic Report.

1. Create a report and a dataset that returns two fields for each row. For the purposes of this example, you can use the following query to retrieve two fields from the AdventureWorks sample database:

   Select distinct Lastname, Firstname from Person.Contact Order by Lastname
   

2. Set report properties to specify the number of columns, margins, and padding. In this example, specify 2 columns for the report. To view instructions about adding columns to a report, see How to: Specify Multiple Columns in a Report (Report Designer).

3. From the Toolbox, drag a List onto the report design surface.

4. Position the List so that it is at the top of the report. This removes extra whitespace from the report and ensures that both columns on the page are aligned with each other when the report is rendered.

5. Drag a Table onto the List. A Table is useful because it provides a uniform arrangement for fields in repeating rows of data, but you can use any kind of data region that gives you the report layout you are looking for.

6. Drag the Lastname and Firstname fields from the dataset onto the Details row in the table.

7. Delete the Footer row and the third column.

8. Resize the page layout to remove extra whitespace and to bring the second column into view. The report layout should look similar to the following image:

   

   When you define more than one column in a report, Report Designer calculates the width of the columns in the report, the width of the report, and the width of the space between columns based on the number of columns. It then displays a decreased design surface so that you can place report items on the report that will fit within the column. Note that the layout of the entire report must be placed on the decreased design surface. Additional columns are displayed so that you can verify that the number of columns you defined will fit the page size dimension. You can adjust page size, padding, and margins to fit more columns on the page.

9. Create a group expression on the List and specify page breaks to force data onto the next column.

   1. Right-click the List and select Properties.

   2. In List Properties, on the General tab, click Edit Details Group.

   3. In the Grouping and Sorting Properties dialog box, on the General tab, type an expression to limit the number of rows that appear in each column. In this expression, twenty rows are allowed in each column:

      =Ceiling(RowNumber(Nothing)/20)
      

   4. Click Page break at end.

   5. Click OK to close the Grouping and Sorting Properties dialog box. Click OK again to close the List Properties dialog box.

10. Preview the report, and then export it to PDF or IMAGE output.

When designing a multi-column report, you can export the report to a .pdf or .tiff file in Report Designer's preview mode by selecting the output format you want from the Export options on the preview toolbar. Note that while the report is open in preview mode, the multi-column layout will not be visible.

See Also

Tasks

How to: Add a Group to a List (Report Designer)
How to: Add, Move, or Delete a List (Report Designer)

Concepts

Exporting Reports
Previewing Reports
Creating, Modifying, and Deleting Subscriptions
Designing the Report Layout

Other Resources

Running Setup to Install AdventureWorks Sample Databases and Samples
URL Access

Help and Information

Getting SQL Server 2005 Assistance

Change History