sql server reporting services: Excel Report - Large File -- sql server reporting services

You're in the

sql server reporting services

group:

Excel Report - Large File

Madhan Raj J
10/29/2007 10:46:01 PM

sql server reporting services: SQL Server 2005 reporting service is being used to generate a large excel
report with about 30,000 rows. The size of file generated is ~15MB. but if
the same file is opened in Excel 2003 and saved back in XLS format itself,
the size of the file reduces to approximately 50% of the original size.

One of the initial guesses why this is occuring is that SQL Server 2005
reporting service generates unicode strings for the report data elements,
while excel saving it back, saves it as ANSI. (the report is in english only).

Let me know if the assumption is correct. If so what are means of fixing

Re: Excel Report - Large File

EMartinez
10/30/2007 12:00:00 AM

On Oct 30, 12:46 am, Madhan Raj J
[quoted text, click to view]

This is just a thought, but it might be related to the Excel export
driver that is used in SSRS: to your point, that it could be adding
extra items. One way to check this out is to probably run a comparison
between the 2 files (maybe using something like a trial version of
Araxis Merge >> http://www.araxis.com/merge/ ). Also, I'm not sure if
you want to go this far, but if the size is a major issue you can
either zip the excel file programmatically with a third party DLL (a
few open source ones available) -or- create a small ASP.NET
application (or console EXE) that basically reads in the Excel file
(via streamreader) and does a string replace on all occurrences of the
undesired format in question and stream it back out (via
streamwriter). Of course, I'm not 100% sure that this will work with
Excel; however, it's worth a shot. Also, I think that there is a
technology called Automation in .NET that might be able to help w/
this. Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

Re: Excel Report - Large File

Bruce L-C [MVP]
10/30/2007 9:05:36 AM

You got me curious so I did a little investiaging. I don't know if Excel
export is doing as you say. I do know that CSV export defaults to unicode.
You can change CSV to export in ASCII by making the following change in
rsreportserver.config (note commenting out the original line):

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

You could see if exporting to CSV ASCII format makes for a smaller file.
This will open up appropriately in Excel 2003 (Unicode shoves each row into
a single cell).

I decided to try the same thing with the Excel entry but I did not see any
difference.

If you make the CSV change go to the RS configuration tool click on Server
Status and stop and start Report Server. It only takes a few seconds to do
this. It will cause the configuration file changes to be processed.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Excel Report - Large File

Madhan Raj J
10/30/2007 10:15:01 AM

Thanks of rthe reply..

But my client needs to the report to be exported to excel only, as there is
significant formatting around the data. As you have observed, the device
setting for excel does not have the encoding options as in CSV.

[quoted text, click to view]

Re: Excel Report - Large File

Bruce L-C [MVP]
10/30/2007 12:22:04 PM

Perhaps it is the formatting making the difference but I am not seeing any
difference in size when I save from Excel (versus the Excel created by RS).

One other issue, are you on SP2?

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn