Replace SSRS Table Nulls

This SSRS article shows how to replace the Nulls or empty values in a table report with custom values or text with an example. In real-time, it is better to display something other than empty rows. We can use a simple IIF condition with an IsNothing function to replace those Nulls.

Right-click on the Datasets folder to create a new DataSet. Writting the SSRS below code inside the SSMS shows the data set we use for the replace table nulls example.

The Sql query that we used in this SSRS example is:

SELECT [FirstName],[MiddleName],[LastName],[Gender],[EmailAddress]
	  ,SUM([SalesAmount]) AS Sales
      ,SUM([TaxAmt]) AS Tax
  FROM [DimCustomer]
  JOIN FactInternetSales ON DimCustomer.CustomerKey  = FactInternetSales.CustomerKey
  GROUP BY [FirstName],[MiddleName],[LastName],[Gender],[EmailAddress]

We have designed a simple table of Customer sales reports and formatted the font and colors. You notice a few empty or NULL Middle names in the report review. Our job is to replace those Nulls with a custom Middle Name.

Report preview

Replace SSRS Table Nulls

Right-click the Middle Name to choose the Expression option from the context menu. It will open the Expression window.

Here, we used the IIF condition with IsNothing() function to check whether the Middle name value is Null or not. If it is Null, replace it with Hello text: otherwise, display the original Middle name.

IIF(IsNothing(Fields!MiddleName.Value), "Hello", Fields!MiddleName.Value)
Expression to Replace SSRS Table Nulls

Now, if you go to the table report preview, you can see the Nulls replaced with the Hello message.

Replace SSRS Table Nulls Report Preview