[reporting services 2005] excel export doesn't honor numer..

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/5095/amountcellformatdr4.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]"
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
>
> wrote in message
>
>
>
> > 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
>
>
 >> Stay informed about: [reporting services 2005] excel export doesn't honor numer.. 

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

On Nov 26, 11:46 am, 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/5095/amountcellformatdr4.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]"
> 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
>
> > wrote in message
>
> >
>
> > > 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
>
>
 >> Stay informed about: [reporting services 2005] excel export doesn't honor numer.. 

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 from the
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

wrote in message

> Just in case, I wrapped the expression with Sum() without any change
>
> On Nov 26, 11:46 am, 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/5095/amountcellformatdr4.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]"
>> 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
>>
>> > wrote in message
>>
>> >
>>
>> > > 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
>>
>>
>
 >> Stay informed about: [reporting services 2005] excel export doesn't honor numer.. 

Thanks Andrew,

The CDec function make it works with excel!

On Nov 26, 10:41 pm, "Andrew Karcher [SQL MVP]"
wrote:
> 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 from the
> 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
>
> wrote in message
>
>
>
> > Just in case, I wrapped the expression with Sum() without any change
>
> > On Nov 26, 11:46 am, 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/5095/amountcellformatdr4.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]"
> >> 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
>
> >> > wrote in message
>
> >> >
>
> >> > > 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
>
>
 >> Stay informed about: [reporting services 2005] excel export doesn't honor numer..