SSRS Visual Studio 2015 Limitations

Question

I am currently using Visual Studio 2015 to export a report to Excel. The stored procedure produces the results in seconds, however returns 588,851 rows. When I try to export this, I receive a error message of; "An error occurred during local report processing. An unexpected error occurred in Report Processing. Exception of type 'System.OutOfMemoryException' was thrown." The export works in CSV format, but I would like to automate this report and I need it to use Excel format. The report is very basic, only showing the rows of data. No graphs or images. I removed timing out options and I have tried running it out of hours. There is no cache or history to the report.

Has anyone got any suggestions please?

Answer 1

Unfortunately, there are quite a few limitations to exporting data to Excel; the characters per cell being the biggest issue (32,767).

Here's some good documentation surrounding the limitations (earlier versions of SQL Server have similar limitations):

https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/exporting-to-microsoft-excel-report-builder-and-ssrs?view=sql-server-2017

I often have to add some code to SSRS to truncate any cells that exceed the character limit with something like:

=iif(Globals!RenderFormat.Name= "EXCELOPENXML" OR Globals!RenderFormat.Name = "EXCEL" OR Globals!RenderFormat.Name = "CSV",
    Left([Your Field or Parameter],32745) + "...Truncated for Excel",
[Your Field or Parameter] )