March 11, 2008 at 1:40 pm
One of our single page report displays 4 nicely formatted rows (summary information) with the text, amounts..etc. Recently, users started complaining that export to excel fails randomly. After researching the cause, noticed the following -
These 4 rows have drill-down capability to see the transaction details if needed. Even though I am exporting only these 4 rows to excel, if the underlaying transactions are more than 65,000, it doesn't allow export to excel. How to fix this? The users need only the summary part to export to excel. Excel 2007 removed this limitations but when I am exporting only one page report, why is it checking for the 65000 limit?
Thanks in advance
March 11, 2008 at 1:55 pm
I have always wondered the value of putting the drill-down into the Report. Shouldn't a webpage be used to drill-down into the detail and just use the report for the summary? I constantly struggle with this. I usually solve with creating 2 reports, 1 summary, 1 detail. I know this doesn't actually solve your problem, but I thought someone else might also give me a little advice on what to do.
March 11, 2008 at 4:04 pm
I think your key is - it's not exporting assuming Excel 2007. From what i remember - the documentation stated "for export into Excel 2000 or later" - meaning, it's using the Excel 2000 format (and limitations).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 4:43 pm
let me correct my last line -
Excel 2007 removed this limitations but we are still at Excel 2003. When I am exporting only one page report, why is it checking for the 65000 limit?
March 13, 2008 at 7:46 am
What I was trying to convey was - SSRS doesn't know and doesn't "care" what version of Excel you (as the reporting client) has. Since it's running in IE, it realistically wouldn't have the access level required to find out what's available on the current machine. So - it has a canned capability to export to Excel. According to the documentation - that limit is based on Excel specs from the 2000 version. Thus the 65000 or so rows. And since you had drill down turned on - it's not seeing it as a one-pager - it's seeing it as all of the detail rows...
Just because you have a brand new version of excel won't change the assumptions built into the exporter routine.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 5:48 am
Consider converting from push to pull mode.
1. Let your user's workbook's data get pulled using an external query to the database -- the workbook knows what version of Excel it is, and what its capacity is. (Although it doesn't seem to warn as well as it might when the row limit is exceeded.) But the external query will only return your handful of summary records anyway.
2. If, instead of an external query, you create a pivot table with an external source referencing your data, 65k is no longer the row limit. (There must be one, but I have never seen it documented, not have I ever hit it.) That way, the user will see both the summary totals, and be able to drill down to detail. (Beware of performance problems if you try to have too many row categories, as if you were trying to duplicate multi-column tab report.) Let that extra detail be hidden until the user requests a drill down.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply