From Report Manager you can export a report to Excel. But Excel 2003 can only handle 65,000 rows per sheet. With this little trick you can get around this problem easily.
With this simple trick you can split the dataset you want to export over several sheets, by using page breaks.
In your data region, for example a table, create a Group on the detail row. Put a page break on it and enter the expression below:
=CInt(Ceiling(RowNumber(Nothing)/30000))
After the 30,000th row a page break is placed. This automatically gives you a new sheet in Excel.
Still, exporting to Excel does not work well for large data dumps. I have a report with a huge number of rows that cannot be exported to Excel (from Report Manager). Searching the internet turns up nothing, except that it simply cannot be done. Report Manager is also not meant for data dumps, but for small reports.
{highslide type=“img” url=“artikelen/ssrs/exelexport2.jpg” width=600 captionText=‘Exporteren 65000 records’}{/highslide}
The best thing you can do is create an SSIS package and write the data to a CSV every night. Then you offer this file through Report Manager. This page describes how to do that.
Frequently asked questions
How do I export more than 65,000 rows from Report Manager to Excel 2003?
Split the data over several sheets with a page break. In your table, create a Group on the detail row and put a page break on it with an expression based on RowNumber.
Which expression puts a page break every 30,000 rows in SSRS?
Use =CInt(Ceiling(RowNumber(Nothing)/30000)). After the 30,000th row there is a page break, and that gives you a new sheet in Excel.
Why can Excel 2003 only handle 65,000 rows per sheet?
Excel 2003 supports at most 65,000 rows per sheet. An export from Report Manager runs into that limit, so you split the dataset over several sheets.
Can Report Manager export large data dumps to Excel?
Not well. A report with a huge number of rows would not export, and the internet only says that it cannot be done. Report Manager is meant for small reports, not for data dumps.
What is a better alternative for a large export from SSRS to Excel?
Create an SSIS package that writes the data to a CSV every night. Then offer that file through Report Manager.
Comments
Related blogs
How do I back up my SSRS report as RDL
SSRS Best Practice I - Limit datasets
Subtotal Bold in a Matrix