Search code examples
reporting-servicesssrs-2016

SSRS Expression - Sum based on condition, unable to get it to return zero if sum returns null

I am creating an expression in SSRS that calculates the number of days where the conditions are met.

The Expression I am currently using:

=Sum(IIF(Fields!Charge.Value = "Chargeable" AND Fields!Progress.Value = "Booked", Fields!Number_of_Days.Value, Nothing), "DataSet1")

I have tried using some suggestions such as 'IsNothing' but trying to copy the same sort of formatting as other answers has given me a "#ERROR" when the report is run.

But, this could be because I did the formatting wrong when attempting this.

Cover Page Design View:

Cover Page Design

Running The Report:

Report Run

As you can see, the values that are coming up are 100% correct - have double checked. Just want zeros to show instead of blanks. The reason there are zeros showing up in the screenshot is because in the the data displayed below this (actual report) has zeros showing up for the number of days column. I cannot show this data as it is sensitive.

I need to use the exact same expression, but be able to display zero if it returns null or zero.

Any help is appreciated, thanks.

Solution

  • Temporary fix that should work:

    =ROUND(Sum( IIF(Fields!Charge.Value = "Chargeable" AND Fields!Progress.Value = "Booked", Fields!Number_of_Days.Value, Nothing) , "DataSet1"), 2)

    I don't know how this will affect the data, but it's a nice workaround and will get it to display 0.00.