exporting large amounts of records (50K rows) to excel - painfully slow

We're trying to do an export to excel of a large amount of data.
When we do 100 even 4000 rows it happens in a decent amount of time.
However, when we try to export say 35,000 rows it never finishes and
appears to hang after a long amount of time (15-20 minutes).
Does anyone have any tips for optimizing or troubleshooting this issue.
We're using SQL Server 2000 and Reporting Services 2000.
Thanks,
JasonI use Access Data Projects for real enterprise level reporting; all the
time-- and I push out this many records without a problem.
Maybe you should consider having a better strategy; and choosing real
tools-- like Access Data Projects... instead of SSRS; where version
2005 is STILL considered a beta.
-Aaron
jason.harris@.gmail.com wrote:
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason|||RS 2005 is faster but the following technique will work. You need to export
in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
handle. In RS 2005 you can configure RS to export as ASCII. What I do is
have a link they click on and use the following URL to export in CSV. Order
of magnitudes faster.
="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
here" &casesensitiveparamname=" & Parameters!Paramname.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
When you play with this you might decide to design a report just for
exporting.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jason.harris@.gmail.com> wrote in message
news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> We're trying to do an export to excel of a large amount of data.
> When we do 100 even 4000 rows it happens in a decent amount of time.
> However, when we try to export say 35,000 rows it never finishes and
> appears to hang after a long amount of time (15-20 minutes).
>
> Does anyone have any tips for optimizing or troubleshooting this issue.
> We're using SQL Server 2000 and Reporting Services 2000.
> Thanks,
> Jason
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> RS 2005 is faster but the following technique will work. You need to
> export in CSV. However, with RS 2000 it exports it as Unicode which
> Excel can't handle. In RS 2005 you can configure RS to export as
> ASCII. What I do is have a link they click on and use the following
> URL to export in CSV. Order of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
It's also useful to remember that RS is a tool to render usable reports to
view in a browser. It also has the option to export those reports. It was
never designed to be used as a data extractor|||Bruce
I have a similar problem but how do I put this URL into the RS Folder.
Thanks
Karen
Bruce L-C [MVP] wrote:
> RS 2005 is faster but the following technique will work. You need to export
> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
> have a link they click on and use the following URL to export in CSV. Order
> of magnitudes faster.
> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to report
> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> When you play with this you might decide to design a report just for
> exporting.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <jason.harris@.gmail.com> wrote in message
> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
> > We're trying to do an export to excel of a large amount of data.
> >
> > When we do 100 even 4000 rows it happens in a decent amount of time.
> >
> > However, when we try to export say 35,000 rows it never finishes and
> > appears to hang after a long amount of time (15-20 minutes).
> >
> >
> > Does anyone have any tips for optimizing or troubleshooting this issue.
> >
> > We're using SQL Server 2000 and Reporting Services 2000.
> >
> > Thanks,
> > Jason
> >|||it's alst important to note that since it's EXCEL we're talking about;
you're gonna have a half dozen ninja spreadsheet FAGS that try to build
ETL tools out of Excel.
In the real world; people use Excel as a word processor and a ETL tool;
just because the idiots dont know any other tools.
is it their fault? no it is the fault of the managers and teachers that
make Excel an expectation.
I would just reccomend shooting anyone that uses Excel for anything.
and then uninstalling Excel from every machine in the house.
Asher_N wrote:
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in
> news:OHYG1F$4GHA.1196@.TK2MSFTNGP02.phx.gbl:
> > RS 2005 is faster but the following technique will work. You need to
> > export in CSV. However, with RS 2000 it exports it as Unicode which
> > Excel can't handle. In RS 2005 you can configure RS to export as
> > ASCII. What I do is have a link they click on and use the following
> > URL to export in CSV. Order of magnitudes faster.
> >
> > ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
> > report here" &casesensitiveparamname=" & Parameters!Paramname.Value &
> > "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >
> > When you play with this you might decide to design a report just for
> > exporting.
> >
> >
> It's also useful to remember that RS is a tool to render usable reports to
> view in a browser. It also has the option to export those reports. It was
> never designed to be used as a data extractor|||This is for using the Jump To URL. If you wanted a report that just
consisted of this then you would have a report with textboxes. Properties of
the textbox, navigation, jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1160044319.003258.210730@.b28g2000cwb.googlegroups.com...
> Bruce
> I have a similar problem but how do I put this URL into the RS Folder.
> Thanks
> Karen
> Bruce L-C [MVP] wrote:
>> RS 2005 is faster but the following technique will work. You need to
>> export
>> in CSV. However, with RS 2000 it exports it as Unicode which Excel can't
>> handle. In RS 2005 you can configure RS to export as ASCII. What I do is
>> have a link they click on and use the following URL to export in CSV.
>> Order
>> of magnitudes faster.
>> ="javascript:void(window.open('" & Globals!ReportServerUrl & "path to
>> report
>> here" &casesensitiveparamname=" & Parameters!Paramname.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> When you play with this you might decide to design a report just for
>> exporting.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <jason.harris@.gmail.com> wrote in message
>> news:1159551507.554931.28710@.m7g2000cwm.googlegroups.com...
>> > We're trying to do an export to excel of a large amount of data.
>> >
>> > When we do 100 even 4000 rows it happens in a decent amount of time.
>> >
>> > However, when we try to export say 35,000 rows it never finishes and
>> > appears to hang after a long amount of time (15-20 minutes).
>> >
>> >
>> > Does anyone have any tips for optimizing or troubleshooting this issue.
>> >
>> > We're using SQL Server 2000 and Reporting Services 2000.
>> >
>> > Thanks,
>> > Jason
>> >
>

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.

Export to Excel Custom Format

Hi,

I have a problem when exporting a report to Excel.

The problem is with the custom formatting. The report has a field named amount with its format property = C (on the properties window of the textbox in the report designer). When the user exports the report everything seems ok, calculations and so on... but the problem is when from another workbook a cell makes a reference to the cell amount of the exported report. The exported report, has this format [$-1010409]$#,##0.00;($#,##0.00) on the amount cell. In fact every format type of the report designer, begins with [$-1010409].

To reproduce this error:

Make a simple rdl with a textbox format C. Export it to excel. Create a new workbook and make a cell reference to the exported report formated textbox cell (='\\Computer\Folder[ExportedReport.xls]Sheet1'!$E$15). Close the exported report and the new workbook, open the new workbook (not the exported one) and update the reference. Results in a #Ref error.

Tnx of your time and effort.

Sorry for my bad english.

G

Hi,

Did you get a solution to your problem? I am having a similar problem: I would like to convert a value in the [$-1010409]Standard format into an integer. Do you know how that can be done? I am using VSTO.

Thanks in advance,

Pedro

|||

Hi Pedro,

No, I never got the solution for that problem. Sorry

I dont need the solution anymore because Im using SSAS (Cubes in Analisis Service) and Excel 2007 (pivot tables). Not using SSRS.