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. Writing the below code inside the SSMS shows the data set we use for this hide SSRS Table Nulls example.
The Sql query that we used for 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 report of Customer sales and formatted the font and colors. 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.
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.
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.
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)
Click OK to close the expression window. Next, please check the final report for the formatting and size adjustment.
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.
