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. Writing the below code inside the SSMS shows the data set we use for this SSRS Report Parameters Optional using IIF example.
The Sql query that we used in this 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 the 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
Within the Report Data window, double-click the @Catgory Parameter to open the window shown in the first image. It opens the Report Parameter Properties window and within the General tab, uncheck the Allow Null Value option.
Go to the Default Values, and select the Specify Values. Next, click the add button to add the default value to the @Category Parameter. Here, enter * wildcard to display all the records.
Change the Dataset Filter condition in the third image with the below expression. Replace the complete IIF expression with the below code.
=Parameters!Category.Value
Now, you can see the report.
