Friday, February 17, 2012

CSV in EXCEL = Users not happy

I am rolling out reporting services at my company and so far, everything has
gone smooth. The main problem for users testing reports has been that when
they open a CSV file in EXCEL, it opens up with everything in the "A" column.
Our current reporting platform does not have this problem and the users want
to keep using the data in this format (CSV files in Excel).
What I would like to do is to modify the CSV rendering format in order for
it to put the CSV files straight out in ASCII format. Does anyone know if
this is possible or if there is a better workaround?First, to explain what is happening. The default rendering of CSV is Unicode
which excel doesn't know what to do with and so it puts it all in the same
column. Here is a workaround. Add a textbox at the top of the report (I call
it Export Data), change to blue and underlined. Then you want to do a jump
to URL. (right mouse click, properties, advanced properties, navigation tab)
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.
RS 2005 will allow you to modify the config file so it defaults to rendering
in ASCII so this problem will go away. RS 2005 will be released in November.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:3AF6B09C-1FAC-48B5-A6A0-9024ABC586D5@.microsoft.com...
>I am rolling out reporting services at my company and so far, everything
>has
> gone smooth. The main problem for users testing reports has been that when
> they open a CSV file in EXCEL, it opens up with everything in the "A"
> column.
> Our current reporting platform does not have this problem and the users
> want
> to keep using the data in this format (CSV files in Excel).
> What I would like to do is to modify the CSV rendering format in order for
> it to put the CSV files straight out in ASCII format. Does anyone know if
> this is possible or if there is a better workaround?

No comments:

Post a Comment