Set SSRS Report Parameters Optional using IIF

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, 
       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.

Create a Report Parameter

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. 

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), "*", 
Set SSRS Report Parameters Optional using IIF Expression

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.

Add Filter to dataset

If you open the Set SSRS Report Parameters Optional using IIF condition preview, it shows the products belong to all the categories by default.

Report Preview

Uncheck the Null and provide the Category to view the products belonging to the Clothing category.

Set SSRS Report Parameters Optional using IIF Preview

Set SSRS Report Parameters Optional using IIF Method 2

Double-click the @Catgory Parameter and uncheck the Allow Null Value option.

Edit Parameter to remove Allow Null Values 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.

Add * wildcard to Set SSRS Report Parameters Optional using IIF

Change the Filter condition to the below image.

Change Filter Condition

Now, you can see the report.

Set SSRS Report Parameters Optional using IIF Condition Preview