Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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


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





More SQL Server Solutions











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, October 11, 2016 - 10:45:08 PM - Roy Back To Top

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

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

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

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

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

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

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

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

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

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

 

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

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.


Learn more about SQL Server tools