SSRS: Export to Excel 2007-2010 Format

On versions previous to Reporting Services 2012 (even Reporting Services 2008 R2), you could only export a maximum of 65536 rows when you chose to export the data in a report to Excel.     If this is a showstopper, then Reporting Services 2012 is your saviour.   However if you are using Reporting Services 2008 R2 you also have another option before you consider going down this path.  Anything 2008 or earlier, you are really out of luck, or perhaps you could aggregate the data in your report before exporting to reduce the number of rows.

First, the new Reporting Services 2012 Excel rendering extension, renders a report as an Excel document that is compatible with Microsoft Excel 2007-2010 but also provides Excel 2003 support. This new Excel-rendering extension removes limitations of the earlier version, meaning the amount of rows you can export lifts from 65536 to a maximum of 1,048,576 rows per worksheet – Amazeballs!   Other improvements are introduced as follows :

  • Maximum columns per worksheet is 16,384
  • Number of colors allowed in a worksheet is approximately 16 million (24-bit color)
  • ZIP compression provides smaller files sizes
  • Support for Microsoft Word .docx

Read more here – If you really can’t upgrade to Reporting Services 2012 just now but you are running Reporting Services 2008 R2, then you can export more than 65536 rows by setting a page break on your report and splitting your data into groups, 1 worksheet per group.  The one catch is that each group needs to be less than 65536 rows per group (per worksheet). To get this happening, you need to edit your original report, then setup your page break in your report Tablix and set the PageName property to an expression that identifies how you will split or group the data.   An example expression shows how to group by the data in your ProductCategory field :

"Product Category : " & Fields!ProductCategory.Value

Read more here