dbTalk Databases Forums  

[reporting services 2005] excel export doesn't honor numeric format

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools [reporting services 2005] excel export doesn't honor numeric format

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools


Discuss [reporting services 2005] excel export doesn't honor numeric format in the microsoft.public.sqlserver.tools forum.



Reply
Page 5 of 5 < 1 2 3 4 5
 
Thread Tools Display Modes
  #41  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM





Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
  #42  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM





Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
  #43  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM


Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
  #44  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM


Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
  #45  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM


Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
  #46  
Old   
newsgroup.poster@gmail.com
 
Posts: n/a

Default Re: excel export doesn't honor numeric format - 11-27-2008 , 04:15 AM


Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41*pm, "Andrew Karcher [SQL MVP]"
<karche... (AT) hotmail (DOT) com> wrote:
Quote:
Gauthier,

I was able to reproduce. *Much easier to see with the screenshot. *Similar
problem to the one I described. *The issue is the return data type fromthe
function is an Object not a number so Excel does not see the value as a
number.

I was able to get around this by wrapping the IIF function in a conversion
to a numeric format.

In my case, I used the decimal conversion function and Excel now saw the
value as a number

=CDec(IIF(Fields!TotalDue.Value < 5000,0,Fields!TotalDue.Value))

Hope that helps,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:cccceed9-6495-44d8-b3e3-c8f48553deb2 (AT) u14g2000yqg (DOT) googlegroups.com...

Just in case, I wrapped the expression with Sum() without any change

On Nov 26, 11:46 am, newsgroup.pos... (AT) gmail (DOT) com wrote:
Hello Andrew,

I don't currently use Format function in the expression for the text
box value.

Here is a screenshot of relevant settings:

http://img141.imageshack.us/img141/5...lformatdr4.png

the source data comes from a select statement over a decimal(20,5)
value.

What I found is that group sums occuring over the same column appears
as real numbers: if I ctrl-select such cells in excel, I can see in
the status bar the summed value, but that won't work if I select any
of the detail cell.

Thanks you for your valuable support

On Nov 25, 9:14 pm, "Andrew Karcher [SQL MVP]" <karche... (AT) hotmail (DOT) com
wrote:

Gauthier,

How are you formatting the number? Based on your problem I suspect that
you
are using the Format method in the expression for the value of the text
box.
The issue is that method returns a string not a number, soExcelsees the
value as a string.

If you instead use the Format Code property for the textbox and enter
your
formatting there, then it will accurately be represented inExcelas a
number. In this case the value of field is going to be a number soExcel
treats it accordingly.

Let me know if you need more info,
Andrew Karcher
SQL Server MVP

newsgroup.pos... (AT) gmail (DOT) com> wrote in message

news:bb836242-1117-4589-a508-de8d39e481c7 (AT) 20g2000yqt (DOT) googlegroups.com....

Hello,

I need some help with an issue displaying numeric amounts as numbers
inexcel.

what I have in thereportfile:
-reportdataset exposing decimal(20,5) fields
-reportcells with Format=N Language=French

what I have in web andexcelresult:
- correct displayed format (thousands separator and 2 decimal
precision)

my issue:
inexcel, the amount cells are displayed with a warning: 'Number
Stored as Text'

What is the correct setting forexcelto understand my numeric amounts
as number (so I can make computations without applying formatting
manually after the export)?

Thanks

GauthierSegay


Reply With Quote
Reply
Page 5 of 5 < 1 2 3 4 5

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top