Export to Excel - file size bloat

When exporting to Excel from Report Manager, we are finding that the file
size is bloated. If I open the exported file, then do a Save As, the file
size drops by at least 50%.
Any thoughts?Make sure your on SP2 - we made some big improvements over RTM in Excel file
size.
There are some optimizations the Excel format is capable of that we do not
currently support. 50% difference seems high but is theoretically possible
even with SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||Already on SP2.
"Donovan Smith [MSFT]" wrote:
> Make sure your on SP2 - we made some big improvements over RTM in Excel file
> size.
> There are some optimizations the Excel format is capable of that we do not
> currently support. 50% difference seems high but is theoretically possible
> even with SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> > When exporting to Excel from Report Manager, we are finding that the file
> > size is bloated. If I open the exported file, then do a Save As, the file
> > size drops by at least 50%.
> >
> > Any thoughts?
>
>|||I also noticed the same thing with all service packs.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> Already on SP2.
> "Donovan Smith [MSFT]" wrote:
>> Make sure your on SP2 - we made some big improvements over RTM in Excel
>> file
>> size.
>> There are some optimizations the Excel format is capable of that we do
>> not
>> currently support. 50% difference seems high but is theoretically
>> possible
>> even with SP2.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> > When exporting to Excel from Report Manager, we are finding that the
>> > file
>> > size is bloated. If I open the exported file, then do a Save As, the
>> > file
>> > size drops by at least 50%.
>> >
>> > Any thoughts?
>>|||I am having the same problem. Not only the huge size frezes the exporting also.
"Amila Liyanaarchchi" wrote:
> I also noticed the same thing with all service packs.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> > Already on SP2.
> >
> > "Donovan Smith [MSFT]" wrote:
> >
> >> Make sure your on SP2 - we made some big improvements over RTM in Excel
> >> file
> >> size.
> >>
> >> There are some optimizations the Excel format is capable of that we do
> >> not
> >> currently support. 50% difference seems high but is theoretically
> >> possible
> >> even with SP2.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> >> > When exporting to Excel from Report Manager, we are finding that the
> >> > file
> >> > size is bloated. If I open the exported file, then do a Save As, the
> >> > file
> >> > size drops by at least 50%.
> >> >
> >> > Any thoughts?
> >>
> >>
> >>
>
>|||If you have a lot of data, try exporting as CSV ASCII. In 2005 you can have
Report Manager default to this when exporting from there.
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 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 will be able to configure it to use ASCII
instead of Unicode.
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.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sun" <sun@.discussions.microsoft.com> wrote in message
news:CB2B8A34-3994-444C-9059-E5FAB6CFE764@.microsoft.com...
>I am having the same problem. Not only the huge size frezes the exporting
>also.
> "Amila Liyanaarchchi" wrote:
>> I also noticed the same thing with all service packs.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
>> > Already on SP2.
>> >
>> > "Donovan Smith [MSFT]" wrote:
>> >
>> >> Make sure your on SP2 - we made some big improvements over RTM in
>> >> Excel
>> >> file
>> >> size.
>> >>
>> >> There are some optimizations the Excel format is capable of that we do
>> >> not
>> >> currently support. 50% difference seems high but is theoretically
>> >> possible
>> >> even with SP2.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> >> > When exporting to Excel from Report Manager, we are finding that the
>> >> > file
>> >> > size is bloated. If I open the exported file, then do a Save As,
>> >> > the
>> >> > file
>> >> > size drops by at least 50%.
>> >> >
>> >> > Any thoughts?
>> >>
>> >>
>> >>
>>|||HI there,
we have the same problem. I have a report with 389 records having 23
columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
(feature) is, that RS saves the file as Excel XML. That's progress to you!!!
If you open the file in Excel and save it as normal Excel format, you will
get a much smaller fle size!
Honestly, I am not impressed. I get files with around 7700 records which is
80 MByte!!! It is not possible to open such a file! If I save this file as
CSV it is just over 12 MByte! But if you think you can just double click on
the CSV you'll get another disappointment. EXCEL opens it with one line per
record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
Don't get me wrong, I love RS! But the export function just sucks! Sorry!
Cheers
Peter
"Kristen" wrote:
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||You are running RS 2000 with no service packs. Install either SP1 or SP2.
The Excel export was changed with service pack one to use native format.
If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
RS 2000 I added a link to export to CSV using ASCII.
At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
problem free upgrade for me and has some very good new features: faster,
multi-select parameters, date picker, end user sorting, etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> HI there,
> we have the same problem. I have a report with 389 records having 23
> columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> (feature) is, that RS saves the file as Excel XML. That's progress to
> you!!!
> If you open the file in Excel and save it as normal Excel format, you will
> get a much smaller fle size!
> Honestly, I am not impressed. I get files with around 7700 records which
> is
> 80 MByte!!! It is not possible to open such a file! If I save this file as
> CSV it is just over 12 MByte! But if you think you can just double click
> on
> the CSV you'll get another disappointment. EXCEL opens it with one line
> per
> record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> Cheers
> Peter
> "Kristen" wrote:
>> When exporting to Excel from Report Manager, we are finding that the file
>> size is bloated. If I open the exported file, then do a Save As, the
>> file
>> size drops by at least 50%.
>> Any thoughts?|||Hiya Bruce,
first of all, I am sorry that I wrote this post yesterday. Too many emotions
because I had all this trouble with the Excel Export. You are absolutely
right, SP2 fixes the problem. I now have to convince the people to update a
production server with SP2.
I know that the upgrade to 2005 improves a lot. But I guess you are in IT
for quite a while. Big organisations usually have a problem with upgrading.
We techos can't understand this. I, for example work in a world wide IT
company. Can you imagine that we still run on XP without SP2?
I am also quite concerned about Excel and XML based on the experiences I
made with the RS Excel file size. If over 7000 rows in a spreadsheet have a
filesize of over 80 MByte, then there is definitely a problem. But that's of
course a different story.
Anyway, I get into waffling.
Thank you for your help with this. Very much appreciated!
Cheers
Peter
"Bruce L-C [MVP]" wrote:
> You are running RS 2000 with no service packs. Install either SP1 or SP2.
> The Excel export was changed with service pack one to use native format.
> If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
> RS 2000 I added a link to export to CSV using ASCII.
> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
> problem free upgrade for me and has some very good new features: faster,
> multi-select parameters, date picker, end user sorting, etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> > HI there,
> >
> > we have the same problem. I have a report with 389 records having 23
> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> > (feature) is, that RS saves the file as Excel XML. That's progress to
> > you!!!
> > If you open the file in Excel and save it as normal Excel format, you will
> > get a much smaller fle size!
> >
> > Honestly, I am not impressed. I get files with around 7700 records which
> > is
> > 80 MByte!!! It is not possible to open such a file! If I save this file as
> > CSV it is just over 12 MByte! But if you think you can just double click
> > on
> > the CSV you'll get another disappointment. EXCEL opens it with one line
> > per
> > record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> >
> > Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> >
> > Cheers
> >
> > Peter
> >
> > "Kristen" wrote:
> >
> >> When exporting to Excel from Report Manager, we are finding that the file
> >> size is bloated. If I open the exported file, then do a Save As, the
> >> file
> >> size drops by at least 50%.
> >>
> >> Any thoughts?
>
>|||One thing to remember when it comes to RS 2005. You can upgrade to RS 2005
without upgrade SQL Server to 2005. That might be easier to do since in
large organizations, database versions are changed slowly (I work for a
fortune 100 company, maybe fortune 50). You do need a SQL Server 2005
license.
By the way, my company is also on XP with SP1, not SP2. However, for RS you
are only affecting reporting services, not the database, not the users.
Another biggie with going to SP1 or SP2 is you get client side printing.
Just based on that it is worthwhile to install the service pack.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:BFC3D54D-6F05-42E7-B4B6-1B75FFAFC95A@.microsoft.com...
> Hiya Bruce,
> first of all, I am sorry that I wrote this post yesterday. Too many
> emotions
> because I had all this trouble with the Excel Export. You are absolutely
> right, SP2 fixes the problem. I now have to convince the people to update
> a
> production server with SP2.
> I know that the upgrade to 2005 improves a lot. But I guess you are in IT
> for quite a while. Big organisations usually have a problem with
> upgrading.
> We techos can't understand this. I, for example work in a world wide IT
> company. Can you imagine that we still run on XP without SP2?
> I am also quite concerned about Excel and XML based on the experiences I
> made with the RS Excel file size. If over 7000 rows in a spreadsheet have
> a
> filesize of over 80 MByte, then there is definitely a problem. But that's
> of
> course a different story.
> Anyway, I get into waffling.
> Thank you for your help with this. Very much appreciated!
> Cheers
> Peter
> "Bruce L-C [MVP]" wrote:
>> You are running RS 2000 with no service packs. Install either SP1 or SP2.
>> The Excel export was changed with service pack one to use native format.
>> If RS 2005 you can set up Report Manager to export CSV in ASCII format.
>> In
>> RS 2000 I added a link to export to CSV using ASCII.
>> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
>> problem free upgrade for me and has some very good new features: faster,
>> multi-select parameters, date picker, end user sorting, etc.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
>> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
>> > HI there,
>> >
>> > we have the same problem. I have a report with 389 records having 23
>> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
>> > (feature) is, that RS saves the file as Excel XML. That's progress to
>> > you!!!
>> > If you open the file in Excel and save it as normal Excel format, you
>> > will
>> > get a much smaller fle size!
>> >
>> > Honestly, I am not impressed. I get files with around 7700 records
>> > which
>> > is
>> > 80 MByte!!! It is not possible to open such a file! If I save this file
>> > as
>> > CSV it is just over 12 MByte! But if you think you can just double
>> > click
>> > on
>> > the CSV you'll get another disappointment. EXCEL opens it with one line
>> > per
>> > record. Because it is Unicode! If I transform it to ASCII it is 6
>> > MByte!
>> >
>> > Don't get me wrong, I love RS! But the export function just sucks!
>> > Sorry!
>> >
>> > Cheers
>> >
>> > Peter
>> >
>> > "Kristen" wrote:
>> >
>> >> When exporting to Excel from Report Manager, we are finding that the
>> >> file
>> >> size is bloated. If I open the exported file, then do a Save As, the
>> >> file
>> >> size drops by at least 50%.
>> >>
>> >> Any thoughts?
>>