[SOLVED] How can i search for multiple "values" in my search box in SSRS REPORT

How can i search for multiple "values" in my search box in SSRS REPORT

I've created a dropdown list that is filtered according the value present in a search box. However I can only search by one field and I want to search for more than one.

I used this query to make possible to use the search box

where upper(ds_in) LIKE upper('%'+@Psearch+'%') or ISNULL(LEN(@Psearch),0)=0

But it only allows me to search for one value and I want to shearch for more. Like imagine you are looking for two countries :

Search box: ITALY, SPAIN

Solution

Assuming your parameter is a simple comma separated list of values then you can use string_split() and then join to the results.

Using your country list as an exmaple...

DECLARE @PSearch varchar(max) = 'Italy , spa'

SELECT * FROM myCountryTable c 
    JOIN string_split(@PSearch, ',') s 
        ON c.myCountryName like '%' + LTRIM(RTRIM(s.value)) + '%'

This will return anything matching %Italy% or %spa%