CODEWITHSUNDEEP
sqlreporting-servicesssrs-2016
ojalon
ojalon

Reputation: 3

Multi values for SSRS parameter with Like statement

I need to find a way for a multi-value parameter to filter my report where I have a LIKE statement in the query.

I have a parameter that provides me with a list of organs (liver, kidney, heart) where I can select multiple values. I need to affect two columns (referral_organs and registration_organs). It is possible to have two organs in the same cell for either referral or registration - for example, there could be a 'liver/kidney' or 'lungs/liver' value in each column. To deal with this, I used a LIKE statement in my WHERE clause:

WHERE (F.REFERRAL_ORGANS LIKE '%' + @ORGAN + '%' OR F.REGISTERED_ORGANS LIKE '%' + @ORGAN + '%')

My list of organs comes from another dataset.

This works great for one selected organ but fails on multiple selection.

I have found a fairly close solution here: https://dba.stackexchange.com/questions/162845/multi-values-for-ssrs-parameter-with-like-statement

But this solution states to 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 '%") & "%'"

But I am unclear on how to implement this.

How do I select multiple values for the organ parameter and filter the table with the LIKE statement in my query? Thanks.

Upvotes: 0

Views: 1381

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

I would use CHARINDEX to find the REFERRAL_ORGANS in the Join(Parameters!ORGAN.Label, "|").

&"WHERE CHARINDEX(F.REFERRAL_ORGANS'" & Join(Parameters!ORGAN.Label, "|") & "') > 0 ...

CHARINDEX will return 0 if REFERRAL_ORGANS is not found in the concatenated string of the ORGAN parameter labels so your expression will look for greater than one.

Upvotes: 0

Related Questions