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,
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
We have created another data set of distinct Class records so that we can use them as the parameter available values.
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 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.
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 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.
Let me uncheck the Allow Null Values option under the Report Parameter general properties.
Now you can see no Null option to choose from Class Parameter.
