sql server reporting services: When exporting to excel from reporting services. Excel maintains the format
of the export by merging the cells. However, when trying to sort base on a
row or column it produces a cell merge error. Is there any way to export to
excel from reporting services into a raw excel spreadsheet? This way you can
modify the data as needed.

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. Much faster than exporting to Excel.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

This problem occurs only when you have any image or title in the page header
or above the table.
If you remove them and if the report is having only table then you will be
able to export to excel and can do sorting and filtering in the excel.

To add the title, you can create another row above the table header.