Multi values for SSRS parameter with Like statement

Question

IN SSRS, I have a parameter named Comment which I set to Allow multiple values

and specified available values for it.

My problem is that if I select one value, I will get results but if I select more than one, I will get an error.

The problem is probably because I wrote my SQL statement like this:

dbo.tbl_Status.Comments LIKE ('%' + @Comment + '%')

NB: I need to use LIKE and not IN

Answer 1

You would need to change your query in an expression and concatenate the condition using the JOIN expression on your parameter values.

For instance, if you create a report based on the Adventureworks 2014 database you could add a parameter like this:

With some values that appear in the table:

If you then enter an expression for your dataset instead of a query like this:

="SELECT "
&     "[NationalIDNumber],"
&      "[JobTitle]"
&"FROM [AdventureWorks2014].[HumanResources].[Employee]"
&"WHERE [JobTitle] LIKE '%" & Join(Parameters!jobtitle.Label, "%' OR [JobTitle] LIKE '%") & "%'"

It would result in what you want when selecting a single value:

Because the concatenated expression is this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'

And it would also work when you select multiple values:

Because the expression would result in this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'
    OR [JobTitle] LIKE '%Marketing%'

Full .RDL file can be found on Github Gist and requires a local Adventureworks 2014 database.

Answer 2

thank you for the solution you mentionned. I tried to reproduce the steps from my end but it's not working... When I select values in the parameter it returned no results even if in the column I have data that match the values... Here is my query :