SSRS expression - If null value then return 0 instead of blank

Question

I've seen a few examples but I just can't figure it out for my case. My expressions sums all values from field Total from the dataset AutoDeliveryCount. I need to reference the dataset since I'm using a few Total fields in my report. If the stored procedure returns null, how can I have my expression return 0 instead of a blank?

=Sum(Fields!Total.Value, "AutoDeliveryCount")

Benitez

Answer 1

=IIf(IsNothing(Sum(Fields!Total.Value, "AutoDeliveryCount"))=True, 0, Sum(Fields!Total.Value, "AutoDeliveryCount"))

Nisus

does this cause SSRS to evaluate the SUM() twice? – Thoughtful

IsNothing returns Boolean. Thus, there is no need compare it with True. =IIf(IsNothing(Sum(Fields!Total.Value, "AutoDeliveryCount")), 0, Sum(Fields!Total.Value, "AutoDeliveryCount")) – Ardie

Answer 2

Simply:

=Sum(Fields!Total.Value, "AutoDeliveryCount") + 0

Mediocre

This the real MVP. Forget adding yet another annoying nested IIF wrapping around everything... just throw + 0 at the end. Brilliant. – Jerkwater

Yep, less verbose and equally functional. – Mediocre

Answer 3

If you want to show 0 in integer column. Write Round(Fields!yourfield.Value ,2) to display as 0.00 . If your field is returning empty.

Inexact

Answer 4

From the marked solution:
=IIf(IsNothing(Sum(Fields!Total.Value, "AutoDeliveryCount"))=True, 0, Sum(Fields!Total.Value, "AutoDeliveryCount"))

1. IsNothing() always returns true or false and also IIF() always delivers true or false. So "=True" is redundant. IIf(IsNothing(Sum(Fields!Total.Value, "AutoDeliveryCount")), 0, ... works exactly the same way.

2. What if Sum(Fields!Total.Value, "AutoDeliveryCount") returns 0? Since IsNothing() and 0 are being treated the same way 0 will be returned in both cases. It does not make any difference whether a field is empty or does contain the nubmer 0. How would you handle the difference between 0 and nothing?

Floccose

Answer 5

Another option may be to use the Format property for the cell concerned. For example if you are displaying an Integer you could format it as:

#,##0

This will display 0 if the value is null/empty.

Dost

This didn't work for me. Which SSRS version supports this? – Benia

@Rashmi - SQL Server 2012. I developed the report using Visual Studio 2013 Update 5 with Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 installed. – Dost

Answer 6

I tried this formula in 2017 and it worked for me:

=IIF(Sum(Fields!Total.Value)=Nothing, 0, Sum(Fields!Total.Value, "AutoDeliveryCount"))

Bobolink