Currency format when exporting to excel

Hi,

I have a problem with the number format when i export my reports to excel with Reporting Services. I set numbers as currency with the command FormatCurrency() in visual studio, but when i export the report to excel, the numbers are considered as text.

Does anyone have a solution?
Thanks in advance.

Use the Format property on the cell/textbox, for currency the format string should be c<number decimal places>, i.e. c0 prints the currency figure with no decimal places. Once you have done this, remove the FormatCurrency() function from the expression as it overrides anything you have in the Format property. The reason the FormatCurrency() does not work when exporting to Excel is because its return value is a string, so Excel is confused - is it a currency or is it a string?

|||Thanks a lot, it works perfectly!!!! I'm so releaved

Currency Exporting to Excel

Hi,
In one of my reports I have some fields that are currency fields displayed
with the currency format string (C). However, when I export these to Excel
(2003) I get the little yellow warning that says Number stored as text. And
if I try to do a sum by dragging across the cells and clicking the sigma
button at the top, it causes it to put an odd formula in the top row.
Is there an easy fix so that #'s exported as currency remain tied to excel
as currency?
Thanks,
ChrisI tried this on a matrix in RS2005 and it worked OK.
The format was c0. I do remember it being an issue in 2000. I don't
think there was a way round it in the report definition.
The only thing you could try is, in Excel, highlight the column and
format the cell to currency. If this works you will be able to do a SUM.
Cheers
Chris
Chris Taylor wrote:
> Hi,
> In one of my reports I have some fields that are currency fields
> displayed with the currency format string (C). However, when I
> export these to Excel (2003) I get the little yellow warning that
> says Number stored as text. And if I try to do a sum by dragging
> across the cells and clicking the sigma button at the top, it causes
> it to put an odd formula in the top row.
> Is there an easy fix so that #'s exported as currency remain tied to
> excel as currency?
> Thanks,
> Chris|||Tried formatting C0, stlil getting the text formatted as #.
MS any insight on this?
"Chris" <mr.mcgoo@.gmail.com> wrote in message
news:uPH9wVvQGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I tried this on a matrix in RS2005 and it worked OK.
> The format was c0. I do remember it being an issue in 2000. I don't
> think there was a way round it in the report definition.
> The only thing you could try is, in Excel, highlight the column and
> format the cell to currency. If this works you will be able to do a SUM.
> Cheers
> Chris
>
> Chris Taylor wrote:
>> Hi,
>> In one of my reports I have some fields that are currency fields
>> displayed with the currency format string (C). However, when I
>> export these to Excel (2003) I get the little yellow warning that
>> says Number stored as text. And if I try to do a sum by dragging
>> across the cells and clicking the sigma button at the top, it causes
>> it to put an odd formula in the top row.
>> Is there an easy fix so that #'s exported as currency remain tied to
>> excel as currency?
>> Thanks,
>> Chris
>
> --
>|||Hi Chris,
Please modify the column width in Excel. I have tested in my side on RS
2005, it works fine.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Nope. I just tried this; both FormatCurrency and Format c0 send a
number as text, causing Excel to just put a Smart Tag on it and ignore
it in functions. I know that converting numbers to text is the entire
purpose of those two functions, but making them the sole means of
formatting numbers makes the Excel export function about as useless as
nipples on the Batsuit.
Wei Lu: I think Chris Taylor means "text formatted as number" and not
"the column's too narrow, so Excel puts in pound signs to tell you to
resize the column."

CSV Exports are not as expected, columns are missing

I use SQL2005. I have a hard time understanding the reason why the CSV exports are not exporting the report with all the columns. Only the first column of data is exported.
example, I have report like this

Product Customer Jan Feb Mar Apr Jun

p1 C1 12 3 13 7 20

After csv export, I get a bunch of label down the rows, then I see my Product and Customer columns but I can only see Jan. Feb Mar Apr and Jun are NOT exported.

All these columns have exactly the same settings and the report is not a matrix report.

When I export to Excel or HTML, the result is perfect.

Any idea?

Thanks

Philippe

Make sure that Output property is set to Auto or Yes for those textboxes (Textbox Properties dialog -> Data Output tab)|||

Hi,

All these text boxes have the output property set to Auto.

|||Would you like to e-mail your report to me?|||

OK, thanks for your help. I greatly appreciate it. The rdl of one of these reports is on its way.

Thanks,

Philippe

|||

There is a bug here : when Output is set Auto it is translated to No for table columns with Hidden set to an expression.

The workaround is to set Output to Yes for textboxes which should be exported.

CSV Exporting SSMSE (Text Identifier)

I just updated to SQL Express SP2 per this Feedback item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=132806

But I don't see an option to use quoted identifiers. Is it not included in the Express version?

I thinnk that is set on a database level. (And the 'default' condition is [ ON ].)

To change:

ALTER DATABASE MyDatabase

SET QUOTED_IDENTIFIER ON

CSV exporting issue

Hi,

I have a simple report with a table and 3 columns. I have used only the detail row of the table to display data from a simple dataset that has a stored procedure as the data source.

When I export it to CSV format, the names of the textboxes that are in the table column also gets exported in the first row. Is there a way to suppress this?

I have already tried setting DataElementOutput = NoOutput for the table and the textboxes but nothing works. Please do not give me the link of an earlier thread on the same topic because that doesnt provide the answer.

I have also tried setting DeviceInfo parameters in RSReportDesigner.config and rsreportserver.config (NoHeader=true) but none of them work even after restarting the machine and SQL Reporting Service.

If there is a way or an alternative, please let me know.

Thanks,

Shyam

There is another issue with CSV exporting.

I want every field to be wrapped around double quotes, so I have handled that in the SQL query itself. When I export to CSV, it adds 4 more double quotes to every field which means every field has 6 double quotes around it (3 to the left and 3 to the right). I also tried handling it in the report by appending Chr(34) and an explicit double quote in the field expression but all of them seem to give the same stupid result (6 double quotes).

Is there a way to work around this problem in SQL Reporting services?

It's very frustrating.

Shyam

|||

Thanks everybody for not responding. Hope it works on another machine.

Shyam

CSV exporting issue

Hi,

I have a simple report with a table and 3 columns. I have used only the detail row of the table to display data from a simple dataset that has a stored procedure as the data source.

When I export it to CSV format, the names of the textboxes that are in the table column also gets exported in the first row. Is there a way to suppress this?

I have already tried setting DataElementOutput = NoOutput for the table and the textboxes but nothing works. Please do not give me the link of an earlier thread on the same topic because that doesnt provide the answer.

I have also tried setting DeviceInfo parameters in RSReportDesigner.config and rsreportserver.config (NoHeader=true) but none of them work even after restarting the machine and SQL Reporting Service.

If there is a way or an alternative, please let me know.

Thanks,

Shyam

There is another issue with CSV exporting.

I want every field to be wrapped around double quotes, so I have handled that in the SQL query itself. When I export to CSV, it adds 4 more double quotes to every field which means every field has 6 double quotes around it (3 to the left and 3 to the right). I also tried handling it in the report by appending Chr(34) and an explicit double quote in the field expression but all of them seem to give the same stupid result (6 double quotes).

Is there a way to work around this problem in SQL Reporting services?

It's very frustrating.

Shyam

|||

Thanks everybody for not responding. Hope it works on another machine.

Shyam

CSV Exporting

I posted this message last week, but I've been out of town for a few days
and can not find any responses.
I have a report that has a single table with a 2 row header, a single row
detail and a single row footer. The report works fine and exports to Excel
fine. However, when I try to export to CSV I am seeing some strange
results. My 2nd row header (which includes only an expression calculating
report date range), and my report footer (which is includes summations of
the detail rows) are included in EVERY row of my exported CSV file.
This may help:
report format:
header1
header2
header3
dataa datab datac
footer1a footer1b footer1c
results in:
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
Any ideas as to why this is happening? Has anyone else experienced the same
issue?
Thanks
TerrellYou're looking for the DataElementOutput property.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"troark" <t.roark.n0spam@.excite.com> wrote in message
news:uK9f5xOaEHA.3704@.TK2MSFTNGP09.phx.gbl...
> I posted this message last week, but I've been out of town for a few days
> and can not find any responses.
> I have a report that has a single table with a 2 row header, a single row
> detail and a single row footer. The report works fine and exports to
Excel
> fine. However, when I try to export to CSV I am seeing some strange
> results. My 2nd row header (which includes only an expression calculating
> report date range), and my report footer (which is includes summations of
> the detail rows) are included in EVERY row of my exported CSV file.
> This may help:
> report format:
> header1
> header2
> header3
> dataa datab datac
> footer1a footer1b footer1c
> results in:
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
>
> Any ideas as to why this is happening? Has anyone else experienced the
same
> issue?
> Thanks
> Terrell
>
>|||Thanks Chris! That was it.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ePjDoITaEHA.4068@.TK2MSFTNGP10.phx.gbl...
> You're looking for the DataElementOutput property.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "troark" <t.roark.n0spam@.excite.com> wrote in message
> news:uK9f5xOaEHA.3704@.TK2MSFTNGP09.phx.gbl...
> > I posted this message last week, but I've been out of town for a few
days
> > and can not find any responses.
> >
> > I have a report that has a single table with a 2 row header, a single
row
> > detail and a single row footer. The report works fine and exports to
> Excel
> > fine. However, when I try to export to CSV I am seeing some strange
> > results. My 2nd row header (which includes only an expression
calculating
> > report date range), and my report footer (which is includes summations
of
> > the detail rows) are included in EVERY row of my exported CSV file.
> >
> > This may help:
> >
> > report format:
> > header1
> > header2
> > header3
> > dataa datab datac
> > footer1a footer1b footer1c
> >
> > results in:
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> > header2,footer1a,footer1b,footer1c,data(x)a,data(x)b,data(x)c
> >
> >
> > Any ideas as to why this is happening? Has anyone else experienced the
> same
> > issue?
> >
> > Thanks
> > Terrell
> >
> >
> >
>

CSV Export error

Hi,

After exporting a report to CSV format, when opening the file in Excel, all the data in a row comes into a single column, than spliting into different columns.

We can split into different columns using 'Text to columns' option from 'Data' Menu, but does not do defaultly as a CSV file is supposed to do.

Is this is bug with Reporting Service? Or am i missing something?

I am using release version of MS Reporting Service 2005.

Thanks,

Anobe

I experienced this problem too. The default setting is unicode, you have to specify ASCII for csv rendering. We were able to fix it by changing the config file.

File to change:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config

Before:

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

After:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">

<Configuration>

<DeviceInfo>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

|||

Thanks buddy,

this was very helpful. I didn't do the exact way, since i cannot change the config file. So, i handled the situation in the code using System.Text.UniEncoding.

|||

hi anobe....

i am facing the same situation like u...i cant change the config file. so i am wondering how did u handled the situation

using the system.text.uniencoding ? could you please elobrate further

thanks bro

CSV Export error

Hi,

After exporting a report to CSV format, when opening the file in Excel, all the data in a row comes into a single column, than spliting into different columns.

We can split into different columns using 'Text to columns' option from 'Data' Menu, but does not do defaultly as a CSV file is supposed to do.

Is this is bug with Reporting Service? Or am i missing something?

I am using release version of MS Reporting Service 2005.

Thanks,

Anobe

I experienced this problem too. The default setting is unicode, you have to specify ASCII for csv rendering. We were able to fix it by changing the config file.

File to change:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config

Before:

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

After:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">

<Configuration>

<DeviceInfo>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

|||

Thanks buddy,

this was very helpful. I didn't do the exact way, since i cannot change the config file. So, i handled the situation in the code using System.Text.UniEncoding.

|||

hi anobe....

i am facing the same situation like u...i cant change the config file. so i am wondering how did u handled the situation

using the system.text.uniencoding ? could you please elobrate further

thanks bro

|||In case you didn't get your problem solved (or for others out there with the same problem):

I was having this problem too and changing the encoding of the report to ASCII (default is UTF8 I think) fixed the problem.

I am calling my report through the url, you can pass the encoding as a url parameter, for example:
http://reportservername/ReportServer/report?rs:Command=Render&rs:Format=CSV&rc:Encoding=ASCII

CSV export with characters "ÿþ"

CSV export with characters "ÿþ":
Using reporting services and URL encoding I'm exporting about 1000 records
to CSV format, after exporting i always these " ÿþ " special characters as
the beginning of the file.
these character can be seen clearly only if opened with office 97 or earlier
or with any HEX viewer.
It seems its a common problem with excel formatting.
http://groups.google.com.au/group/microsoft.public.excel.programming/browse_thread/thread/e540d95a1f3e83ae/4828dd254c80c8b9?q=%C3%BF%C3%BE+in+csv&rnum=2&hl=en
Does anyone has the same problem? I am not able to use this CSV for printing
with the printing softwares unless i manually remove these characters.
This problem disappeared if I select all data from CSV and paste it to a new
NOTEPAD and save as CSV. Is there any other way to avoid this extra work.
Cheers,
SRM.The CSV export from Report Manager uses the Unicode format for the CSV
created. You could try doing a jump to URL that specifies the ASCII format.
It is one of the options you can provide on the URL. Give that a try and see
if it solves the problem for you. It also solves the issue with Excel
opening it and putting everything in a single column. When exported as ASCII
CSV it opens up in Excel in all the correct columns.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"srm" <srm@.discussions.microsoft.com> wrote in message
news:2E59C2AE-833B-45B1-8FFE-84784B0AF82E@.microsoft.com...
> CSV export with characters "ÿþ":
> Using reporting services and URL encoding I'm exporting about 1000 records
> to CSV format, after exporting i always these " ÿþ " special characters as
> the beginning of the file.
> these character can be seen clearly only if opened with office 97 or
> earlier
> or with any HEX viewer.
> It seems its a common problem with excel formatting.
> http://groups.google.com.au/group/microsoft.public.excel.programming/browse_thread/thread/e540d95a1f3e83ae/4828dd254c80c8b9?q=%C3%BF%C3%BE+in+csv&rnum=2&hl=en
> Does anyone has the same problem? I am not able to use this CSV for
> printing
> with the printing softwares unless i manually remove these characters.
> This problem disappeared if I select all data from CSV and paste it to a
> new
> NOTEPAD and save as CSV. Is there any other way to avoid this extra work.
> Cheers,
> SRM.|||Tried with rs:Encoding=ASCII into the URL.
But did not remove those Characters. It's the same export format.
Given below is the part of the parameters within URL.
&rs:Command=Render&rs:Format=CSV&rc:Toolbar=false&rs:Encoding=ASCII&rc:Qualifier=*?&rc:NoHeader=true&rc:FieldDelimiter="|"&ProcessID=19653
Thank you.
Roy.
"Bruce L-C [MVP]" wrote:
> The CSV export from Report Manager uses the Unicode format for the CSV
> created. You could try doing a jump to URL that specifies the ASCII format.
> It is one of the options you can provide on the URL. Give that a try and see
> if it solves the problem for you. It also solves the issue with Excel
> opening it and putting everything in a single column. When exported as ASCII
> CSV it opens up in Excel in all the correct columns.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "srm" <srm@.discussions.microsoft.com> wrote in message
> news:2E59C2AE-833B-45B1-8FFE-84784B0AF82E@.microsoft.com...
> > CSV export with characters "ÿþ":
> >
> > Using reporting services and URL encoding I'm exporting about 1000 records
> > to CSV format, after exporting i always these " ÿþ " special characters as
> > the beginning of the file.
> >
> > these character can be seen clearly only if opened with office 97 or
> > earlier
> > or with any HEX viewer.
> >
> > It seems its a common problem with excel formatting.
> >
> > http://groups.google.com.au/group/microsoft.public.excel.programming/browse_thread/thread/e540d95a1f3e83ae/4828dd254c80c8b9?q=%C3%BF%C3%BE+in+csv&rnum=2&hl=en
> >
> > Does anyone has the same problem? I am not able to use this CSV for
> > printing
> > with the printing softwares unless i manually remove these characters.
> >
> > This problem disappeared if I select all data from CSV and paste it to a
> > new
> > NOTEPAD and save as CSV. Is there any other way to avoid this extra work.
> >
> > Cheers,
> > SRM.
>
>|||Oh well. It was worth a try. I don't have any other suggestions.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"srm" <srm@.discussions.microsoft.com> wrote in message
news:F88C961C-25A5-4DC7-9F75-22EB9D9AB99A@.microsoft.com...
> Tried with rs:Encoding=ASCII into the URL.
> But did not remove those Characters. It's the same export format.
> Given below is the part of the parameters within URL.
> &rs:Command=Render&rs:Format=CSV&rc:Toolbar=false&rs:Encoding=ASCII&rc:Qualifier=*?&rc:NoHeader=true&rc:FieldDelimiter="|"&ProcessID=19653
> Thank you.
> Roy.
>
> "Bruce L-C [MVP]" wrote:
>> The CSV export from Report Manager uses the Unicode format for the CSV
>> created. You could try doing a jump to URL that specifies the ASCII
>> format.
>> It is one of the options you can provide on the URL. Give that a try and
>> see
>> if it solves the problem for you. It also solves the issue with Excel
>> opening it and putting everything in a single column. When exported as
>> ASCII
>> CSV it opens up in Excel in all the correct columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "srm" <srm@.discussions.microsoft.com> wrote in message
>> news:2E59C2AE-833B-45B1-8FFE-84784B0AF82E@.microsoft.com...
>> > CSV export with characters "ÿþ":
>> >
>> > Using reporting services and URL encoding I'm exporting about 1000
>> > records
>> > to CSV format, after exporting i always these " ÿþ " special characters
>> > as
>> > the beginning of the file.
>> >
>> > these character can be seen clearly only if opened with office 97 or
>> > earlier
>> > or with any HEX viewer.
>> >
>> > It seems its a common problem with excel formatting.
>> >
>> > http://groups.google.com.au/group/microsoft.public.excel.programming/browse_thread/thread/e540d95a1f3e83ae/4828dd254c80c8b9?q=%C3%BF%C3%BE+in+csv&rnum=2&hl=en
>> >
>> > Does anyone has the same problem? I am not able to use this CSV for
>> > printing
>> > with the printing softwares unless i manually remove these characters.
>> >
>> > This problem disappeared if I select all data from CSV and paste it to
>> > a
>> > new
>> > NOTEPAD and save as CSV. Is there any other way to avoid this extra
>> > work.
>> >
>> > Cheers,
>> > SRM.
>>|||Instead of &rs:Encoding try &rc:Encoding
HTH
"srm" wrote:
> Tried with rs:Encoding=ASCII into the URL.
> But did not remove those Characters. It's the same export format.
> Given below is the part of the parameters within URL.
> &rs:Command=Render&rs:Format=CSV&rc:Toolbar=false&rs:Encoding=ASCII&rc:Qualifier=*?&rc:NoHeader=true&rc:FieldDelimiter="|"&ProcessID=19653
> Thank you.
> Roy.
>
> "Bruce L-C [MVP]" wrote:
> > The CSV export from Report Manager uses the Unicode format for the CSV
> > created. You could try doing a jump to URL that specifies the ASCII format.
> > It is one of the options you can provide on the URL. Give that a try and see
> > if it solves the problem for you. It also solves the issue with Excel
> > opening it and putting everything in a single column. When exported as ASCII
> > CSV it opens up in Excel in all the correct columns.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "srm" <srm@.discussions.microsoft.com> wrote in message
> > news:2E59C2AE-833B-45B1-8FFE-84784B0AF82E@.microsoft.com...
> > > CSV export with characters "ÿþ":
> > >
> > > Using reporting services and URL encoding I'm exporting about 1000 records
> > > to CSV format, after exporting i always these " ÿþ " special characters as
> > > the beginning of the file.
> > >
> > > these character can be seen clearly only if opened with office 97 or
> > > earlier
> > > or with any HEX viewer.
> > >
> > > It seems its a common problem with excel formatting.
> > >
> > > http://groups.google.com.au/group/microsoft.public.excel.programming/browse_thread/thread/e540d95a1f3e83ae/4828dd254c80c8b9?q=%C3%BF%C3%BE+in+csv&rnum=2&hl=en
> > >
> > > Does anyone has the same problem? I am not able to use this CSV for
> > > printing
> > > with the printing softwares unless i manually remove these characters.
> > >
> > > This problem disappeared if I select all data from CSV and paste it to a
> > > new
> > > NOTEPAD and save as CSV. Is there any other way to avoid this extra work.
> > >
> > > Cheers,
> > > SRM.
> >
> >
> >