Hide SSRS Table Nulls

This SSRS article shows an example of how to hide table rows with Nulls or empty values. In real-time, it is annoying to see empty rows in a table report, and using a simple expression with the IIF condition will hide those Nulls.

To work with this, right-click on the Datasets folder to create a new DataSet. The screenshot below shows the data set we use for this hide SSRS 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 report of Customer sales and formatted the font and colors.

Customer Sales Table

If you observe the report review, there are a lot of rows whose Middle Name is empty or NULL. Here, our job is to hide the customers whose Middle Name is Null.

Report Preview

How to Hide SSRS Table Nulls?

Select the details row and right-click on it to choose the Row Visibility option from the context menu.

Select Row Visibility option

It will open the Row Visibility window. First, change the display option from default Show to Show or Hide based on the expression option. Next, click the ƒx button to write an expression.

Visibility Option to Hide SSRS Table Nulls

Here, we used the IIF condition with IsNothing() function to check whether the Middle name value is Null. If not Null, display the row; otherwise, hide the complete row.

=IIf(IsNothing(Fields!MiddleName.Value), True, False)
Expression to Hide SSRS Table Nulls

And the final report is.

Hide SSRS Table Nulls Final Report

Now, if you go to the SSRS hide nulls table report preview, you won’t see any rows with NULLs or customers with empty Middle Names.

Hide SSRS Table Nulls Report Preview