Reduce size of xls export reporting services

chtru1
chtru1 used Ask the Experts™
on
Hi,

We have produced a report in reporting services that we want people to use as an 'extract' in excel for doing their own pivots and analysis.

We've tried to remove most formatting, but when the report is exported to excel after rendering, the file size is very large.  I presume it is because there is formatting on every cell?

Is there a way to remove formatting? We've investigated exporting as CSV, but excel doesn't open the file nicely (need to to text->columns afterwards).

Is there some way around this?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hope this isn't duplicated; I don't see my original answer.

Yes, you can highlight the entire spreadsheet by clicking the button at the top of the row numbers and to the left of the column letters.

Then go to EDIT -> CLEAR -> FORMATs.

Do you need to do this every single time?

Can you also check and make sure the formatting is what is causing your size issue? I cannot validate with my testing that formatting can exponentially increase the file size to justify what you are speaking about.

While the above answer does indeed remove the formatting I would like to ensure that we have taken care of your need.

Author

Commented:
Hi apologies for the late response. Something went amiss with my email notifications.

Was hoping for an automated way of doing it out of reporting services.  It needs to happen every single time and it'll be users who will be doing it.  They will expect the excel to work 'out of the box' so to speak.

I was hoping that maybe there was some parameter that you could pass through reporting services to make excel treat it unformatted?

Thanks for your replies so far.
Commented:
I solved this.  The solution was to change the format that the CSV files were coming out in from Unicode to ASCII so excel could open it natively without using the text to columns feature.

I went to C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config on the EBR and changed part of the file
FROM
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering" />

TO
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
                                <DeviceInfo>
                                                <Encoding>ASCII</Encoding>
                                </DeviceInfo>
                </Configuration>
</Extension>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial