aggregates and type conversion

Hello,
I have a field of type string which I know will either hold a double value or null. I wish to sum the value of this field and mulitply the result by -1 to give a negative number, but it's proving really difficult.
My first problem is that if I modify my field to use CDbl I get an error when it runs, along the lines of "Field contains a direct or indirect reference to itself..."
So I have to get around it by creating a separate field with a different name, like
myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
Why is that then ?
My next problem is that when I multiply by -1
e.g. =Sum(Fields!MyFieldDbl.Value * -1),
it gives me this error :
"The value expression for the textbox â'textbox19â' uses an aggregate function on data of varying data types".
To get around this I have to multiply by -1.0 instead ! As if there's a difference !!!
Type conversion in Reporting services is diabolical. I can't even use the format property unless I explicitly declare my field to be a particluar type first, even though I could happily use format$(myField,"C") in the Value property with any data type.
Can I expect MS might have a look at thses issues in time for the next service pack?Note: It is up to the data provider (e.g. managed SQL provider) how it
translates database types into .NET datatypes. Generally, you can determine
the runtime datatype of fields by temporarily adding a textbox to the report
which shows the runtime datatype:
=First(Fields!SomeFieldName.Value, "DataSetName").GetType.ToString
I assume that your field is actually a System.Decimal rather than a
System.Double as you indicated. An expression like this should work for you:
= -1 * Sum( iif(IsNothing(Fields!MyFieldDbl.Value), 0.0,
CDbl(Fields!MyFieldDbl.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Salway" <MikeSalway@.discussions.microsoft.com> wrote in message
news:B90D6631-A0D2-4F53-9C69-A06366ADA6C7@.microsoft.com...
> Hello,
> I have a field of type string which I know will either hold a double value
or null. I wish to sum the value of this field and mulitply the result
by -1 to give a negative number, but it's proving really difficult.
> My first problem is that if I modify my field to use CDbl I get an error
when it runs, along the lines of "Field contains a direct or indirect
reference to itself..."
> So I have to get around it by creating a separate field with a different
name, like
> myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
> Why is that then ?
> My next problem is that when I multiply by -1
> e.g. =Sum(Fields!MyFieldDbl.Value * -1),
> it gives me this error :
> "The value expression for the textbox 'textbox19' uses an aggregate
function on data of varying data types".
> To get around this I have to multiply by -1.0 instead ! As if there's a
difference !!!
> Type conversion in Reporting services is diabolical. I can't even use the
format property unless I explicitly declare my field to be a particluar type
first, even though I could happily use format$(myField,"C") in the Value
property with any data type.
> Can I expect MS might have a look at thses issues in time for the next
service pack?