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. The SSRS screenshot below shows the data set we use for the replace table nulls example.
The Sql query that we used above 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.
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)
Now, if you go to the table report preview, you can see the Nulls replaced with the Hello message.
