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?

Dichlorodifluoromethane

Do you have any decimal values stored in the column kWp ? – Harlequin

Yes - they're all decimal values - roughly in the range 0 - 100 – Dichlorodifluoromethane

Then you need to cast the values into int SUM(iif(Fields!ProjectTypeID.Value = 2,CINT(Fields!FieldkWp.Value),0) – Harlequin

Won't this throw away the fractional part, though? These are significant, and I don't want to round down all the 3.4 to 3, etc. – Dichlorodifluoromethane

So better convert every thing to decimal SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!FieldkWp.Value),0.00) – Harlequin

Great - thanks! Can you make this into an answer so I can mark it answered? – Dichlorodifluoromethane

Answer 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))

Harlequin

Answer 2

I had similar problem, this worked for me:

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

zb

Guido

Answer 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.

Compeer

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. – Dichlorodifluoromethane

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

Answer 4

To get the conditional sum you can try this expression

=sum(IIf(Fields!balance.Value > 0,(Fields!balance.Value),0))

It sums only positive numbers otherwise it adds 0 to the total, you can do it wise versa.

Letterpress