RS2005: Excel huge file size

I have an issue with exporting RS2005 reports to Excel:
1) The exported fie size is disproportional to the rowset size - ~7000
records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
2) RS exports to Excel with a redundant blank A column on the left and 4
rows that hold the report caption. Is there anyway to configure RS not to
export with these rows and columns, so it'll start directly with my data?
We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818Offirh wrote:
> I have an issue with exporting RS2005 reports to Excel:
> 1) The exported fie size is disproportional to the rowset size - ~7000
> records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
> 2) RS exports to Excel with a redundant blank A column on the left and 4
> rows that hold the report caption. Is there anyway to configure RS not to
> export with these rows and columns, so it'll start directly with my data?
> We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818
The best way to minimize file size when exporting to excel is to remove
as much formatting from the report design as possible. Remove any
borders, colors, etc. Still, SSRS will apply some formatting to the
resulting export and make it very difficult to match the more efficient
CSV export, (which is your best workaround IMO).
Ghost columns can be very difficult to remove completely, however,
paying attention to the left and right boundaries of your controls in
relation to other controls on your report can help.
For example, if you have a textbox at the top of your report holding a
report title, and then a table beneath which contains detail records,
making sure that the left and right sides of the textbox control align
vertically with the table control, or column boundaries in the table
control will minimize the occurrence of Ghost columns in the excel
export.
Basically, SSRS seems to evaluate each control boundary, and will
define all columns necessary to bound each control's right and left
side, (often merging columns as well which makes things a real mess
when you try to sort data in the spreadsheet).
SoftArtisans has a nice product call OfficeWriter that helps eliminate
the problem altogether. OfficeWriter allows you to define DataSets in
SSRS, and then use Excel to define where you want the data from the
datasets to land. This bypasses SSRS doing the formatting of the Excel
spreadsheet, which results in several desirable outcomes:
1) The memory drawdown on your SSRS box is drastically reduced when
the report is being run. Excel exports take a lot of memory to
complete!
2) The resulting file size is reduced because none of the formatting
codes are generated by SSRS which balloon the excel file size.
3) You bypass all the other formatting problems with merged
columns/rows
4) You get to use all the native Excel functionality in the resulting
report, including custom macros, etc.
But, it's not free!
Philip|||One other design point. You should not use textboxes at all. If you have a
textbox at the top of your report and then a table the user will not be able
to sort the data because of merged columns issue in Excel. I add extra
header rows to my table and put the report title there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PhilipL" <pal@.philiplee.com> wrote in message
news:1158092129.378116.36300@.b28g2000cwb.googlegroups.com...
> Offirh wrote:
>> I have an issue with exporting RS2005 reports to Excel:
>> 1) The exported fie size is disproportional to the rowset size - ~7000
>> records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
>> 2) RS exports to Excel with a redundant blank A column on the left and 4
>> rows that hold the report caption. Is there anyway to configure RS not to
>> export with these rows and columns, so it'll start directly with my data?
>> We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818
> The best way to minimize file size when exporting to excel is to remove
> as much formatting from the report design as possible. Remove any
> borders, colors, etc. Still, SSRS will apply some formatting to the
> resulting export and make it very difficult to match the more efficient
> CSV export, (which is your best workaround IMO).
> Ghost columns can be very difficult to remove completely, however,
> paying attention to the left and right boundaries of your controls in
> relation to other controls on your report can help.
> For example, if you have a textbox at the top of your report holding a
> report title, and then a table beneath which contains detail records,
> making sure that the left and right sides of the textbox control align
> vertically with the table control, or column boundaries in the table
> control will minimize the occurrence of Ghost columns in the excel
> export.
> Basically, SSRS seems to evaluate each control boundary, and will
> define all columns necessary to bound each control's right and left
> side, (often merging columns as well which makes things a real mess
> when you try to sort data in the spreadsheet).
> SoftArtisans has a nice product call OfficeWriter that helps eliminate
> the problem altogether. OfficeWriter allows you to define DataSets in
> SSRS, and then use Excel to define where you want the data from the
> datasets to land. This bypasses SSRS doing the formatting of the Excel
> spreadsheet, which results in several desirable outcomes:
> 1) The memory drawdown on your SSRS box is drastically reduced when
> the report is being run. Excel exports take a lot of memory to
> complete!
> 2) The resulting file size is reduced because none of the formatting
> codes are generated by SSRS which balloon the excel file size.
> 3) You bypass all the other formatting problems with merged
> columns/rows
> 4) You get to use all the native Excel functionality in the resulting
> report, including custom macros, etc.
> But, it's not free!
> Philip
>sql