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

By: Ghanesh Prasad   |   Updated: 2015-06-02   |   Comments (7)   |   Related: > Reporting Services Parameters

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 @ManufacturePeriod and @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 for ManufacturePeriod 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.

Next Steps

• Check out how to Allow Null Value in Multi Value Report Parameter
• Check out my other tips here.
• Check out other tips on Parameters.
• Check out all of the SQL Server Reporting Services tips
• Stay tuned for my next tip on Displaying Single and Multi-Valued Report Parameter Selections in report header.

About the author

Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips