Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: > 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.

Report Data Pane Window

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.

ManufacturePeriod Report Parameter Properties Window

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.

Size Report Parameter Properties Window

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.

Adding 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.

Report Preview without Select All checked

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.

Setting Select All default value for ManufacturePeriod Report parameter

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.

Setting Select All default value for Size Report parameter

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.

Report Preview after Setting Select All

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.

Final Report Data Preview

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


Last Update:






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

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, November 15, 2016 - 10:39:07 AM - Michele Back To Top

 Thank you for your article. I was wondering  if it makes any difference if you name your dataset and your parameter with the same name. I have followed all steps, but I am unable to obtain the "select all" result. I do have nulls and have made reference to your tip about excluding Nulls.

 


Tuesday, November 15, 2016 - 10:37:34 AM - Michele Back To Top

Hello, I read your article and I thank you for discussing in detail the factor of NULL.

 

I am still not able to get the "Select All" Feature to show on my pull down list box.

 

I have a dataset and a parameter with the same name. Would this make a difference? Should I use different names for the Dataset and the Parameter? 

 


Tuesday, July 12, 2016 - 10:57:10 PM - Mayur Patel Back To Top

 I have around 10 parameter and 8 are dropdowns and 2 textbox parameters for a report. This is the business requirements. I have 6 of the dropdowns that are cascade dropdowns so they are linked to each other. So if you select firstname then it should shows possible lastname. Then I have next 3 dropdowns that are also cascading dropdowns but they are not linked to firstname and lastname. Then I have the remaining dropdowns that are not linked to any of them and are independent. All the dropdowns are multivalue dropdowns. so the requirement is to load all dropdowns with select all option checked on all the dropdowns. Soon as I started to put the cascading dropdowns the once that were independent stopped loading with select all option checked when report parameter options are loaded. Is there a way to fix this? 


Tuesday, March 22, 2016 - 6:38:46 AM - Uduak Back To Top

Hi,

 

I am able to display multivalue parameters. However, I have designed my report such that a user pick a department before the report displays. However,

I notice that when the user checks "Select All", all values in the department show as the  individual department for each record.

Please see my query below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date:

-- Description: <Description,,>

-- =============================================

 

ALTER PROCEDURE [rpt].[uspPersonalAndContactInformation] --

(

@companyID int,

@department varchar(30),

@employeeNo varchar(10) = null

 

)

AS

 

BEGIN

select e.employeeNo AS [EMPNO]/*for display on report view*/,

 ISNULL(t.value,'') +' '+e.firstName+ ' '+ISNULL(e.middleName,'')+ ' '+e.lastName AS NAME, 

ISNULL(e.email,'') AS EMAIL,ISNULL(d.departmentName,'') AS DEPARTMENT, ISNULL(e.officialEmail,'') AS [OFFICIAL EMAIL],

 ISNULL(e.dateOfBirth,'') AS DOB, ISNULL(e.maritalStatus,'') AS [MARITAL STATUS], 

ISNULL(e.gender,'') AS GENDER, ISNULL(e.mobilePhone,'') AS [MOBILE PHONE], ISNULL(e.homePhone,'') AS [HOME PHONE], 

ISNULL(a.addressLine1,'')+' '+ISNULL(a.addressLine2,'') +' '+ISNULL(a.zipCode,'')+' ' + ISNULL(s.stateName,''),

+' ' + ISNULL(c.countryName,'') AS [ADDRESS], ISNULL(ct.challengeTypeName,'NONE') AS [CHALLENGE TYPE],

ISNULL(l.languageName, 'NOT SPECIFIED') AS [LANGUAGES SPOKEN],e.companyID AS [COMPANY_ID]

 

from trans.Employee e

left outer join systems.Titles t on t.titleID = e.titleID

left outer join trans.EmployeeAddress a on a.employeeID = e.employeeID

left outer join systems.State s on s.stateID =a.stateID

left outer join systems.Country c on c.countryID = a.countryID

left outer join trans.ChallengeType ct on ct.challengeTypeID = e.challengeTypeID

left outer join systems.language l on l.languageID = e.languageID

left outer join systems.department d on d.departmentID = e.departmentID

where e.isActive = 1 and a.validTo >= GETDATE() and a.isDirty = 0 and (e.employeeNo = @employeeNo or @employeeNo is null)

and @department in(d.departmentName)

ORDER BY NAME;

 

END

 

Please help! In other words, i want the department column to display the correct department per record when "select all" is ticked on SSRS 2013.

 

Thank you.


Thursday, September 03, 2015 - 1:57:48 AM - Sai Back To Top

Hi,

I have followed the same steps as explained, but i have a doubt what is the procedure if the parameter is 'Date' data type, where in it doesnot have any specific dataset as "size dataset and manufactured period dataset" as explained by you.

Please help me in this. Below is the stored procedure.

 

ALTER PROCEDURE [dbo].[HR_EmployeeReportNew]

 

@EmployeeNameNVARCHAR(MAX)= NULL,

@EmploymentTypeNVARCHAR(MAX)= NULL, 

@JobTitleNVARCHAR(MAX)= NULL,

@StatusNVARCHAR(MAX)= NULL,

@HireDateFromDATETIME= NULL,

@HireDateToDATETIME= NULL

 

AS

SET NOCOUNT ON

SELECT DISTINCT

 

 EI.EmployeeCode AS EmployeeNumber

, ET.EmploymentTypeName

, rUStatus.StatusName

, EI.SocialSecurityNumber AS SSN

, COALESCE(CONVERT(VARCHAR(10), EI.DateOfBirth, 120),'') AS [DOB]

, COALESCE(CONVERT(VARCHAR(10), EI.DateOfHire, 120),'') AS [HireDate]

, UI.FullName AS [EmployeeFullName]

, UI.Email AS [ContactEMail]

, AI.PhoneNumber1 AS [ContactPhone]

, JT.JobTitleName AS [JobTitle]

, COALESCE(CONVERT(VARCHAR(10), EIM.DateOfExpiry, 120),'') AS [ExpiryDate]

, UI1.FullName

, COALESCE(CONVERT(VARCHAR(10), EE.EndDate, 120),'') AS [EndDate]

, CASE

WHEN AI.Address1 IS NULL THEN AI.Address2

WHEN AI.Address2 IS NULL THEN AI.Address1

WHEN (AI.Address1 = '') AND (AI.Address2 = '') THEN '--'

ELSE AI.Address1 + ', ' + AI.Address2 

 END AS [Address]

, COALESCE(CONVERT(VARCHAR(10), EE.EndDate, 120),'') AS [TerminationDate]

, COALESCE(CONVERT(VARCHAR(10), EE.EVerficationDate, 120),'') AS [EVerificationDate]

, EIM.VisaStatusId AS [VisaStatus]

, rST.SalaryTypeName AS [SalaryType]

, ET.EmploymentTypeName AS [EmployeeType]

 

FROM 

EmployeeInformation AS EI 

INNER JOIN 

UserInformation AS UI

ON EI.EmployeeId = UI.UserId

INNER JOIN

xref_UserAddress AS xrefUA

ON xrefUA.UserId = UI.UserId 

INNER JOIN

AddressInformation AS AI

ON AI.AddressId = xrefUA.AddressId

INNER JOIN

ref_JobTitle AS JT

ON JT.JobTitleId = EI.JobTitleId

LEFT JOIN

EmployeeEmployment AS EE

ON EE.EmployeeId = EI.EmployeeId

INNER JOIN 

EmployeeImmigration AS EIM

ON EIM.EmployeeId = EI.EmployeeId

INNER JOIN 

ref_SalaryType AS rST

ON rST.StatusId = JT.StatusId

LEFT JOIN

ref_EmploymentType AS ET

ON ET.EmploymentTypeId = EI.EmploymentTypeId

INNER JOIN

ref_UserStatus AS rUStatus

ON rUStatus.UserStatusId = UI.UserStatusId

INNER JOIN

UserInformation UI1

ON UI.ReportingManagerId = UI1.UserId 

 

WHERE

   (

ISNULL(UI.FullName,'') IN (SELECT cValue FROM BusiOps.dbo.fct_getStringSplit (@EmployeeName,',')) 

OR ((@EmployeeName) IS NULL)-- OR @EmployeeName = 'None')

)

AND (

ISNULL(ET.EmploymentTypeName,'') IN (SELECT cValue FROM BusiOps.dbo.fct_getStringSplit (@EmploymentType,',')) 

OR ((@EmploymentType) IS NULL)-- OR @EmploymentType = 'None')

)

AND (

ISNULL(JT.JobTitleName,'') IN (SELECT cValue FROM BusiOps.dbo.fct_getStringSplit (@JobTitle,',')) 

OR ((@JobTitle) IS NULL)-- OR @JobTitle = 'None')

)

AND (

ISNULL(rUStatus.StatusName,'') IN (SELECT cValue FROM BusiOps.dbo.fct_getStringSplit(@Status,',')) 

OR ((@Status) IS NULL)-- OR @Status = 'None')

)

AND (

ISNULL(EI.DateOfHire,'NULL') BETWEEN (@HireDateFrom)  AND (@HireDateTo) OR @HireDateFrom IS NULL

)

AND (

ISNULL(EE.EndDate,'NULL') BETWEEN (@HireDateFrom)  AND (@HireDateTo) OR @HireDateTo IS NULL

)

 

Learn more about SQL Server tools