SSRS Excel Export for more than 65536 rows

When you export data in excel file through SSRS report (up to 2008 R2 ), you generally get into limitation of exporting rows less than 65536 and, if row count exceeds 65536, the report fails to generate the excel file.

Why so ?

SSRS generally renders the excel file in .xls format which has limitation of rows not exceeding more than 65536.

Solution

To overcome this multiple worksheets can be created in excel file. Each worksheet will hold up to 60K rows.

Group and Page breaks

Using groups and page breaks in SSRS report, we can restrict report to hold specific number of data in report per page. Applying page break for the group allows excel to show that many numbers of rows per work sheet.

Steps

Design the report as per the requirement.

Group Addition

Select "Parent Group" from "Add Group" section by right click on the detail row

Group Expression

Add group expression “=Ceiling((RowNumber(Nothing)) / <Expected row count>)” Put expected row count as the number of row count you want in the excel worksheet. This count should not exceed 65536.

Once the group and expression is added remove the sort expression from the row group properties.

Page break

Now report will show 60K rows per group. Next step is to add the page break. From the group properties go to page break and select the option.

Run Report

Once you export excel, you will find different worksheets created for data having rows more than 60000.

How this is handled in later versions.

The Reporting Services Excel rendering extension, new in SQL Server 2012, renders a report as an Excel document that is compatible with Microsoft Excel 2007-2010 as well as Microsoft Excel 2003 with the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint installed. The format is Office Open XML and the file extension is XLSX. This Excel-rendering extension removes limitations of the earlier version, compatible with Excel 2003. The following lists the improvement in the rendering extension:

• Maximum rows per worksheet is 1,048,576
• Maximum columns per worksheet is 16,384.

About Author

Sujit Kumbhar is technology lead in Systems Plus and is very keen to resolve challenges using his technical skills. He is important part of Systems Plus technology Think Tank. He works in Systems Plus and actively contributes to technology. He can be contacted at: sujit.ku@spluspl.com