Creating a multi-option parameter report for SQL Server Reporting Services

By:   |   Comments (17)   |   Related: > Reporting Services Parameters


Problem

I have a reporting requirement where users want to enter report parameters by either selecting values from a dropdown list or entering To and From range values.  How can I do this in a Reporting Services report?

Solution

This tip assumes that you have experience building a simple SSRS report and T-SQL skills. In my previous tips I have explained optional query parameters and cascaded report parameters in SSRS, it is recommended to read these tips as well.

In SSRS we can't enable or disable report parameters based on other parameter values. If we have a report parameter, then we have to provide a value. SSRS doesn't provide any functionality to choose query parameters from report parameters, but we have a workaround.

In this article we show how to choose query parameters based on report parameters. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.

To demonstrate the solution, I will create a report which will give two different choices to filter the data:

  • Users can either filter the data using a Multi Select Parameter or
  • Users can filter the data using a Range Parameter, using To and From values.

Step 1 (A): Add Dataset

I have already created an embedded data source connection to the AdventureworksDW2008R2 database. Let's create a new dataset for our report.

DataSet Query

IF @QueryParameterType=1
(
SELECT       ProductKey, EnglishProductName
FROM            DimProduct Where ProductKey IN(@Product)
)
else if @QueryParameterType=2
(
Select ProductKey, EnglishProductName From DImProduct 
Where Productkey >=@ProductFrom and Productkey <= @ProductTo
)

This dataset has four query parameters and it returns Productkey and EnglishProductName.

The @QueryParameterType query parameter value will decide the dataset query.

  • If user provides 1 as a value for @QueryParameterType then the user can filter the data by providing values for @Product query parameter.
  • If user provides 2 as a value for @QueryParameterType then the user can filter the data by providing values for @ProductFrom and @ProductTo query parameters.
Report Main Dataset

As you can see from the below image the dataset has been created and SSRS has automatically created four report parameters.

Report Data Pane after creating main dataset

Step 1 (B): Add Parameter Dataset

As you know our recently created dataset has one @Product query parameter which can accept multiple values, so we have to create a new dataset for our Product report parameter. This dataset will be used to return a list of available values for the Product report parameter.

Product DataSet

WITH MYCTE AS 
(SELECT  -2147483648 AS ProductKey, '(Parameter Not Applicable)' AS EnglishProductName, 
2 AS ParameterType)
SELECT        ProductKey, EnglishProductName, ParameterType FROM MYCTE AS MYCTE_1
WHERE        (ParameterType = @QueryParameterType)
UNION ALL
SELECT        ProductKey , EnglishProductName, ParameterType FROM  
(SELECT DISTINCT ProductKey, EnglishProductName, 1 AS ParameterType FROM   DimProduct) AS A
WHERE        (ParameterType = @QueryParameterType)
ORDER BY EnglishProductName

This Dataset has one query parameter.

Based on @QueryParameterType query parameter value, this dataset will return the records as follows:

  • If @QueryParameterType query parameter value is 1 then it will return all values from the database.
  • If @QueryParameterType query parameter value is 2 then it will return only one record whcih is "Parameter Not Applicable".
Adding DataSet for Product parameter

Step 2: Report Parameter Configuration

We have to configure the report parameters and in this step we will configure each parameter one by one.

Parameter 1 - QueryParameterType

Double click on the QueryParameterType report parameter. It will open the Report Parameter Properties window. Change Data type to Integer from Text. You can also modify the parameter Prompt as per your choice. You can refer to the below image.

Report Parameter Properties for QueryParameterType Parameter

Click on Available Values, choose the Specify Values radio button and click on the Add button to add two parameter values as shown below.

Adding available values for QueryParameterType parameter

These parameter values will decide the query for your report. If the Multi Select Parameter value is selected then users can choose parameter values from the list of available values.  If Range Parameter value is selected then users can filter report data using a Range Parameter and the user has to enter values for the ProductFrom and ProductTo parameters.

Parameter 2 - Product

Double click on the Product report parameter, it will open the Report Parameter Properties window. Change data type to Integer from Text and check the Allow multiple values checkbox. You can also modify the parameter Prompt as per your choice. You can refer to the below image.

Report Parameter Properties for Product Parameter

We have to get a list of values for this parameter, so click on the Available Values tab, choose the Get values from a query radio button and select Product for the dataset, ProductKey for the Value field and EnglishProductName for the Label field. You can refer to the below image.

Adding available values for Product Parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set a default value using an expression, so click on the expression button as shown below.

Adding default value for Product Parameter

Once you click on the expression button it will open the Expression window. Set the expression as shown below.

=iif(Parameters!QueryParameterType.Value=2,-2147483648,nothing)

You can refer to the below image as well.

Product parameter default value expression

This expression will set (Parameter Not Applicable) as the default value for Product when the QueryParamterType parameter value is chosen as Range Parameter (value = 2).

Parameter 3 - ProductFrom

Double click on the ProductFrom report parameter, it will open the Report Parameter Properties Window. Check the Allow blank value ("") checkbox. You can also modify the parameter prompt as per your choice. You can refer to the below image.

Report Parameter Properties for ProductFrom Parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set the default value using an expression, so click on the expression button as shown below.

Adding default value for ProductFrom Parameter

Once you click on the expression button it will open the Expression window.  Set the expression as shown below.

=iif(Parameters!QueryParameterType.Value=1,"(Parameter Not Applicable)","")

You can refer to the below image as well.

Default value expression for ProductFrom parameter

This expression will set (Parameter Not Applicable) as the default value for ProductFrom when the QueryParamterType report parameter value will be selected as Multi Select Parameter (value = 1).

Parameter 4 - ProductTo

Double click on the ProductTo report parameter, it will open the Report Parameter Properties Window. Check the Allow blank value ("") checkbox. You can also modify the parameter prompt as per your choice. You can refer to the below image.

Report Parameter Properties for ProductTo parameter

Click on the Default Values tab, choose the Specify Values radio button and click on the Add button. We have to set the default value using an expression, so click on the expression button. You can refer to the below image.

Adding default value for ProductTo Parameter

Once you click on the expression button it will open the Expression window.  Set the expression as shown below.

=iif(Parameters!QueryParameterType.Value=1,"(Parameter Not Applicable)","")

You can refer to below image.

Default value expression for ProductTo parameter

This expression will set (Parameter Not Applicable) as the default value for ProductTo when QueryParamterType report parameter value will be selected as Multi Select Parameter (value = 1).

Step 3: Add Tablix

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

Adding Tablix to the report

Step 4: Preview Report

We have made all necessary changes, now let’s preview the report. As you can see from the below image, the first parameter of the report gives two options to filter the data. You can either filter using a Multi Select Parameter or Range Parameter.

Report Preview for Multi select parameter

When I select Multi Select Parameter, my next parameter gets enabled with a list of available values. You can refer to the below image.

Product list from multi select parameter

I can choose the desired values from the list of available values. For this report run, the default values for the ProductFrom and ProductTo parameters will be (Parameter Not Applicable), so I don't need to enter any value for these. Even if I enter a value for ProductFrom or ProductTo it will not impact the report data.

Let's view the report, as you can see below, the report is showing data only for the selected values in the Product parameter.

Report Preview For Multi Select Product Parameter

Now I will select Range Parameter in my first report parameter, this will allow me to filter the data using the Range Parameter. The Product parameter value will be automatically set to Parameter Not Applicable and this is the only value available in this case. I have to enter the ProductFrom and ProductTo values to filter the report data. As you can see from the below image, the report is showing data for the range parameter values from 1 to 10.

Report Preview for Range Parameter
Next Steps
  • Try to implement the same solution using optional query parameters.
  • Check out Optional Query parameter in SSRS.
  • Check out Cascaded Report parameters in SSRS
  • You can find more SSRS parameter tips here.
  • Check out all of the SQL Server Reporting Services tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips


Comments For This Article




Friday, July 22, 2022 - 2:20:29 PM - Joe Back To Top (90303)
You can also use this on the Parameter Dataset Products:

IF @QUERYPARAMETERTYPE = 1
SELECT DISTINCT
ProductKey
, EnglishProductName
FROM DimProduct
ELSE
SELECT 32342343 as ProductKey
, '(Parameter Not Applicable)' AS EnglishProductName

Wednesday, June 15, 2022 - 10:05:58 AM - Greg Robidoux Back To Top (90163)
Hi Chandra,

One option is to UNION the results together, so they are in one dataset.

-Greg

Wednesday, June 15, 2022 - 5:41:27 AM - chandra Back To Top (90162)
i want to create a report in SQL with below two queries in one Dataset

SELECT V_GS_NETWORKPRINTERS0.ResourceID, DEV.Netbios_Name0 AS Netbios_Name, PrinterQueue0 AS PrinterName, PrintServer0 AS PrinterServer,
PrinterLocation0 AS PrinterLocation, PrinterDriver0 AS PrinterDriver, DateInventoried0 AS Timestamp, 'Network Printer' AS Type FROM V_GS_NETWORKPRINTERS0
LEFT JOIN v_R_System_Valid DEV ON DEV.ResourceID = V_GS_NETWORKPRINTERS0.ResourceID ORDER BY DEV.Netbios_Name0

SELECT ResourceID, SystemName0 AS Netbios_Name, Caption0 AS PrinterName, SystemName0 AS PrinterServer, SystemName0 AS PrinterLocation,
DriverName0 AS PrinterDriver, TimeStamp AS Timestamp, 'Local Printer' AS Type FROM V_GS_PRINTER_DEVICE
ORDER BY PrinterName


And a parameter AS @type with a drop down for Network and Local printers selection for end users

When i execute the report it is showing me only the network printers and not getting any details from the local Printers, if i select in parameter field as local Printer

Wednesday, May 5, 2021 - 12:06:53 PM - Adina Burrows Back To Top (88648)
Hi Prasad,

I have an existing stored procedure for an old crystal report. I have tried to write the parameter in the where clause but it doesn't work. If write it as a query for my dataset, would I write the parameters in the where clause?

Either way I am going in circles as I have done it multiple ways and cant get the multi, multi value parameters to work.

My query/SP:
SELECT
distinct
syStudent.StuNum,
RTRIM(systudent.firstname) + ' ' + RTRIM(systudent.lastname) AS Fullname,
sycampus.Descrip AS CampusDescrip,
syStudent.email AS PrimaryEmail,
syStudent.OtherEmail AS SecondaryEmail,
syStudent.Phone AS HomePhone,
syStudent.MobileNumber AS MobilePhone,
REPLACE(REPLACE(syStudent.Addr1, char(13), ''), char(10), '') AS Addr1,
syStudent.Addr2,
syStudent.City,
syStudent.State,
syStudent.Zip,
SyCountry.Descrip AS Country,
syStudent.ARBalance AS SABalance,
AdEnroll.CreditsReq,
AdEnroll.CreditsEarned,
AdProgram.Descrip AS ProgramDescrip,
AdProgramVersion.Descrip AS ProgramVersionDescrip,
RTRIM(AdTerm.Descrip) as Descrip,
dbo.fn_AdConcentrationsByEnroll(adenroll.adenrollid) AS Concentrations,
SySchoolStatus.Descrip AS SchoolStatus,
(vw_StudentEnrollmentAdvisors.AdvisorFirstName + ' ' + vw_StudentEnrollmentAdvisors.AdvisorLastName) AS AcademicAdvisor,
CASE (Ex_cont.Exam_or_Continuation_Enrolled)
when 'Y' then 'Yes'
else 'No'
END AS EnrolledIn_Ex_or_Continuation,
CASE (HoldGrp.HoldMsg)
when 'Y' then 'InAHoldGroup'
else ''
END AS HoldFlag,
CASE (DualEnroll.DualEnroll)
when 'Y' then 'InDualEnrollment'
else ''
END AS DualEnrollFlag
into ##ActiveNonRegStu
from
syStudent (nolock)
join AdEnroll (nolock) on syStudent.SyStudentId = AdEnroll.SyStudentID
join AdTerm(nolock) on AdEnroll.AdtermID=Adterm.AdtermID
--inner join @CampusList Campus
--on AdEnroll.SyCampusID = Campus.SyCampusID
join SyCampus on AdEnroll.SyCampusID = SyCampus.SyCampusID
join (
select distinct
AdEnroll.AdEnrollID
from AdEnroll (NOLOCK)
where AdEnroll.SySchoolStatusID in (13, 14, 16, 66, 68, 82, 86)
and AdEnroll.AdEnrollID not in (select AdEnrollID
from AdEnrollTerm (nolock)
where AdEnrollTerm.TermCreditsSched > 0)
) AS NoEnroll
on AdEnroll.AdEnrollID = NoEnroll.AdEnrollID
Left Join SyCountry (nolock) on SyStudent.SyCountryID = SyCountry.SyCountryID
join AdProgram (nolock) on AdEnroll.AdProgramID = AdProgram.AdProgramID
join AdProgramVersion (NOLOCK) on AdEnroll.adProgramVersionID = AdProgramVersion.AdProgramVersionID
join SySchoolStatus (nolock) on AdEnroll.SySchoolStatusID = SySchoolStatus.SySchoolStatusID
left join vw_StudentEnrollmentAdvisors (nolock) on AdEnroll.AdEnrollID = vw_StudentEnrollmentAdvisors.AdEnrollId
and vw_StudentEnrollmentAdvisors.AdvisorStaffGroupCode = 'ACAD'
left Join (
select AdEnrollSched.AdEnrollID, 'Y' Exam_or_Continuation_Enrolled
from AdEnrollSched (nolock)
join adclassSched (nolock) on adenrollsched.adclassschedid = adclasssched.adclassSchedid
where
(adclasssched.code like 'EX%'
or adclasssched.descrip like '%Continuation%')
and AdEnrollSched.Status = 'S'
) As Ex_cont
on AdEnroll.AdEnrollID = Ex_cont.AdEnrollID
left join (
select systudgrp.SyStudentID,
'Y' AS HoldMsg
from SyStudGrp
join SyHold on SyStudGrp.SyGroupsID = SyHold.SyGroupsID
join SyGroups on SyHold.SyGroupsID = SyGroups.SyGroupsID
where SyGroups.Active = 1
and (SyStudGrp.DateOff is null or SyStudGrp.DateOff = '')
) AS HoldGrp
on AdEnroll.SyStudentID = HoldGrp.SyStudentID
left join (
select AdEnroll.SyStudentID,
'Y' AS DualEnroll
from AdEnroll
where SySchoolStatusID in (13, 14,16, 66, 68, 82, 86)
group by SyStudentID
having count(adenroll.adenrollid) > 1
) AS DualEnroll
on AdEnroll.SyStudentID = DualEnroll.SyStudentID
where
AdEnroll.AdProgramID not in (7, 14, 17, 18)
and AdEnroll.SySchoolStatusID in (13, 14, 16, 66, 68, 82, 86)

I want to add 3 multi value parameters based off of this. 1-Term(adterm.descrip), 2-Campus(sycampus.Descrip) and 3.Advisor ((vw_StudentEnrollmentAdvisors.AdvisorFirstName + ' ' + vw_StudentEnrollmentAdvisors.AdvisorLastName)).

I have tried adding them and adding a separate dataset for each, adding parameters to main report dataset filter and even tried adding them to tablix filter. Any help you can provide would be greatly appreciated. I am driving myself crazy trying to get this to work!!!

Adina Burrows

Thursday, November 16, 2017 - 11:33:53 AM - Marcio Takahashi Back To Top (69834)

 Hi Prasad,

 

This is a great article that will solve a report that I am building.

However, how would I handle a third parameter (interger data type).  These are the scenarios.

Scenario 1: Par1 (Applicable), Par2 (Not Applicable), Par3 (Not Applicable)

Scenario 2: Par1 (Not Applicable), Par2 (Applicable), Par3 (Not Applicable)

Scenario 3: Par1 (Not Applicable), Par2 (Not Applicable), Par3 (Applicable)

 

Thank you

 

Tuesday, October 11, 2016 - 10:45:08 PM - Roy Back To Top (43544)

I want to select a simple parameter, Yes Filter or No Filter (Values 1 or 2) and be able to set the dataset query to a filtered query with selections from another multi-select dropdown or set the query to ignor filtering.

 

When I set the query as below I get an error which says I must declar the scalr parameters.  The parameters are set up just as you have in the tip and so is the query for the Org dropdown.

 I cannot get this code to work at all:

IF @NoFilter = 1

(

SELECT        SALUTATION, CLIENT, Department, ADDRESS, CITYSTZIP, COUNTRY, ContactID, LastName, FirstName, ClientID, CLAddress, Vendor, VEAddress, Type, MiddleName, Suffix, Title, ContactAddressee, ContactAddress1, ContactAddress2, ContactAddress3, ContactAddress4, ContactCity,  ContactState, ContactZip, ContactCountry, ContactPhone, ContactFax, Pager, CellPhone, HomePhone, EMail, MailingAddress, Billing, PrimaryInd, 
ContactStatus, PreferredName, CustomCurrencyCode, Source, Name, EMPLOYEE, OTHERS, MAILINGLIST, custOrganizationPrime,  custOrganizationClient
                                     FROM            vw_MailingListUpdate2
                                     WHERE        (EMPLOYEE IS NOT NULL)
                                     ORDER BY EMPLOYEE, CLIENT, LastName

)

ELSE IF @NoFilter = 2
    (

SELECT        SALUTATION, CLIENT, Department, ADDRESS, CITYSTZIP, COUNTRY, ContactID, LastName, FirstName, ClientID, CLAddress, Vendor, VEAddress, Type, MiddleName, Suffix, Title, ContactAddressee, ContactAddress1, ContactAddress2, ContactAddress3, ContactAddress4, ContactCity,  ContactState, ContactZip, ContactCountry, ContactPhone, ContactFax, Pager, CellPhone, HomePhone, EMail, MailingAddress, Billing, PrimaryInd, 
ContactStatus, PreferredName, CustomCurrencyCode, Source, Name, EMPLOYEE, OTHERS, MAILINGLIST, custOrganizationPrime,  custOrganizationClient
      FROM            vw_MailingListUpdate2
      WHERE        (EMPLOYEE IS NOT NULL) AND (custOrganizationClient IN (@Org))
      ORDER BY EMPLOYEE, CLIENT, LastName

)

 

 

Thursday, November 12, 2015 - 6:16:53 PM - FrankL Back To Top (39060)

I also have an issue that I get ORA-00900  invalid SQL Statemen if I try your logic on the Query part of the Dataset Property, I tried a simple test if it was my query and it is not because I get the same error with this

IF @QueryParameterType=1
(
select 1 as A, 2 as B from dual
)
else if @QueryParameterType=2
(
select 3 as A, 4 as B from dual
)

 

it clearly gives me an error when I put the IF @... statement.   How is it that yours works....

Tuesday, June 16, 2015 - 4:18:55 AM - Vanaja Back To Top (37932)

I have code to select the statements if the codition satisfys as like IF @Parameter = 1 Then SElect statemnt ElseIF @Paremeter = 2 Then Select STatment but this gives error as Invalid SQL statement. Could you please help me on this

 

Friday, March 27, 2015 - 8:13:05 AM - Ghanesh Back To Top (36729)

Hi Michael,

If you are using calendar date picker then it will not work. If you have Date in list of values then it will work but you have to set parameter value in date format and before passing the value into dataset convert it into date.

Hope it helps.

Friday, March 27, 2015 - 2:37:09 AM - Michael Back To Top (36728)

HI

Please assist can this work with Year and Date Selection.

my issues  is when the user select Parametertype  = 1 must be able to select a Year Parameter

when he/she select   Parametertype   =2 then  must be able to select date ranges

attached is my code

WITH  vw_ops_INV_Breakages AS
(SELECT   '(Parameter Not Applicable)' AS JnlDate, 2 AS ParameterType)
SELECT      JnlDate, ParameterType
FROM       vw_ops_INV_Breakages  AS MYCTE_1
WHERE (ParameterType = 2)
ORDER BY   JnlDate asc
Union All
SELECT   JnlYear,ParameterType FROM
 (SELECT  DISTINCT  JnlYear, 1 AS ParameterType FROM    vw_ops_INV_Breakages) AS A
WHERE        (ParameterType = 1)
order by JnlYear  asc

 

Thanks

Michael

 

 

 

 

 

 

 

Thursday, March 26, 2015 - 4:09:28 PM - casualinfoguy Back To Top (36723)

I just jumped into this fray, and didn't read the article carefully, but it seems that if the parameter passed to SQL Server is a combined parameter like "(bread, jam)" and the sql query uses an "IN" clause, then that query in all cases would return a nullset if "bread" and "jam" are values in the table.

This is simply because the values are not represented as strings in the query.

This would work:

SELECT       ProductKey, EnglishProductName
FROM            DimProduct Where ProductKey IN(@Product)

if @product looked like this: "('bread', 'jam')", NOT "(bread, jam)"

Thursday, March 26, 2015 - 12:11:03 PM - Ghanesh Back To Top (36719)

Hi Kris,

It should have worked without any problem, I guess you may have missed something.

Can you please cross check your report with all steps mentioned above?

Let me know if you still face any issue, I can help you out.

Regards,

Ghanesh

Thursday, March 26, 2015 - 11:30:54 AM - Kris Back To Top (36718)

Ganesh,

I think when you pass Range values 1 and 10 the CTE is just ignoring the select statement after CTE

Thursday, March 26, 2015 - 10:10:44 AM - Kris Back To Top (36715)

I followed all the steps and created the report and It worked for Multi Select Parameter but when I pass Range parameter witn 1 and 10 then the report displays as below could you please clarify?

 

Product Key         English Product Name

-2147483648        (Parameter Not Applicable)

Thursday, March 26, 2015 - 10:02:12 AM - Ghanesh Back To Top (36714)

HI Aswini,

I had also faced similar kind of problem, this happens becasue combination of all multiple values are treated as a single string value when we pass in proc and our query doesn't find any match for that.

i.e you are passing (Bread, Jam) but it will be treated as single value 'Bread, Jam'.

Hope it helps.

 

Thursday, March 26, 2015 - 8:34:28 AM - Aswini Kumar T Back To Top (36708)

 

If you confused with my earlier question.

Question is "Why the proc will not return a single record if we pass MULITIPLE values as a input to IN clause(Procedure) from SSRS Report? "

NOTE: Input values are can be STRING datatype.

Example:

Scenario 1:

select * from products where prodname in (@prodname)

Scenario will work fine from SQL window as well as SSRS report(Direct statement in SSRS dataset).

Scenario 2:

If we place same statement in procedure, the code looks like below

create proc usp_GetProd_Details(@ProdName)

as

begin 

select * from products where prodname in (@ProdName)

end;

My question is, If i pass values for  parameter @ProdName from SSRS report i.e multivalued parameter (With or Without JOIN(SSRS function) )

proc didn't retun any o/p or empty output.

Please let me know the reason behind this limitation.

Limitation is from SSRS or SQL Server i.e proc limitation?

Assume values reside in my table are as follows.

1.Bread

2.Jam

 

Thursday, March 26, 2015 - 6:35:27 AM - Aswini Kumar T Back To Top (36707)

Hi Ganesh,

I had a question to the article Step1(A)-Screen Shot-1.

After we do all the stuff report looks good, but as per my experience if we copy/place the entire SELECT statement in store procedure with IN clause.

Proc/SSRS report which uses proc to fetch the data from DB will not return any record because of IN clause.

Can you please let me know why this LIMITATION in SQL?.

I know proc which is designed with IN clause will not throw correct o/p.

 

Thanks,

Aswini Kumar Tummala.














get free sql tips
agree to terms