Export (0) Print
Expand All

Page Layout in Excel Rendering

SQL Server 2000

A report can consist of multiple pages of data. Reports that are generated by rendering extensions that support page breaks can have page breaks at the beginning or end of a designated report item. Excel is a rendering extension that preserves layout and page breaks in an exported report. To see how to add a page break, see How to add a page break (Report Designer).

The layout of the data becomes a tabular structure, generated from the entire report, including any nested items. Cells are merged as necessary to let report items cover their allotted area.

Any blank rows or columns smaller than a user-configurable value are removed to reduce the amount of blank space in the Excel spreadsheet. This size is adjustable through the RemoveSpace device information setting; the default value is one-eighth inch. Explicit blank rows or columns in a table or matrix are not removed. For more information on the RemoveSpace setting, see Device Information Settings.

Rows and columns are sized to match the layout of the original report. If the layout has too many rows or columns for Excel, the rendering extension generates an error and the report is not processed. Overlapping elements are handled by placing them next to each other.

Document Map

A document map provide users a way to navigate to specific areas of the report. When you view the report using HTML Viewer, a document map appears along the side of the report. Clicking items in the document map refreshes the report and displays the area of the report that corresponds to the item in the document map. When you export a report containing a document map to Excel, the document map is rendered as the first worksheet, with a name of "Document Map". Each of the nodes in the document map are listed in the first column of this sheet, and each cell is indented to reflect its level in the document map. An Excel outline is also created with the same structure as the document map outline. Each document map cell links to the sheet and cell of the item that it represents. For example, if the Product Catalog is exported to Excel, the document map contains a cell with the value "Accessory". Clicking on this cell brings you to the worksheet that contains the accessories. The document map sheet can be turned off by setting the device information setting OmitDocumentMap to true. If this device information setting is not found, then a document map sheet is rendered by default. To see how the document map is rendered in Excel, the Product Catalog sample contains a document map that you can export to Excel. For more information, see Sample Report - Product Catalog.

Page Orientation

The paper height and width are compared to an internal table containing the most common paper sizes and orientations. If there is a match, that paper size is used, and the orientation is set to Portrait. If the height and width isn't found, the internal paper size table is searched again with the width and height reversed. If a match is found, then the paper size is used but the orientation set to Landscape. If still no match is found, orientation is determined by comparing the height to the width. If the paper height is greater than the width, the orientation is set to Portrait; otherwise it is set to Landscape. Columns and column spacing are ignored.

Page Headers

The page header is rendered as rows in the report. The Excel properties of Freeze Panes and Split, found in the Window menu of the worksheet, keep the page header rows visible on the screen. The property Print titles, found in the Page Setup dialog, contains values in both Rows to repeat at top and Columns to repeat at left so that those rows are repeated on each page of the report.

Page Footers

The Excel footer has a restriction of 255 characters. Excel truncates the string if it contains more than 255 characters.


All page break information is written into the worksheet for printer pagination. However, tables, matrices, and rectangles that contain other elements that have page breaks may not render properly.

See Also

Design Considerations for Rendering

Reporting Services Programming

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft