Excel Rendering Extension Limitations

Question

I realize there is a 65,000 row limit with the Excel Rendering Extension. But I have two questions:

1.) Why wasn't the extension updated to sync with Office 2007? I can't believe the same limitation that was present in SQL Server 2000 is STILL present with the so awesome SQL Server 2008.

2.) Is there any way to get around this limitation with respect to actual report design? We have several reports that are now going to surpass 65,000 rows. Users would like to have the data in Excel in order to pivot/analyze/etc. the data. I've told users to export the report into a CSV file, but the results apparently don't import into Excel correctly.

Thanks for any assistance..

---

A. M. Robinson

Answer 1

I would create a stored procedure and let MS Excel execute it directly to pull in the infomarion.
If you send me an email I will bounce back a document I have created showning you step by step instructions on doing this.

My email address is [e-mail]

Answer 2

That's not going to work...users don't have a way to execute a stored procedure. We use SSRS so the business end can get to data without needing to execute stored procedures, know anything about databases etc.

Some of these reports take several parameters. Not only would the users know nothing about how to execute a stored procedure, they wouldn't accept that as a viable option.

For example, how would a business user in a different office, who doesn't have access to any software/tool to execute a stored procedure, execute a stored procedure. If there were a stored procedure and there were users all across the country, where would the results go?

That would be great if I were running the procedure and wanted to pipe the results to Excel, but not for end users. But I'm curious to see how you are doing this...

---

A. M. Robinson

Answer 3

Hi ansonee,

 

For your first question, good news for you, the SQL Server Reporting Services 2008 R2 has implemented the ability to export reports to Excel 2007.

Or we can use the 3^rd party Reporting Services tool “OfficeWriter”

 

For your second question, since the Reporting Services export each page into one sheet, we can add a group based on rows to the report, add a page break at end of the group, and then export the report.

To add a group based on the rows, we can use the following expression for the group:

=RowNumber(nothing)\50000

That means add a group for each 50000rows.

 

Please feel free to ask, if you have any more questions.

 

Thanks,

Jin Chen

---

Jin Chen - MSFT