Error in exporting ssrs 2008 report to excel

Question

I have a report returning around 90000 rows. But when i export it to excel from the asp.net page it gives this error:
An error occurred during rendering of the report
But the same report is getting successfully exported to excel from the report server.
If i pass some more parameters from the web page to reduce the number of records to 100, then i am able to export it to excel from the web page itself.

Edit-1
Stack Trace: [Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors]

[Exception: An error occurred during rendering of the report.] Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +574 Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +905 Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +28 Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +88 Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +123 Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +585 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +901 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +76

Answer 1

I can suggest you some workaround: in the report you can place a link to Force SSRS Report to create a file rather than render on screen.

the link should contain the following:

http://localhost/ReportServer/Pages/ReportViewer.aspx?
%2fSample+Report&rs:Format=excel

Answer 2

SSRS 2008 can only export to XLS (MS Office 2003). XLS files are limited to 65k rows.

Have you looked at the row count of the export from the Report Manager? Unless you are using a custom or third-party tool to allow export to XLSX, the file should only have ~65k rows.

I think the Report Manager works around the exceeded row length by performing what is essentially something like SELECT TOP 65k from the report. Your asp.net interface probably does not account for this limitation, and is forcing too many rows into the Excel export.

Your options are:

1.) Use SSRS 2012 which allows export to XLSX. XLSX files can handle many more rows.

2.) Change your asp.net code to slice off the top 65k rows before export to XLS.

3.) Consider a third-party or custom solution to work around the SSRS 2008 export limitations (I know of Aspose.)

4.) Export to CSV, then have the end user convert the CSV into Excel.

Answer 3

As dev_etter mentioned, SSRS 2008 doesn't support such large Excel reports out of the box but it can be done with 3rd party tools like SoftArtisans OfficeWriter which allows you to design and export reports in the OOXML (.xlsx/.xlsm) format.

Disclaimer: I work for SoftArtisans

Answer 4

Maybe there is timeout error in ReportViewer control? Try to play with:

ViewerControl.ServerReport.Timeout

http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.serverreport.timeout(v=vs.80).aspx Because report server also use ReportViewer control and maybe with different configaration with yours. The default value for timeout is 600000 milliseconds.

BTW: It would be great to see your StackTrace. This will help to give you better answer.

Answer 5

Ram on the server may be an issue. There are a lot of issues here with high volume reports not coming through and ram seems to fix them, from the responses at least.

Answer 6

if the problem is exceeding the 65k limit of excel, you can add a parent group to your tablix and add the following code for the group expression

=CEILING(RowNumber(Nothing)/65500)

Then add a page break between each instance of the group...

this will create multiple tabs in the excel file for the tablix that is > 65500 rows...

Answer 7

My fix turned out to be removing the seal(image) on our report. I'm not sure if this is limited to png's or not but after it was removed the reports exported to excel without issue.

Answer 8

I had this problem when exporting an Oracle SSRS report with an hierarchical connect by type query, in SSRS 2014. The report was displaying, but faster than I expected. I was wondering if it was timing out in the background, but still displaying some of the rows. I changed the report to never timeout and that fixed it. Took longer to run after that, but it would export to excel after it finished.