Monday 12 April 2010

SSRS Export to Excel - Performance

One fine day, we got a call from one of the users complaining that a report was not working - typical user speak. On investigating the issue, we found that it was the way in which the report was being used. We have few parameters in the report and most of the users use the filter to retrieve < 1000 rows of data. But this particular user was generating the report that returns > 30000 rows and trying to export it to Excel. Excel could not open the report and fails with error saying the sheet could be corrupt.

To start with, I found that the size of the Excel file was ~75MB. The reason was the drill-down links that is included in the report that blows up the size of report. So, I included another parameter (cannot think of a cleaner way) based on which I do (/not) provide drill-down capability in the report and hence reducing the size of the exported report. The report had come down to 7 MB now. Even this was not good enough. The report could be opened but Excel took more than 10 minutes to open the report. Even saving the report and reopening it did not help.

On closely looking at the Excel file, I saw that there were lot of merged columns in Excel. I thought it may not be related to this issue but nevertheless decided to fix it. The report layout was very simple - Page Header with few textboxes, a table with data and a Page Footer with few textboxes. The reason for the merged cell was that textboxes in header and footer was not aligned to the table columns in the report body. So, when the report is rendered using Excel renderer, the columns were merged to accommodate the differing column widths. I went ahead and managed to reduce the merged columns from around 5 to 1 (This would be much harder for Matrix reports). Surprisingly, this solved the issue - the size of the exported Excel file went down to < 4 MB and the report now opens in < 1 min.

1 comment:

  1. I had the same issue with merged cells due to textboxes in the page header, i got around this by adding

    =IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

    to the textboxes so they don't output to excel

    ReplyDelete