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. Right-click on the Datasets folder to create a new DataSet. Writing the below code inside the SSMS shows the data set that we use for this example.
The Sql query that we used for this SSRS example is:
SELECT Prod.EnglishProductName AS ProductName, Prod.Color, Prod.Class,
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
How to Allow Nulls as SSRS report Parameter value?
We have created another data set of distinct Class records so that we can use them as the parameter available values. The query for this ClassDSet Dataset is:
SELECT DISTINCT Class FROM DimProduct
We have designed a simple table report of product sales and formatted the font and colors.
The report preview shows a few empty rows under the Class column.
Right-click on the Parameters folder and choose the Add Parameter option to open the following window. Add proper name and prompt text. Remember to checkmark the Allow Null Value option to accept nulls as SSRS Report Parameter.
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.
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 using the drop down list. Next, we keep the default equals operator and clicked on the ƒx button beside the Value to write an expression.
Double-click the class parameter to add. This filter restricts the report preview to displaying records with class names equal to @class parameter values(user-chosen).
Now you can see Null Parameter, displaying or allowing all the SSRS table report records whose class value is empty or Null.
Let me choose the Not Null value.
If you uncheck the Allow Null Values option under the Report Parameter general properties (refer fourth image before this). You can’t see the Null option to choose from the Class Parameter drop down list.