This SSRS article shows how to work at the query level to keep or set the report parameter value as optional. It is the quickest approach; if you can edit the query, use this one.
Right-Click on the Datasets folder to create a new DataSet. The below SSRS screenshot shows the data set that we use for this Optional Report Parameter using a query example.
The Sql query that we used above SSRS example is:
SELECT Geo.EnglishCountryRegionName AS Country,
Geo.StateProvinceName AS State,
Geo.City,
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 DimCustomer
ON DimCustomer.CustomerKey = Fact.CustomerKey
JOIN DimGeography AS Geo ON DimCustomer.GeographyKey = Geo.GeographyKey
WHERE Geo.EnglishCountryRegionName = @Country
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City
We used the where condition with @Country to create a parameter.
Optional SSRS Report Parameter using a Query
We have designed a simple table report of sales by state and formatted the font and colors.
To keep the report parameter optional, we need a group by column. So, under the Row group, click the down arrow beside the details and select add group and parent group.
Let me choose the group by option as a Country column. Now, you can see the Country under the Row Groups section.
If you see the report preview, entering the United States shows all the sales belong to the USA. However, it offers a blank report if you don’t provide any Country Name.
Double-click on the CountrySalesDSet primary DataSet, and use the SQL IS NULL function.
SELECT Geo.EnglishCountryRegionName AS Country,
Geo.StateProvinceName AS State,
Geo.City,
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 DimCustomer
ON DimCustomer.CustomerKey = Fact.CustomerKey
JOIN DimGeography AS Geo ON DimCustomer.GeographyKey = Geo.GeographyKey
WHERE Geo.EnglishCountryRegionName = @Country
OR @Country IS NULL
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City
Next, double-click the report parameter and checkmark the Allow Null Values option.
Now, if you look at the optional SSRS report parameter using a Query preview, NULL is the default check marked and displays sales belonging to all the countries.
Uncheck the Null and provide the Country name to view the products belonging to that Country.
