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.
