Exporting of Large Reports (Report Manager)

How can I increase the performance when attempting to export large reports to
an MS Excel or PDF file?
The report currently has an average of 29,995 records and is approximately
19,398,584 bytes in size. (650+ pages)
However, when rendering the report, the report takes only 30+ seconds.
http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
2fMember_Verification%2fReports%2fIPC_Members_Verification
Thank you in advance for your support.If it takes 30 seconds to html then it will take as much as 10 times longer
to Excel or PDF. For Excel there is a work around.
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 (RS 2000) 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 be able to configure it to use
ASCII instead of Unicode when exporting to CSV.
So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
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. CSV ASCII will be just as fast as HTML.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> How can I increase the performance when attempting to export large reports
> to
> an MS Excel or PDF file?
> The report currently has an average of 29,995 records and is approximately
> 19,398,584 bytes in size. (650+ pages)
> However, when rendering the report, the report takes only 30+ seconds.
> http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> 2fMember_Verification%2fReports%2fIPC_Members_Verification
> Thank you in advance for your support.|||The solution worked! Thank you very much.
However, is there a way of scheduling the report using the same solution?
"Bruce L-C [MVP]" wrote:
> If it takes 30 seconds to html then it will take as much as 10 times longer
> to Excel or PDF. For Excel there is a work around.
> 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 (RS 2000) 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 be able to configure it to use
> ASCII instead of Unicode when exporting to CSV.
> So, in RS 2000 I add a link at the top of the page and then use Jump to URL.
> 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. CSV ASCII will be just as fast as HTML.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
> > How can I increase the performance when attempting to export large reports
> > to
> > an MS Excel or PDF file?
> >
> > The report currently has an average of 29,995 records and is approximately
> > 19,398,584 bytes in size. (650+ pages)
> >
> > However, when rendering the report, the report takes only 30+ seconds.
> >
> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
> >
> > Thank you in advance for your support.
>
>|||It looks to me like you can't from the report manager. I don't know if you
can use web services (my guess is that you can). In RS 2005 you can
configure RS to automatically use CSV ASCII (which is what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:68EB7C88-F34A-4254-B963-A0F5F3FDE734@.microsoft.com...
> The solution worked! Thank you very much.
> However, is there a way of scheduling the report using the same solution?
>
> "Bruce L-C [MVP]" wrote:
>> If it takes 30 seconds to html then it will take as much as 10 times
>> longer
>> to Excel or PDF. For Excel there is a work around.
>> 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 (RS 2000) 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 be able to configure it to
>> use
>> ASCII instead of Unicode when exporting to CSV.
>> So, in RS 2000 I add a link at the top of the page and then use Jump to
>> URL.
>> 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. CSV ASCII will be just as fast as HTML.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Terry" <Terry@.discussions.microsoft.com> wrote in message
>> news:5429586F-A50A-43F9-B913-E7E7B98A7F15@.microsoft.com...
>> > How can I increase the performance when attempting to export large
>> > reports
>> > to
>> > an MS Excel or PDF file?
>> >
>> > The report currently has an average of 29,995 records and is
>> > approximately
>> > 19,398,584 bytes in size. (650+ pages)
>> >
>> > However, when rendering the report, the report takes only 30+ seconds.
>> >
>> > http://reportserv/Reports/Pages/Report.aspx?ItemPath=%
>> > 2fMember_Verification%2fReports%2fIPC_Members_Verification
>> >
>> > Thank you in advance for your support.
>>