You have created report in SSRS2008R2 and your report contains more than 65,536 records. When you export the report into Excel you get Export Error message “Exceeding 65,536 rows limit”. How can we resolve this issue?
SQL Server Reporting Services allows you to export data into multiple formats (i.e. Excel, PDF, XML, etc.) and you can find all the supported formats here. SSRS 2008R2 supports the .xls file extension for Excel, but these Excel files support a maximum of 65,536 records per sheet. When you try to export a SSRS report which has more than 65,536 records it will fail to export the report into Excel.
In this tip I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report. To demonstrate the solution, I will use theAdventureworks2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.
Read complete article here.