Tuesday, March 27, 2012

Excel Export Limit?

hi,
I'm having issues exporting "large" Reports into Excel.
These reports create 11 separate worksheets with most not going over 7-10K
rows on each sheet.
I have been able to export "smaller" reports that have the same 11 Excel
sheets but perhaps not as as many rows (1-2k a piece).
When I try to export the large reports it ends up toasting the entire RS
site, and i'm not able to get back into RS until it recycles itself 10-15
minutes.
The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
it.
Is this just a RS2K issue? Would upgrading to RS05 fix it?
Thank you for your time
tOn Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
wrote:
> hi,
> I'm having issues exporting "large" Reports into Excel.
> These reports create 11 separate worksheets with most not going over 7-10K
> rows on each sheet.
> I have been able to export "smaller" reports that have the same 11 Excel
> sheets but perhaps not as as many rows (1-2k a piece).
> When I try to export the large reports it ends up toasting the entire RS
> site, and i'm not able to get back into RS until it recycles itself 10-15
> minutes.
> The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
> it.
> Is this just a RS2K issue? Would upgrading to RS05 fix it?
> Thank you for your time
> t
I think the issue is more related to Excel than SSRS. I know that
Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
version. I don't realistically think that the SSRS version will make
much difference. Of course, Excel 2007 supposedly has an unlimited row
capacity. If this is very important and money is not an object, I
would suggest upgrading the OS to Vista, purchase Office 2007 and then
you would need to purchase SQL Server 2005 and install SP2 to run on
Vista. Again, this might be overkill for your situation. That you will
have to decide. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for your response Enrique,
however ... like i said the largest worksheet is less than 10K rows, and it
never even gets to the Excel portion. When i hit export it sits for 5-10
minutes then returns a general IIS error. At that point the RS site is down.
I'm just trying to determine if it's an RS issue, a RAM/Environment issue,
or ...? I know somewhere something is having an issue with the size since
i'm able to export a similar smaller report. Although the smaller report
still ends up taking 10-15 minutes to export.
Not sure where to start looking.
Any other thoughts?
thank you again
t
"EMartinez" wrote:
> On Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
> wrote:
> > hi,
> > I'm having issues exporting "large" Reports into Excel.
> >
> > These reports create 11 separate worksheets with most not going over 7-10K
> > rows on each sheet.
> >
> > I have been able to export "smaller" reports that have the same 11 Excel
> > sheets but perhaps not as as many rows (1-2k a piece).
> >
> > When I try to export the large reports it ends up toasting the entire RS
> > site, and i'm not able to get back into RS until it recycles itself 10-15
> > minutes.
> >
> > The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
> > it.
> >
> > Is this just a RS2K issue? Would upgrading to RS05 fix it?
> >
> > Thank you for your time
> > t
>
> I think the issue is more related to Excel than SSRS. I know that
> Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
> version. I don't realistically think that the SSRS version will make
> much difference. Of course, Excel 2007 supposedly has an unlimited row
> capacity. If this is very important and money is not an object, I
> would suggest upgrading the OS to Vista, purchase Office 2007 and then
> you would need to purchase SQL Server 2005 and install SP2 to run on
> Vista. Again, this might be overkill for your situation. That you will
> have to decide. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS 2005 does render Excel better. With 2000 I would have issues when a user
would export a large amount of data to Excel. It would freeze up the server.
At one point to get around this I would provide a link to CSV ascii format
and have them use that instead of exporting to Excel. With RS 2005 I no
longer have to do that. It is extremely rare that I have an issue with the
server now.
I have a user that would export frequently to Excel (and he is the reason I
came up with the CSV workaround). He does not need the workaround any more.
Based on my experience I would say you have a high probability of fixing the
problem if you upgrade. Not only did my server stop freezing but I saw a
pretty significant speedup with pdf and excel export.
Other benefits of upgrading: end user sorting, multi-select parameters, date
picker. I highly recommend upgrading.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DigHazuse" <DigHazuse@.discussions.microsoft.com> wrote in message
news:2E4EF2CD-5DCD-40D2-BD99-FDA5BD5CFCF0@.microsoft.com...
> Thank you for your response Enrique,
> however ... like i said the largest worksheet is less than 10K rows, and
> it
> never even gets to the Excel portion. When i hit export it sits for 5-10
> minutes then returns a general IIS error. At that point the RS site is
> down.
> I'm just trying to determine if it's an RS issue, a RAM/Environment issue,
> or ...? I know somewhere something is having an issue with the size since
> i'm able to export a similar smaller report. Although the smaller report
> still ends up taking 10-15 minutes to export.
> Not sure where to start looking.
> Any other thoughts?
> thank you again
> t
>
> "EMartinez" wrote:
>> On Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
>> wrote:
>> > hi,
>> > I'm having issues exporting "large" Reports into Excel.
>> >
>> > These reports create 11 separate worksheets with most not going over
>> > 7-10K
>> > rows on each sheet.
>> >
>> > I have been able to export "smaller" reports that have the same 11
>> > Excel
>> > sheets but perhaps not as as many rows (1-2k a piece).
>> >
>> > When I try to export the large reports it ends up toasting the entire
>> > RS
>> > site, and i'm not able to get back into RS until it recycles itself
>> > 10-15
>> > minutes.
>> >
>> > The RS server is a Win2K3 with 4GB ram and RS2K is the only thing
>> > running on
>> > it.
>> >
>> > Is this just a RS2K issue? Would upgrading to RS05 fix it?
>> >
>> > Thank you for your time
>> > t
>>
>> I think the issue is more related to Excel than SSRS. I know that
>> Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
>> version. I don't realistically think that the SSRS version will make
>> much difference. Of course, Excel 2007 supposedly has an unlimited row
>> capacity. If this is very important and money is not an object, I
>> would suggest upgrading the OS to Vista, purchase Office 2007 and then
>> you would need to purchase SQL Server 2005 and install SP2 to run on
>> Vista. Again, this might be overkill for your situation. That you will
>> have to decide. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>

No comments:

Post a Comment