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

score 20 · Answer 1 · answered Aug 30 '19 at 17:19

20

Simply:

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

answered Aug 30 '19 at 17:19

Christian Felipe

333
2
13

This the real MVP. Forget adding yet another annoying nested IIF wrapping around everything... just throw + 0 at the end. Brilliant. – Don Cheadle May 19 '21 at 22:29
Yep, less verbose and equally functional. – Christian Felipe May 21 '21 at 16:59

score 18 · Accepted Answer · edited Aug 17 '15 at 18:33

18

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

edited Aug 17 '15 at 18:33

Nathan A

11,059
4
47
63

answered Jan 30 '15 at 20:48

Jeffrey Van Laethem

2,601
1
20
30

1

does this cause SSRS to evaluate the SUM() twice? – AJ. Dec 15 '20 at 13:25
[IsNothing](https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.information.isnothing?view=net-5.0) returns Boolean. Thus, there is no need compare it with True. `=IIf(IsNothing(Sum(Fields!Total.Value, "AutoDeliveryCount")), 0, Sum(Fields!Total.Value, "AutoDeliveryCount"))` – Fenix Mar 24 '21 at 19:32

score 4 · Answer 3 · answered Nov 09 '17 at 09:08

4

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.

answered Nov 09 '17 at 09:08

Raghavendra Aditya Badida

41
1

score 1 · Answer 4 · answered Jan 17 '20 at 08:09

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

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.
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?

score 0 · Answer 5 · answered Jun 24 '16 at 05:16

0

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.

answered Jun 24 '16 at 05:16

user2444499

757
8
14

This didn't work for me. Which SSRS version supports this? – Rashmi Pandit Jul 28 '16 at 00:03
1

@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. – user2444499 Aug 04 '16 at 23:36

score 0 · Answer 6 · edited Nov 30 '18 at 00:11

0

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

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

edited Nov 30 '18 at 00:11

Abhinav Sood

799
6
23

answered Nov 29 '18 at 21:37

Jeff Power

1

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

6 Answers6