Sum returns error with IIF of field values.

Question

Post reply

Sum returns error with IIF of field values.

Ronald R Mayfield

SSC-Addicted

Points: 446
More actions
November 6, 2007 at 1:06 pm

#183609

I want to do a conditional summation of a field. The value of the InSpec field is either Yes or No. The value of BBLs is a Decimal. Using the following expression
=sum(iif(fields!InSpec.Value ="No",Fields!BBLs.Value,0))
I wanted to add total BBLs for all lines not "InSpec".
When I do this I get the following error
[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘txtBBLsOut’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
We tried changing the value of 0 to 0.0 and 0.00001, but still get the same error. We changed the value of BBLs returned from the database to Int32, but I still get this error.
Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

Ronald,

See if this will work -- try an explicit conversion of the second value to Decimal, something like:

=sum(iif(fields!InSpec.Value ="No",Fields!BBLs.Value,CDec(0)))

Scott

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

You can as well modify your source stored procedure or query to return 0 for No and 1 for yes and that way you may not be required to CAST your datatype inside the expression.

Prasad Bhogadi
www.inforaise.com

maddogs SSCrazy Points: 2474 More actions · Answer 3

Or just use a case statement in the SP against the InSpec column value to make the BBLs column value what it is or zero using same logic as the IIF statement, then don't worry about the IIF statement at all in the report expression - just a straight sum of that field. If you need the original BBLs value to not be zero for other purposes then just return a new column in the SP result set specifically for the report field in question here. I always find it's simpler to let the DB engine to the majority of the work for SSRS reporting and have the reports just spit out the pre-calculated results. If you have to add a few additional and\or repeated columns to the result set then so be it.

maddog