bad excel performance / capacity question

a web application that i have recently deployed uses reporting services and exports data to excel reports with drill down options

i have reports that are being generated up to 5 MB and are not usable in excel (i.e. scrolling freezes, slow opening times, large file size)

the reason for this is the detail displayed for each record

each record has a drop down of approximatley 4-6 rows of detail

a report with 3000 lines will have those 4-6 rows of detail for each record

shouldn't excel be able to handle this?

the reports also take a long time to generate and i have had timeout issues, i was able to set the webservice that generates the reports to have no timeout to resolve this problem, my bigger concern is why excel performs so slowly and why the file size is so large

previously we ran these reports out of access, and instead of having the reporting services format, we displayed detail information across as columns and there was no performance issue with the same amount of data

the problems seems to be with out excel handles the (+) signs

Hi,

I also have this problem and it is a big problem according to my customers - I've contacted Microsoft via an incident.

The MS's answer was : the SSRS Excel renderer is the problem and it shall be solved in the next SQL Server version (completely rewritten). Not earlier, and there is no possibility to fix it now.

The only workaround I've found:

1. the main point is to open the exported excel file for the 1.time (you have simply to wait until it opens) and then copy-paste as values the whole sheet. save the file after this, the next time you open it you will see performance improvement.

2. maybe you can split the report into smaller parts - this means less data but more reports

3. you can modify the renderer by setting OMIT_FORMULAS to true in the renderer's config file (there will be no formulas exported, all is exported as values)

The only what really helps is the point 1.

Not only the (+) sign is the problem. I have a report exporting 7000 of rows in 20 columns and there is a problem too.

Hope this helps, if someone has more ideas please let me know, I would also appreciate it.

Jan

|||I would guess that you have a significant number of merged cells in your generated report, due to alignment issues in the RDL. Excel has known issues when loading a spreadsheet with many merge cells. The Excel Export in RS uses merged cells to maintain the layout defined in RDL, and if you have many rows, this can result in large numbers of merged cells.

Check out Chris's blog entry on merging cells and how to reduce them:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx
|||

interesting...thanks for the answer

but there are no merged columns in my problematic excel file...and how is it possible that after copy-paste as values of the whole sheet becomes the opening and working with the excel file non-problematic...?