How to filter SSRS report with multivalue parameters works like a string.contains

Question

I have a SSRS Report calls a stored procedure to get the result set. One of its column is a "Owner" column which can have 0 or more person name and the string is concatenated with a " & ". I can't change the stored procedure so in the report I need to add a multivalue report parameter. This parameter displays all the possible owner name in the drop down and need to use this parameter to do a post-filter after the result set is back from the stored procedure call. My question is how to create this filter on the Owner field that works like a string.contains? To better illustrate, below is an example:

Below is the raw result set

Ticket Number Owner
100           John Doe & Jane Doe
101           John Doe & Jack Smith
102           John Doe & Bill White

If user selects Jack Smith and Bill White in the Owner parameter drop down, the result should be

Ticket Number Owner
101           John Doe & Jack Smith
102           John Doe & Bill White

If only John Doe is selected, all 3 rows should be returned.

Answer 1

You could do something like this.

 DECLARE @String NVARCHAR(10)
 SET @String = 'string'

 WHERE Owner LIKE '''' + '%' + @String + '%' + '''' 

which should give you

WHERE Owner LIKE '%string%'

Answer 2

Assuming your multi-valued parameter is populated with the individual owners' names, there are two steps to achieve this.

Add a DataSet embedded into your report and change the query type to 'text', inputting the following SQL and replace the <StoredProcedureName> accordingly:

-- Insert stored procedure results into temp table
CREATE TABLE #Temp(TicketNumber int, Owner nvarchar(50))
INSERT INTO #Temp 
EXEC <StoredProcedureName>

-- Build dynamic SQL query to allow for single or multiple values from the parameter
DECLARE @SQL NVARCHAR(1024) = 'SELECT * FROM #Temp WHERE Owner LIKE ' + '''%' + REPLACE(@S,',','%'' OR Owner LIKE ''%') + '%'''

EXEC(@SQL)

DROP TABLE #Temp

In your embedded DataSet's Properties window, open the Parameters tab and open the Expression Editor for the @S Parameter and add the following VBA:

=Join(Parameters!ReportParameter1.Value, ",")

This is important, as it builds a single string when multiple parameter values are chosen. For example, I choose Jane Doe and Bill White, the output string will be: "Jane Doe, Bill White".

The dynamic SQL statement replaces any comma (,) occurances with '% OR LIKE %' building a dynamic SQL string that when executed is the equivalent of:

SELECT * FROM #Temp WHERE Owner LIKE '%Jane Doe%' OR LIKE '%Bill White%'