Carrying out a SUMIF like operation using SQL Server Report Builder

Question

I'm trying to produce a conditional sum in SQL Server Report Builder 3.0.

My expression looks like this:

=Sum(Iif(Fields!ProjectTypeID.Value=2,Fields!kWp.Value,0))

I'd hoped that this expression would produce a sum of the kWp of all projects of type 2.

Unfortunately, it is not to be. And I can't seem to work out why. It just returns a 0 result, even though I know that there are non-zero values in the kWp column, and the column does not contain nulls.

A colleague did manage to get a positive result by replacing the

Fields!kWp.Value 

with

1 * Fields!kWp.Value

But we have no idea why this works, and therefore, can't really trust the answer.

How can I get this conditional sum to behave itself?

Solution 1

The data type of the column 'kWp' is Decimal so you need to either convert the default value to 0.00 or cast the column to double

 SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!kWp.Value),0.00))

Solution 2

I had similar problem, this worked for me:

=Sum(iif(Fields!date_break.Value = "0001-01-01",Fields!brkr_fee.Value, nothing))

zb

Solution 3

To get the sum of the kWp of all projects of type 2, the expression is as follows,

=IIf(Fields!ProjectTypeID.Value=2,sum(Fields!kWp.Value),0) 

I hope this will help u.

Comments

[Daniel Neal]

Thanks for your answer. Are you sure this will only sum the values for which this is true? Written this way round, it looks like the if will be performed first - and then return the sum of all the fields.

[HEDMON]

I tried and the result was the same as =SUM(Fields!kWp.Value)