Excel Report - Large File

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
this issue or else, is there any other option.On Oct 30, 12:46 am, Madhan Raj J
<MadhanR...@.discussions.microsoft.com> wrote:
> 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
> this issue or else, is there any other option.
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|||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"/>
-->
<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
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> 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
> this issue or else, is there any other option.|||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.
"Bruce L-C [MVP]" wrote:
> 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"/>
> -->
> <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
> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
> > 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
> > this issue or else, is there any other option.
>
>|||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
"Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
news:A5146FAB-7087-45C7-A00E-9CA8A734AB7A@.microsoft.com...
> 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.
> "Bruce L-C [MVP]" wrote:
>> 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"/>
>> -->
>> <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
>> "Madhan Raj J" <MadhanRajJ@.discussions.microsoft.com> wrote in message
>> news:54B53C11-1569-468B-AF12-3A6EAD6904BE@.microsoft.com...
>> > 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
>> > this issue or else, is there any other option.
>>|||I have the same issue. When save directly from Reporting Services, the file
is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
out Unicode. When I opened the excel file in notepad, I noticed there's lots
of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
them. I am still investigating, please let me know if you find a solution.
There's not much formatting in the report, just the column headings, so I
can't figure out what the excess binary data is.
Thanks.
"Madhan Raj J" wrote:
> 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
> this issue or else, is there any other option.|||I spoke too soon - it could be Unicode issue after all. In the large file,
the characters take two spaces, eg. "n e w " vs. "new" (in the smaller file).
"Pranil" wrote:
> I have the same issue. When save directly from Reporting Services, the file
> is 8 MB, but when opened in excel and than saved, it's 1.6 MB, which rules
> out Unicode. When I opened the excel file in notepad, I noticed there's lots
> of binary data in the tail of the 8 MB file, but the 1.6MB file doesn't have
> them. I am still investigating, please let me know if you find a solution.
> There's not much formatting in the report, just the column headings, so I
> can't figure out what the excess binary data is.
> Thanks.
> "Madhan Raj J" wrote:
> > 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
> > this issue or else, is there any other option.