July 13, 2010 at 1:13 pm
I am trying to export a report that is returning over 65000 records to excel. I have created a grouping Int((RowNumber(Nothing)-1)/65000) and also told it to create a new page after 65000. But the report takes too long to export and just times out. Does anyone have any ideas? Any help would be greatly appreciated.
July 13, 2010 at 1:58 pm
Seems like you're using the wrong tool for the job. More than likely you're bumping up against the timeout for either a) the Database, connection etc, b) the reportserver's rendering timeout, c) IIS's timeouts etc... Perhaps if you elaborate on the business rules/reasons you're doign this we might be able to offer a suggestion for you.
Do you just need to get the data out? will XML or a CSV do? If you so might be better servers with BCP instead of SSRS. Will you users actually take the time to analyze 65k+ rows? probably not. I'm guessing this is for an import into another system, which is why I suggested BCP. Alternatively, you could use SSIS to output your data to another datasource, Txt file or others.
-Luke.
July 13, 2010 at 2:48 pm
We have a report in place that users can run through SharePoint. When the report is ran they export the data to excel so they can do some pivoting and sorting. When they are trying to export a large amount of data it just times out and never finishes the export to excel. It take hours before it finally fails. Can this be fixed somehow?
July 14, 2010 at 6:15 am
Again, you're probably bumping up against some timeout somewhere along the lines, or perhaps even running the server/workstation out of disk space as it tries to create a really big excel document.
If all they are doing is exporting the data so they can sort/pivot/aggregate it, why not do that in the report for them? Why have the extra step of exporting all of that data just to have most of it tossed aside by aggregation?
-Luke.
July 14, 2010 at 7:42 am
I completely agree with you. I guess my next step would be to learn how to do pivoting in a report. Any ideas on where I could look?
July 14, 2010 at 7:56 am
There are a couple of ways to do it, first, you could just do the pivot in your Stored Procedure or SQL Code for your dataset. This would limit the amount of data you need to pull across the network from your database to reporting server (if they are on separate machines that is) For ideas on how to do it in TSQL Code, you can take a look at JEff's article on this topic here: http://www.sqlservercentral.com/articles/T-SQL/63681/[/url].
Alternatively, you could use a matrix (in SSRS 05) or tablix(in 08) and use column and row grouping options in much the same way you would in excel to create a pivot table. This may be a bit easier, but will probably be the slower of the two methods due to network traffic and processing time on the Report server.
-Luke.
November 7, 2012 at 2:37 pm
I'm out looking for a solution for this same issue and came across your post. Sometimes users want a simple report that they can slice and dice to thier own needs. I build reports for our finance team all the time that get turned upside down and inside out depending on what they're looking for. There shouldn't be an issue exporting to Excel. If the report didn't time out when executing, the export to Excel should have been simple.
One of the previous times we ran into this issue there was an Excel file stored in a temp directory that was locked and SSRS wouldn't export another until that file was deleted. Don't know what happened there but it's worth a shot to clean up your temp files. One of our locations has run into this again and it appears this isn't the issue this time so we'll keep looking. Multiple users can run the report and export in seconds but our tech at this site and the user he's helping both have an issue exporting. This tells me it's workstation specific.
My point is, don't give up. There's something wrong if you can't export to Excel. You'll face this again sooner or later with another report. May as well have the know how to fix it when you do. imho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply