SSRS: How to select two or more values from a long filtered multiple selection list with different search strings

Question

Let's consider a multiple selection parameter on a report: Employee

This parameter has a lot of possible values. Initially nothing is shown on the list and there is a textfield search parameter associated, that updates the Employee selection list with top n matches for the searched string.

If the entered search query is John Doe we can imagine that now the selection list shows:

John Doe
...
Xavier John Doesson

Now I can select as many items as I want from this filtered list, but if I want to select both John Doe and Alicia Keys happens the following:

First when I enter the search string "John Doe" the selection list gets populated accordingly
I select John Doe - OK
I enter search string "Alicia Keys", the selection list gets populated also
Selection of John Doe is gone - I want to be able to select both Alicia and John at the same time, but I don't want to go through a thousands of names long selection list

Update:

Forgot to mention that we have an OLAP cube in the background with dimension 'Employee'. This dimension is used as the source of the parameter and the param dataset uses MDX to fetch the values, therefore the SQL solution cannot be applied here.

The current solution creates an custom set with MDX Filter and Head functions and then this set is used in the ROWS-part of the MDX query.

Here is how the set created:

SET setEmployees AS { 
HEAD(
  FILTER( [Employees].[Employees].ALLMEMBERS, 
      INSTR([Employees].[Employees].CURRENTMEMBER.Name,@EmployeeSearch,1 >= 1 ) 
      )
,100) 
}

Basically the problem with this solution is that how do you add multiple search strings to the instr function

Is there a common solution to this kind of situation? Am I approaching the problem from wrong direction?

Jeroen Jeroen · Accepted Answer · 2012-12-14T14:08:48

What you could do is make the search parameter more flexible, so you can handle input such as:

John OR Jane

If "OR" queries are more common than "AND" queries you could support it with queries such as:

John Jane

Note that this may throw people off, because the search features they're used to (such as Google search) typically tend interpret multiple words in the "AND" sense.

Anyhow, the tricky bit of course is the SQL behind the Employee data set. This should use the search parameter in a more flexible way. You haven't specified how that's currently working, but I imagine you may be using something like:

WHERE Employee.FullName LIKE '%' + @SearchParameter + '%'

You would need to extend that to support "OR" queries. There's a whole range of solutions for that, from quick 'n dirty handmade SQL (e.g. string split combined with WHERE...IN) to full-text querying. Choose a solution that's best for your situation.

SSRS: How to select two or more values from a long filtered multiple selection list with different search strings

Update:

2 Answers