This SSRS article shows how to set report parameters optional using IIF condition and display all the records when the user provides no parameter value. Therefore, it is very important to keep the report parameter value optional.
Right-Click on the Datasets folder to create a new DataSet. The screenshot below shows the data set we use for this SSRS Report Parameters Optional using IIF example.
The Sql query that we used above SSRS example is:
SELECT Cat.[EnglishProductCategoryName] AS Category,
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName,
Prod.Color,
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
INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
Set SSRS Report Parameters Optional using IIF
Right-click on the Parameters folder and choose Add Parameter option to open the following window. First, add Category as the name and Enter Category as the prompt text. Next, checkmark the Allow Null Values option.
We have designed a simple table report of sales by product category and formatted the font and colors.
The screenshot below shows the available columns in both DataSets, the newly created @Category Parameter, and the table report.
Double-click on the CatDSet DataSet, go to the filters tab and click the add button to add a filter. Here, we have chosen the category column as an expression and clicked on the ƒx button beside the Value to write an expression.
=IIf(IsNothing(Parameters!Category.Value), "*", Parameters!Category.Value)
The above iif statement checks if anything is inside a parameter value; if true, use it as a filter condition. Otherwise, use * wildcard as the filter condition to select all the product categories.
Click OK to close the window.
If you open the Set SSRS Report Parameters Optional using IIF condition preview, it shows the products belong to all the categories by default.
Uncheck the Null and provide the Category to view the products belonging to the Clothing category.
Set SSRS Report Parameters Optional using IIF Method 2
Double-click the @Catgory Parameter and uncheck the Allow Null Value option.
Go to the Default Values, and select the Specify Values. Next, click add button to add the default value to the @Category Parameter. Here, enter * wildcard to display all the records.
Change the Filter condition to the below image.
Now, you can see the report.
