SSRS Null Parameters using Query

This SSRS article shows how to work at the query level to use the NULL parameters in a report. If you can edit the Query, this approach is much easier than writing expressions.

Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the data set that we use SSRS Null Parameters using Query level 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
WHERE Prod.Class = @Class
GROUP BY Prod.EnglishProductName, Prod.Color, Prod.Class

We used the where condition with @Class to create a parameter.

SSRS Null Parameters using Query

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

Once we type the L as the parameter value, the report preview shows rows belonging to the L Class. However, you can’t get the records that belong to Nulls.

Double-click or right-click and choose the @Country Parameter Properties option will open the Report Parameter Properties window. Checkmark Allow Null Value option.

Checkmark Allow Null Value to allow SSRS Null Parameters at Query Level

Now, we can check NULLs in the report preview, but we don’t see the result of the NULL class. For this, we have to tweak the Query.

Double-click on the primary data set, and use the ISNULL function to replace Nulls with empty. And click OK.

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
WHERE ISNULL(Prod.Class, '') = ISNULL(@Class, '') 
GROUP BY Prod.EnglishProductName, Prod.Color, Prod.Class

DataSet of SSRS Null Parameters using Query Level

In SSRS Null Parameters using Query level preview, Now you can see the products whose Class is Null.

And if you type the L and uncheck the Null, you can see the products belonging to the L class.