Allow Nulls as SSRS Report Parameter

This SSRS article shows how to allow NULLs as a parameter value in a report. By default, parameters won’t accept null values, and to make it happen, we have to checkmark allow null values option.

For instance, if you have a product class column and each product represents a different class, there are few rows with no class name(NULLS). If you want to use the Class column as a report Parameter, by default, reporting services won’t display the rows with NULL values.

How to Allow Nulls as SSRS report Parameter value?

Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the data set that we use for this example.


The Sql query that we used above SSRS example is:

SELECT Prod.EnglishProductName AS ProductName, 
       SUM(Fact.OrderQuantity) AS Orders, 
       SUM(Fact.TotalProductCost) AS ProductCost, 
       SUM(Fact.SalesAmount) AS Sales, 
       SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
GROUP BY Prod.EnglishProductName, Prod.Color, Prod.Class

We have created another data set of distinct Class records so that we can use them as the parameter available values.

DataSet for Available Values

We have designed a simple table report of product sales and formatted the font and colors.

Simple Table Report

The report preview shows a few empty rows under the Class column.

Report With a few empty rows

Right-click on the Parameters folder and choose Add Parameter option to open the following window. Add proper name and prompt text. Remember to checkmark Allow Null Value option to accept nulls as SSRS Report Parameter.

Allow Nulls as SSRS report Parameter value 5

Go to the available values tab and choose to get values from a query option. Next, select the data set we created earlier to get the distinct class names and choose the class name as a value field and label field.

Choose DataSet for available values Lakeland value fields

Double-click on the primary data set, go to the filters tab and click the add button to add a filter. Here, we have chosen the class column as an expression and clicked on the ƒx button beside the Value to write an expression.

Add Filter to the main data Set

Double-click the class parameter to add.

Allow Nulls as SSRS report Parameter value

This filter restricts the report preview to displaying records with class names equal to @class parameter values(user-chosen).

Filter to Allow Nulls as SSRS report Parameter value

Now you can see Null Parameter, displaying or allowing all the SSRS table report records whose class value is empty or Null.

Allow Nulls as SSRS report Parameter value Preview

Let me choose the Not Null value.

Allow Nulls as SSRS report Parameter value

Let me uncheck the Allow Null Values option under the Report Parameter general properties.

Uncheck Allow Null Values option in SSRS report Parameter value

Now you can see no Null option to choose from Class Parameter.

No Null