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. Writing the below code inside the SSMS shows the data set that we use SSRS Null Parameters using Query level for this example.

Here, we used the where condition with @Class to create a parameter. 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

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.

Report Preview

Double-click or right-click and choose the @Country Parameter Properties option, which 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.

Empty Report

Double-click on the primary data set, and use the ISNULL function to replace Nulls with empty. It means to replace the old query with the below shown one 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

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

SSRS Null Parameters using Query level Preview

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

All SSRS Null Parameters using Query Level