You have a requirement where users want to run a SQL Server Reporting Services (SSRS) report without passing any parameter values. In this case the SSRS report should show all the records, but the users also want an option to filter the data, if required. In SSRS terms the users want an Optional Query Parameter; which filters the dataset if a value is provided. If a value is not provided, then the query will return all records. So how do you create an Optional Query Parameter in SQL Server Reporting Services?
Problem
You have a requirement where users want to run a SQL Server Reporting Services (SSRS) report without passing any parameter values. In this case the SSRS report should show all the records, but the users also want an option to filter the data, if required. In SSRS terms the users want an Optional Query Parameter; which filters the dataset if a value is provided. If a value is not provided, then the query will return all records. So how do you create an Optional Query Parameter in SQL Server Reporting Services?
Solution
In SQL Server Reporting Services (SSRS) we can't enable/disable an SSRS report parameter; if we have created a parameter then the user has to provide a value for the parameter. Optional Query Parameters are not available natively in SSRS, but we can create a workaround to create a report.
If a report has an Optional Query Parameter and the user doesn't provide a parameter value and then previews the report, the report will show all of the data. If the user wants to filter the data then the user can filter by entering the parameter value. Optional Parameters can use any operators such as "=", "<", ">", "<>", “IN”, “BETWEEN”, etc.
I have prepared this tip in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use the AdventureworksDW2008R2 database. You can download the database from codeplex.
Steps to create an SSRS Optional Query Parameter
Step 1 - Create an SSRS Data Source Connection
I am creating an embedded data source connection to the AdventureworksDW2008R2 database. You can refer to the below image to create a new data source connection.
The Data Source connection is successfully created. You can check your new data source connection under Data Sources in the Report Data window.
Step 2 - Create an SSRS Dataset
I am creating a new Dataset; this dataset has one query parameter called @Product. This dataset returns the Productkey and EnglishProductName based on value supplied for the @Product query parameter.
SQL Server Reporting Services Dataset Query
Select ProductKey,ProductEnglishName from DimProduct where Productkey = @Product
You can refer to the image below to create a new dataset.
The dataset is successfully created. The @Product report parameter has been automatically created by SSRS. The @Product parameter is not using any query for available values, so it will allow the user to enter the parameter value at report run time. You can refer to the below image.
Step 3 - Add an SSRS Tablix for Data Validation
I am inserting a Tablix for data viewing and validation purposes. In the Tablix I have chosen both the data fields from the main dataset. You can refer to the image below.
If the report has a parameter then the user has to enter the parameter value to view the report. If the user doesn't enter the parameter value and tries to view the report then the report will not run and will throw error. You can refer to the image below.
If the user wants to ignore this error then this parameter should be allowed to accept a blank value.
I am entering a parameter value and viewing the report. As you can see from the image below, our report is showing data for a supplied parameter value.
Step 4 - SSRS Optional Query Parameter Implementation
In this step we will configure the @Product parameter as an Optional Query parameter.
Right click on the @Product parameter and click on "Parameter Properties", the Report Parameter Properties window will open. You can refer to the image below.
On the "General" tab, check on the "Allow null value" and "Allow blank value" options. This change will allow the user to pass a NULL value for the @Product parameter. Your changes should look like the image below.
Click on the "Default Values" tab and then select the "Specific Values" radio button. To add a default value click the "Add" button. It will add "NULL" as a default value for the @Product parameter. Your changes should look like the image below.
Right click on "Main" Dataset and click on "Dataset Properties", it will open Dataset Properties window. We have to make the below changes in the Query.
SELECT ProductKey, EnglishProductName FROM DimProduct WHERE ProductKey = @Product OR @Product IS NULL
You can refer to the image below.
We have made all necessary changes, let's preview the report. Once we clicked on the Preview button, the report started running automatically without asking for a parameter value, by default it is accepting NULL as parameter value because we added NULL as the default parameter value. You can see from the image below the report is returning all the records.
If the user wants to filter the data then they uncheck the NULL checkbox and then enter the parameter value. As you can see from the image below, the report is showing only records for the supplied parameter value. We have successfully implemented an Optional Query Parameter in SQL Server Reporting Services. But is that it? Can we do more to help our users?
Pass a Blank Parameter in SQL Server Reporting Services
Many times a user doesn't want to use the NULL check box, because they have to check and uncheck the NULL checkbox to pass the parameter value which wastes time. The scenario our users want is if they do not pass a parameter then the report should return all the records and if a user enters a parameter then filter the data.
This report also accepts a blank value for the @Product parameter, now it will not throw an error if the parameter value is not provided. You can refer to image below.
Let's implement the solution for the above requirement. This can be easily achieved, by following these steps:
- Unselect "Allow null value" for @Product parameter. You can refer Step 4.
- Right click on "Main" dataset and click on Dataset Properties then click on expression button under Parameters Tab. Refer to the image below.
In the expression window write the following expression.
=IIF(Parameters!Product.Value ="",Nothing,Parameters!Product.Value)
You can refer to the image below.
Let's run the report without the parameter value. As you can see from the image below the report returns all records.
Let's provide one parameter value and preview the report. As you can see from the image below the report is showing data for the supplied parameter value.
