SSRS Large Report -- Error When Exporting to Excel

0
votes

We are having an issue when exporting a large SSRS report to Excel. We receive the following error when it is run over a three-year date range. If the report is run for each of the years separately, the error does not occur.

"We found a problem with some content in 'ReportName.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes."

After clicking yes, the file opens, but it doesn't look usable.

What is the fix for this error, other than running the report for a year at time?

I have changed the report's timeout to "Do not timeout report" in Report Manager, but that has no effect.

1
excelreporting-services
How many rows of data is it trying to put in the Excel file? The limits are 1,048,576 rows by 16,384 columns - braX
What you could do is, if your report is generating more than the limits of Excel, you could add a page break after each year. Excel will create new sheets for each page. So there will be 3 sheets on Excel, one for a year - Crazy Cucumber

1 Answers

0
votes

This error can occur on reports of any size. The issue can be caused by the scale or precision of the datatypes of the output columns. I think the limit in Excel is 20 digits (plus a decimal point) so you could cast your decimal values to say decimal (14,6) and this will solve this particular issue.

You could also have issues as others have stated if you exceed the maximum rows/columns allowed in Excel. This is dependant on the version of SSRS you are using as older versions render the older Excel version files (Excel 97-2003 I think but could be wrong). later version (SSRS2012 I think) render the newer XLSX (Excel 2007 ->) format.