Report Builder – Replace Empty Table Values with a zero

If you are creating a highly customised report you may come across a situation where your dataset may not return any results.

If this happens your table with render with blank.  I have come across a solution to enable these empty / null values to be replaced with zeros if the dataset query returns no results

Consider the following example report

The Total column uses the following query to sum the number of records from the dataset results where the prioritycodename = 2

 

=Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0))

If the dataset returns no results this field will be blank.  On an SLA report this is not desirable.  I would prefer to see a 0 instead.  To achieve this I used the following query

 

=IIF(ISNOTHING(sum(Fields!prioritycodename.Value)),0,Sum(IIF(Fields!prioritycodename.Value = "2", 1, 0)))

 

In English the above query reads as,

If sum of prioritycodename equals zero, display a zero in the table, otherwise count the instances of prioritycodename where prioritycode = 2

I think you will agree the report looks a lot better with zeros than blank rows / lines