SQL Server Reporting Services Reports With Optional Query Parameters

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.

Set Select All as Default for Multi-Value Report Parameters in SQL Server Reporting Services

Most SQL Server Reporting Services (SSRS) reports contain multi-value report parameters and sometimes there be a requirement to set "Select All" as the default. There is not a simple way to set "Select All" as the default for an SSRS report, but in this tip I will demonstrate how to set "Select All" as the default parameter value.

Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report with parameters. I have prepared this tip in such a way that a SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Problem

Most SQL Server Reporting Services (SSRS) reports contain multi-value report parameters and sometimes there be a requirement to set "Select All" as the default. There is not a simple way to set "Select All" as the default for an SSRS report, but in this tip I will demonstrate how to set "Select All" as the default parameter value.

Solution

Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report with parameters. I have prepared this tip in such a way that a SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Let's understand the problem with a simple example

I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let's create three datasets for the sample report.

Report Dataset: This dataset will be used for the report body and it has two query parameters @ManufacturePeriodand @Size. Both query parameters can accept multiple values.

SELECT DaysToManufacture, Productkey, EnglishProductName,Size 
FROM DimProduct
WHERE DaysToManufacture In(@ManufacturePeriod)  and Size IN(@Size) 
ORDER BY DaysToManufacture

ManufacturePeriod Dataset: This dataset will be used to get a list of values for the ManufacturePeriod report parameter. This dataset doesn't return any NULL value rows.

SELECT Distinct DaysToManufacture 
FROM DimProduct 
ORDER BY DaysToManufacture

ProductSize Dataset: This dataset will be used to get a list of values for the Size report parameter. This dataset does return a NULL value row.

SELECT Distinct Size
FROM DimProduct 
ORDER BY Size

Because Report Dataset has two query parameters @ManufacturePeriod and @Size, SSRS will automatically create two report parameters named as ManufacturePeriod and Size. After creating all three datasets, your Report Data pane must look like the below image.

Let's configure both of the report parameters to get a list of parameter values.

Right click on ManufacturePeriod report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of the ManufacturePeriod report parameter.

Right click on Size report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of the Size report parameter.

Let's add a Tablix for data viewing purposes. Please pull all the data fields from the Report Dataset into a Tablix. Your report must look like the below image after adding the Tablix.

Let's preview the report. As you can see from the below image, ManufacturePeriod and Size report parameters don't have default values. If you want to select all parameter values then you have to do it manually for each parameter.

Steps to make "Select All" as the default SSRS parameter value

As you know, our sample report has two report parameters ManufacturePeriod and Size. If I use the same query as the "Available Values" for the "Default Values" then SSRS will set "Select All" as the default value. Actually in this way we will provide every single "Available Value" as the "Default Value" that is why the "Select All" option is automatically checked.

I will set "Select All" as the default parameter value for both of the parameters.

Right click on ManufacturePeriod report parameter and click on Parameter Properties, it will open the Report Parameter Properties window. Click on the Default Values tab and make the below changes.

Right click on Size report parameter and click on Parameter Properties, it will open the Report Parameter Properties window. Click on the Default Values tab and make the below changes.

To set "Select All" as the default value, I have made similar changes for both report parameters. Let's preview the report. As you can see from the below image, by default all parameter values have been selected forManufacturePeriod parameter, but for Size the report parameter has nothing selected.

This is because the Size report parameter ProductSize dataset returns a NULL value and multi value parameters don't allow a NULL value.

There are two ways to handle this problem.

Option 1

You can exclude the NULL value if it is not required. In this case your report will not show all those records for which Size equals NULL. Let's modify the ProductSize dataset with the below query to exclude NULL values.

SELECT Distinct Size
FROM DimProduct 
WHERE Size IS NOT NULL
ORDER BY Size

After modify the dataset, preview the report. As you can see from the below image by default "Select All" has been checked for the Size report parameter.

Option 2

If you don't want to exclude the NULL value, then you can allow NULL value for multi value report parameters. In my previous tip, I described how this can be done.

Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services

Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULLvalue, so users can't filter the data for NULL values. Your requirements state a need to be able to filter the data forNULL values, so in this tip I will demonstrate how to allow NULL values in a multi value SSRS report parameter.

Problem

Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULLvalue, so users can't filter the data for NULL values. Your requirements state a need to be able to filter the data forNULL values, so in this tip I will demonstrate how to allow NULL values in a multi value SSRS report parameter.

Solution

This tip assumes that you have previous real time work experience building a simple SQL Server Reporting Services (SSRS) report with parameters.

I have prepared this tip in such a way that a beginner SSRS Developer can also understand the problem and implement the solution. In this tip we will first look at the problem with a simple example and later I will demonstrate the solution. I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Let's understand the problem with a simple example

I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let's create the below two datasets for the sample report.

ReportDataset: This dataset will be used for the report body.

SELECT     ProductKey, EnglishProductName, Size
FROM       DimProduct
WHERE      Size IN (@ProductSize)
ORDER BY   ProductKey

SizeDataset: This dataset will be used to get a list of values for the ProductSize report parameter. This dataset will return one NULL value. You can verify it.

SELECT     Distinct Size
FROM       DimProduct
ORDER BY   Size

ReportDataset has one query parameter named @ProductSize, so SSRS will automatically create one report parameter name as ProductSize. After creating both datasets, your Report Data pane will look like the below image.

Let's configure the ProductSize report parameter. Right click on the ProductSize report parameter and click on theParameter Properties, it will open the Report Parameter Properties window. Please make the below changes in the properties for the ProductSize report parameter.

Let's add a Tablix for data viewing purposes. Please pull all the data fields from ReportDataset into the Tablix. Your report will look like the below image after adding the Tablix.

Let's preview the report; As you can see from the below image, the ProductSize report parameter doesn't show NULLin the drop down list. So the report will not show products where the Size is NULL. You can verify this by previewing the report.

If you will try to enable "Allow null value" for the ProductSize report parameter, you will get this error message “A multi-value parameter cannot include null values” as shown below.

You get this error because if you perform any String or Mathematical operation with a NULL value then result will beNULL that is why NULL is not allowed in a multi value report parameter. How can we allow a NULL value in a multi value parameter? I will demonstrate the solution below.

Steps to allow NULL value in Multi Value Parameter in SSRS

SizeDataset is responsible to return the list of values for the ProductSize report parameter. We have to modifySizeDataset in such a way that NULL can be displayed in the Productsize report parameter dropdown list.

Modify the SizeDataset with the below query.

SELECT     Distinct ISNULL(Size,'NULL') as Size
FROM       DimProduct
ORDER BY   Size

I have used the ISNULL() function in the above query which will return NULL (a string value) if the NULL value is found for Size. Now SizeDataset will return NULL along with the other list of values in the ProductSize report parameter dropdown. You can refer to the below image.

We have to modify the ReportDataset in such a way that when NULL is passed from the ProductSize report parameter then ReportDataset must return the products for which Size is NULL.

Modify the ReportDataset with below query.

SELECT     ProductKey, EnglishProductName, Size
FROM       DimProduct
WHERE      ISNULL(Size,'NULL') IN (@ProductSize)
ORDER BY   ProductKey

I have used ISNULL() again in the where clause so the report parameter value can match the where clause value.

We have made all necessary changes, now let's preview the report. As you can see from the below image, our sample report is returning all the records with NULL values.

How to use a multi valued comma delimited input parameter for an SSRS report

You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a drop down list.  In this tip I will show how this can be completed with Reporting Services.

In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.

Problem

You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a dropdown list.  In this tip we show how this can be completed with Reporting Services.

Solution

In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.

This tip assumes that you have previous real world experience building a simple SSRS Report and Stored Procedure in SQL Server.

Steps to Implement Multi Valued User Input Report Parameter in SSRS

Step 1: Add Data Source

I have already created an embedded data source connection to the AdventureworksDW2008R2 database. You can refer to the below image.

Step 2: Add Dataset using a Stored Procedure

Generally when a user wants to filter report data for one or more parameter values, we create a dataset using the "IN" operator in the WHERE clause. This becomes a little complex when you try to pass comma separated values from a user input report parameter into a query parameter. This is because SSRS treats all comma separated values as a single string value and it passes all comma separated values as a single string value into the query parameter.

For example, suppose I enter 1,2 in the user input report parameter and view the report. SSRS passes '1,2' into the query parameter to be used in the WHERE clause.  If the column data type is set as a string then the report will execute successfully, but it will not return data.  This is because the user is expecting data for two different values '1'and '2', but in the query SSRS is passing '1,2' as a single string value and '1,2' will not match the data in the table. In the WHERE clause, if the column data type is integer then the report will fail, because SSRS will be passing string data into an integer data type column.

I created a Stored Procedure as shown below. The Stored Procedure has one input parameter, where we will be passing the user input report parameter value into the Stored Procedure. Each comma separated value will then be extracted from the stored procedure input parameter and will be stored in a temp table that will be used to narrow down the results.

Let's walk through an example to understand this. Suppose I have entered 1,2,3 in the report parameter.  The stored procedure will extract each comma separated value and insert it into temp table, therefore the temp table will have three records one for each extracted value. The temp tables first record will have a value of 1, the second record will have value of 2 and third record will have value of 3. Then at the end all the records in the temp table will be passed into the query which will return the requested data for our report.

Here is the stored procedure. I am using a table called TEMP to hold the extracted values, but you could also use a temporary table (#temp or some other name).  Another approach is to use XML to parse the data and join to your table.  Take a look at this tip for some ideas on how to do that.

/*CREATING A STORED PROCEDURE, WHICH HAS ONE INPUT PARAMETER*/
CREATE PROCEDURE MULTI_VALUED_USER_INPUT_PARAMETER(@PRODUCTKEY VARCHAR(MAX))
AS
BEGIN
  /*CHECKING IF TABLE EXISTS IN THE DATABASE*/
  IF OBJECT_ID('TEMP', 'U') IS NOT NULL 
    /*IF TABLE EXISTS THEN DROPPING AND RECREATING TABLE*/
    DROP TABLE TEMP

  CREATE TABLE TEMP (PRODUCTKEY VARCHAR(MAX))

  /*INSERTING EACH COMMA SEPERATED VALUE INTO TEMP TABLE*/
  WHILE CHARINDEX(',',@PRODUCTKEY)<>0
  BEGIN
    INSERT INTO TEMP VALUES((SELECT LEFT(@PRODUCTKEY, CHARINDEX(',',@PRODUCTKEY)-1)))
    SET @PRODUCTKEY=(SELECT RIGHT(@PRODUCTKEY,LEN(@PRODUCTKEY)-CHARINDEX(',',@PRODUCTKEY)))
  END

  /*QUERY RETURNS PRODUCTKEY AND ENGLISHPRODUCTNAME BASED ON VALUE PROVIDED IN REPORT PARAMETER IN SSRS*/
  SELECT Productkey, EnglishProductName FROM DIMPRODUCT WHERE PRODUCTKEY IN(SELECT PRODUCTKEY FROM TEMP)

  /*DROPPING THE TEMP TABLE*/
  DROP TABLE TEMP
END

I am creating a new dataset using a MULTI_VALUED_USER_INPUT_PARAMETER stored procedure; it returns two data fields Productkey and EnglishProductName. This dataset has one @PRODUCTKEY Query Parameter which accepts multiple values. You can refer to the below image.

Once you click OK, it will open the Define Query Parameters window. Check the "Pass Null" checkbox and click OK.

As you can see from the below image the dataset has been created with one report parameter.

Step 3: Modify Dataset Parameter Value Expression

Right click on the Dataset and click on Dataset Properties, It will open a Dataset Properties window and then click on the Parameters tab. You can refer to the below image.

Click on the Parameter value expression button, it will open an expression window. Modify the expression as shown below.

Step 4: Add Tablix

For data viewing purpose I am adding a Tablix into my report. This Tablix will show Productkey and EnglishProductName. You can refer to the below image.

Step 5: Preview Report

We have made all the necessary changes, now let’s preview the report. I have run the report for two different parameter values. You can refer to the below images.

As you can see from the above two images the report is working fine for a multi-value user input report parameter.

Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Problem

In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.

Solution

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Let's understand the problem with a simple example

I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let's create the below three datasets for the sample report.

Report Dataset: This dataset will be used for the report body and it has two query parameters @ManufacturePeriodand @ProductKey. The @ManufacturePeriod query parameter can accept a single value and the @ProductKeyquery parameter can accept multiple values.

SELECT DaysToManufacture, Productkey, EnglishProductName
FROM DimProduct
WHERE DaysToManufacture =(@ManufacturePeriod)  and ProductKey IN(@ProductKey)
ORDER BY DaysToManufacture

ManufacturePeriod Dataset: This dataset will be used to get a list of values for the ManufacturePeriod report parameter.

SELECT Distinct DaysToManufacture 
FROM DimProduct 
ORDER BY DaysToManufacture

ProductKey Dataset: This dataset will be used to get a list of values for the ProductKey report parameter.

SELECT DISTINCT ProductKey 
FROM  DimProduct 
ORDER BY ProductKey

Because the Report Dataset has two query parameters named as @ManufacturePeriod and @ProductKey, SSRS will automatically create two report parameters name as ManufacturePeriod and ProductKey. After creating all three datasets, your Report Data Pane must look like the below image.

Let's configure both of the report parameters to get a list of parameter values.

Right click on the ManufacturePeriod report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of ManufacturePeriod report parameter.

Right click on the ProductKey report parameter and click on Parameter Properties, it will open Report Parameter Properties window. Please make the below changes in the properties of the ProductKey report parameter.

Let's add a Tablix for data viewing purposes. Please pull all the data fields from Report Dataset into a Tablix. Your report must look like the below image after adding the Tablix.

Now if you will preview the report then it will not display selected parameter values. Please follow the below steps to display the parameter values in the report header.

Steps to display parameter values in an SSRS report header

Our sample report has two report parameters ManufacturePeriod and ProductKey. ManufacturePeriod is a single select and ProductKey is a multi select report parameter. I will update this sample report to display both parameters selection values in the report header.

1. Add the page header in the report.

2. Add two text boxes in the report header to display ManufacturePeriod report parameter value. In the first text box give a meaningful parameter caption for the ManufacturePeriod report parameter. Right click in the second text box and click on expression, it will open the expression window. You have to set the expression to display theManufacturePeriod report parameter value. You can find the expression below.

3. Add two more text boxes in the report header to display the ProductKey report parameter values. In the first text box give a meaningful parameter caption for the ProductKey report parameter. Right click on the second text box and click on expression, it will open an expression window. You have to set the expression to display the ProductKeyreport parameter values. The ProductKey report parameter is a multi select report parameter, so I have used theJoin SSRS function in the expression. It will display multiple selected parameter values using a comma separator. You can find the expression below.

4. Let's preview the report. As you can see from the below image, the report displays the parameter selection values, but that's not all.

5. If your report parameter has a long list of values and you preview the report for all the values then the report will display all parameter values using a comma separator. As you can see from the below image, the report shows all parameter values and it takes lots of report space. It will be better to display a value such as "All" when a report is run with all parameter values.

6. Let's modify the text box expression which displays the parameter values for the ProductKey report parameter. We will display "All" if all the values are selected from the ProductKey report parameter. You can find the expression below.

7. We have made all necessary changes, let's preview the report for all parameter values for the ProductKey report parameter. You can see from the below image, the report displays "All" when all parameter values are selected in theProductKey report parameter.