How to Display Blanks as Zeros in a SQL SSRS Report

Home > Blogs > SQL Server > How to Display Blanks as Zeros in a SSRS Report

How to Display Blanks as Zeros in a SSRS Report

Added by Peter Avila November 12, 2014

Some of the cells in this SSRS matrix report are blank:

To display zeros instead, we will use two functions:

IsNothing(<Val2Check>) returns a True of Val2Check is Null; otherwise, it returns False

1	IsNothing(<Val2Check>) returns a True of Val2Check is Null; otherwise, it returns False

IIF(<Test>, <IfTrue>, <IfFalse>) return <IfTrue> if <Test> is True; otherwise, it returns <IfFalse>

1	IIF(<Test>, <IfTrue>, <IfFalse>) return <IfTrue> if <Test> is True; otherwise, it returns <IfFalse>

In the design of the matrix, right-click on the textbox containing the values in question, then select Expression… to edit the expression underlying the textbox.

Type the following expression in the Expression dialog:

The expression says that if SUM(Fields!SubTotal.Value) is Null (the IsNothing() function that it is in returns a True if it is Null and a Fales if it isn’t), then a zero is returned by the expression; otherwise the SUM(Fields!SubTotal.Value) is used.

Click OK and preview the report again. Notice that zeros are now present in what used to be the blank cells.