sum text field

[Chumley40]

I have a text field that comes across with multiple values. I am putting these into a matrix which filters to receive only two specific fieldnames:
for each field there is a value. the value is stored as varchar in sql. The value is always null or has a number in it for these two fieldnames. so in the matrix I use:
=sum(int(Fields!Value.Value))

The idea is to convert the value to an integer so it will sum

but I still get an error when I run the report. Any ideas?

Thanks!

 

[ousoonerjoe]

What about specifying a SELECT statement that pulls the values you need? For the the numeric field, simply CAST as INT:

SELECT field1, [b]CAST(ISNULL(field2, 0) AS INT) AS field2[/b] FROM Table1 WHERE....

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Chumley40]

Do you mean in sql? I really wanted to do it in reporting services, if it is possible?

 

[paulkeys]

think you should be using =sum(CInt(Fields!Value.Value)) instead, as the int() function int() you've used will extract the integer part of a number as opposed to converting to integer - CInt()