Work around the Excel 2003 row limitation

This article explains how to work around the Excel 2003 row limitation when you export paginated reports to Excel. The workaround is for a report that contains only a table.

Excel 2003 supports a maximum of 65,536 rows per worksheet. You can work around this limitation by forcing an explicit page break after some rows. The Excel renderer creates a new worksheet for each explicit page break.

Create an explicit page break

1. Open the report in SQL Server Data Tools - Business Intelligence or the Reporting Services web portal.

2. Right-click the Data row in the table, and then select Add Group. Choose Parent Group to add an outer table group.

   

3. Enter the following formula in the Group by expression box, and then select OK to add the parent group.

   =Int((RowNumber(Nothing)-1)/65000)

   The formula assigns a number to each set of 65,000 rows in the dataset. When a page break is defined for the group, the expression results in a page break every 65,000 rows.

   Adding the outer table group adds a group column to the report.

4. Delete the group column by right-clicking on the column header, selecting Delete Columns, choosing Delete columns only, and then selecting OK.

   

5. Right-click Group 1 in the Row Groups section, and then select Group Properties.

   

6. On the Sorting page of the Group Properties dialog, select the default sorting option and select Delete.

   

7. On the Page Breaks page, choose Between each instance of a group and then select OK.

   

8. Save the report. When you export it to Excel, it exports to multiple worksheets and each worksheet contains a maximum of 65,000 rows.