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?

Yes - they're all decimal values - roughly in the range 0 - 100 — Daniel Neal, Jun 14 '12 at 10:10
Then you need to cast the values into int SUM(iif(Fields!ProjectTypeID.Value = 2,CINT(Fields!FieldkWp.Value),0) — praveen, Jun 14 '12 at 10:40
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. — Daniel Neal, Jun 14 '12 at 10:52
So better convert every thing to decimal SUM(iif(Fields!ProjectTypeID.Value = 2,cdbl(Fields!FieldkWp.Value),0.00) — praveen, Jun 14 '12 at 10:56
Great - thanks! Can you make this into an answer so I can mark it answered? — Daniel Neal, Jun 14 '12 at 10:59

score 31 · Accepted Answer · edited Jan 14 '14 at 16:23

31

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

edited Jan 14 '14 at 16:23

flash

388
4
15

answered Jun 14 '12 at 14:29

praveen

12,083
1
41
49

score 2 · Answer 2 · answered May 14 '18 at 14:49

2

I had similar problem, this worked for me:

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

zb

answered May 14 '18 at 14:49

Zalek Bloom

551
6
13

score 1 · Answer 3 · edited Feb 08 '16 at 13:31

1

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.

edited Feb 08 '16 at 13:31

Pedram

6,256
10
65
87

answered Jun 14 '12 at 10:15

Venaikat

197
2
5
20

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. – Daniel Neal Jun 14 '12 at 10:55
I tried and the result was the same as =SUM(Fields!kWp.Value) – HEDMON Aug 16 '19 at 07:17

score 0 · Answer 4 · edited Apr 29 '17 at 13:35

0

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.

edited Apr 29 '17 at 13:35

pacholik

8,607
9
43
55

answered Apr 29 '17 at 13:08

Ghayour Khan

1
2

Carrying out a SUMIF like operation using SQL Server Report Builder

4 Answers4

Linked