SSRS sum based on not-null condition

Question

I have a report I am building that I am summing a column with the total minutes listed in it then dividing it by 60 to get number of hours like so.

=Sum(Fields!designtimeValue.Value) / 60

But I have a condition where I want to weed out rows that contain other data in another column

Its laid out like so

Submittal_date   Submittal_returned   Design_Hours
   12/01/2014                             180
   12/01/2014        12/02/2014           240
   12/01/2014                             60

So I want to do something like =Sum(IIF(Fields!sumbittal_returned.Value="NULL",Fields!design_hours.Value)) / 60

But it is throwing an error the sum iff does not allow that number of exceptions

I then need to take it a step further And be able to take the sum of design total if submittal_returned = NULL total then for any instance submittal_returned = Not-NULL add 120 minutes

Thanks

Edit : format code

Tab Alleman Tab Alleman · Accepted Answer · 2014-12-01T21:11:54

IIF takes 3 arguments. A condition, a true result, and a false result. You have to supply all three.

This would be the correct syntax:

=Sum(IIF(IsNothing(Fields!submittal_returned.Value),Fields!design_hours.Value,0)) / 60

Although that condition will only be true if submittal_returned actually contained the string "NULL". If it's actually NULL (not a string), you would need test with the IsNothing() function.

Not sure I understand your last sentence, maybe you mean this?:

=Sum(IIF(IsNothing(Fields!submittal_returned.Value),Fields!design_hours.Value,Fields!design_hours.Value + 120)) / 60

SSRS sum based on not-null condition

1 Answers