How can I add Default list in dropdown then use my search input to filter that list in SSRS Report

Question

I've created a dropdown list that is filtered according the value present in a search box . But the issue is untill I enter the value in Search box the dropdown is in disabled state. Is there any way that i can get the whole list by default on load and then i use my search box to filter the list.

Answer 1

This is quite simple to do. Here's the steps to create a similar report.

If you want to follow exactly, then you will need a copy of the WideWorldImporters sample database as that's what I used as my sample data.

This following report simply lists customers and their phone numbers, the customers listed are based on a drop down list of customers and that drop down list is based on the contents of a 'search' parameter.

To start, create a new blank report

Add a parameter called pSearch, set the Allow blank value ("") option ON. In the default values tab, select Specify values, click Add but leave the Value blank. This bit is important...

Next, add a dataset called dsCustomer. This query will supply the data to the drop down list so we will filter based on the pSearch parameter that we just set up.

The dataset query should look something like this.

SELECT CustomerID, CustomerName FROM [Sales].[Customers] 
WHERE 
    (CustomerName LIKE '%' + @pSearch + '%'
    OR      
    ISNULL(LEN(@pSearch),0)=0
    )
ORDER BY CustomerName

This will return the full list if the parameter is blank or null, otherwise it will return a filtered list.

Next, create a paramater called pCustomers, Set Allow multiple values to ON. Set the available values to Get values from a query and point this to the dsCustomer dataset.

You can test the parameter now, when the report first opens the customer list will be full but if you type into the search parameter then drop the customer list down again you will see a filtered list.

This may be all you need, I finished the report as follows Create another dataset called dsMain. This will supply the data to the final report table so it will filter based on our pCustomers parameter.

The dataset query will be something like.

SELECT CustomerID, CustomerName, PhoneNumber FROM [Sales].[Customers] 
WHERE CustomerID IN (@pCustomers)
ORDER BY CustomerName

Finally I placed a table in the report and bound it to dsMain

The final report looks like this then it first opens.

and if I type ala into the seearch parameter and drop the list down again I see this.

IMPORTANT Remember that parameter names are Case Sensistive so the name must match exactly with your dataset query.