SSRS Multiple Parameters in a single dropdown

Question

I have a report I need to develop where all Quotes should be listed where a specific Product is present. The product can be identified based on Product ID/Price/Product Name etc. I was looking to develop one report where all there fields are present as part of a dropdown. When the user selects one value such as Product ID and enters a value in the text box next to the ID, the report is filtered on this parameter. If the user selects name, the name can be entered in the text box and the report is filtered based on the Vendor ID etc.

Is there a way to do this? I have the parameters showing up next to each other instead.

LONG LONG · Accepted Answer · 2017-01-04T17:35:47

It is doable if I understand correctly and here are the steps for achieving your report purpose. I will use a simple query as an example data set for your reference.

1.Create source and datasets, in this example I skip the data sources and here is the short query for my exmaple

SELECT * FROM Table
WHERE 
(ID = @ID or @ID = '')
 and 
(Name = @Name or @Name = '')

2.Add another dataset named Control, the query for Control is:

SELECT 'ID' as option
UNION
SELECT 'Name' as option

The purpose of creating this dataset is to provide the available values later when you need to choose either name or ID.

3.After step1, you should already have two parameters generated by system, which are ID and Name, if not, create them by yourself and go to each parameter page, DO MAKE SURE CHECK Allow blank value''

4.Create the 3rd parameter called Control, go to parameter setting page, go to Available Values, Choose Control dataset, Choose option (which is the customize column name you set in Controldataset) for both Value field and Label field

5.Go to ID parameter setting page, go to Default Values, choose Specify values, click fx, type this:

=Switch(Parameters!Control.Value="Name","")

Click ok

6.Go to Name parameter setting page, go to Default Values, choose Specify values, click fx, type this:

=Switch(Parameters!Control.Value="ID","")

Click ok

The ID and Name expression are correct, not set wrong

7.Change the parameter order to make Control on the VERY TOP, no matter ID or Name comes for the 2nd, but Control should be in the 1st place.

8.Drag and drop a table, place Name and ID. Preview the report, at this time, only Control will be available for you to choose value, choose either ID or Name to see the difference. When you choose ID, Name will be gray out, only ID light up and type any ID you want, actually, what the system does internally is passing the "" (blank value) to Name, that is why you need to make sure the Name parameter could accept blank value. Same for selecting Name

Update me if you have any run time issue

SSRS Multiple Parameters in a single dropdown

1 Answers