Slow Report/Excel Generation compared to Query execution time

D Witherspoon

unread,

Nov 28, 2005, 11:33:57 AM11/28/05

to

I have a query that returns 71,000 rows. When I run it in query analyzer it
takes 11 seconds to execute and return all records. In reporting services I
came up with a report designed just to have the data eported to excel. It
seems to just take forever and then time out.

Has anyone experienced this?

Bruce L-C [MVP]

unread,

Nov 28, 2005, 11:54:00 AM11/28/05

to

Excel export is very slow (as well as PDF). MS is going for fidelity of the
look of the report. What you want is more a data export to Excel. I do a lot
of this for my users. CSV is much much faster but in RS 2000 you need to do
a workaround.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you can configure it to use ASCII instead of
Unicode.

Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:

="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"

If you don't want to have it appear in a new window then do this in jump to
URL:

=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"

Very nice and very fast.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"D Witherspoon" <dwithe...@noway.org> wrote in message
news:%23H%233ymD9...@TK2MSFTNGP14.phx.gbl...

Kali Charan Tripathi

unread,

Jan 12, 2010, 11:32:32 PM1/12/10

to

Hi All,

I am using given below script for rendering approx 85000 data into the csv format but it is exporting only 65,555 data into csv.
I mean am putting &rs:Format=CSV&rc:Encoding=ASCII on my URL.

How to export all 85000 data into the CSV files.

="javascript:void(window.open('http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fKonsolidata_Data_Exporting_Project%2fTest_EXPORT_REPORT_TEST&rs:Command=Render&RP_cntry="+Fields!STD_CTRY_NM.Value+"&M_DATE="+Fields!Mon_YY.Value+"&RP_STTS="+Join(Parameters!RP_cll_stts.Value,",")+"&RP_CT1="+Join(Parameters!RP_cll_typ_l1.Value,",")+"&RP_CT2="+Join(Parameters!RP_cll_typ_l2.Value,",")+"&RP_CS1="+Join(Parameters!RP_cust_clssfctn_l1.Value,",")+"&RP_CS2="+Join(Parameters!RP_cust_clssfctn_l2.Value,",")+"&RP_PS1="+Join(Parameters!RP_prdct_sgmnt_l1.Value,",")+"&RP_PS2="+Join(Parameters!RP_prdct_sgmnt_l2.Value,",")+"&rs:Format=CSV&rc:Encoding=ASCII'))"

From http://www.developmentnow.com/g/115_2005_11_0_0_646626/Slow-ReportExcel-Generation-compared-to-Query-execution-time.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

Bruce L-C [MVP]

unread,

Jan 13, 2010, 9:16:33 AM1/13/10

to

Are you sure the CSV isn't really there? My guess is the CSV is OK but that
when it opens excel that the version of Excel (2003 or earlier) only allows
65,555 rows of data. I don't think that CSV export is limited.

What are you using to determine the number or rows in the csv file? What
version of Excel are you using?

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Kali Charan Tripathi" <tripat...@yahoo.co.in> wrote in message
news:ff84f052-7d41-47f3...@developmentnow.com...