CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008sql-like
Jason Westover
Jason Westover

Reputation: 48

SQL Server Selecting multiple LIKE values in Report Builder

I have a report where I'm trying to allow the user to select multiple predetermined LIKE values from a drop down list for their results in report builder. Is there a way I can do this? I have tried to use LIKE IN() but those two keywords don't seem to work together. Here is the code that I have. The code I have only works if I select one option.

DECLARE @Warehouse nvarchar(10)
DECLARE @Location nvarchar(10)
SET @Warehouse = 'Warehouse1'
SET @Location = 'IB'

SELECT Part
, Tag
, CurrentLocation AS 'Location'
, TotalQty
, DateTimeCreated 
, datediff(hour, DateTimeCreated, getdate()) AS 'Hours in Location'
, User AS 'Last User'
FROM table1
WHERE datediff(hour, DateTimeCreated, getdate())>=1
AND Warehouse IN(@Warehouse)
AND(CurrentLocation LIKE '%' + @Location + '%')
ORDER BY 'Hours in Location' DESC, CurrentLocation

Upvotes: 0

Views: 832

Answers (2)

Jason Westover
Jason Westover

Reputation: 48

Thank you for your responses. This is what I ended up doing that fixed my problem.

SELECT Part
, Tag
, CurrentLocation AS 'Location'
, TotalQty
, DateTimeCreated 
, datediff(hour, DateTimeCreated, getdate()) AS 'Hours in Location'
, User AS 'Last User'
FROM table 1
WHERE datediff(hour, DateTimeCreated, getdate())>=1
AND Warehouse in (@Warehouse)
AND LEFT(CurrentLocation,2) IN(@Location)
ORDER BY 'Hours in Location' DESC, CurrentLocation

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

This would be best handled with a stored procedure. Here is a high-level description of how it would work. Each of the techniques can be learned at a low-level with some astute googling:

For your report dataset, call the stored procedure, pass your multi-valued parameter to a varchar parameter in the stored proc. For the rest of this answer, we'll call that parameter @MVList

In the stored proc, @MVList will be received as a comma-delimited string of all the options the user chose in the parameter list.

Write your SELECT query from Table1, JOINing to a Table-Valued Function that splits the @MVList (google SQL Split Function to get pre-written code), and produces a table with one row for each value that the user chose.

For the JOIN condition, instead of equals, do a LIKE: 

INNER JOIN MySplitFunction(@MVList, ',') AS SplitFunction
ON Table1.CurrentLocation LIKE '%'+SplitFunction.value+'%'

The result of the query will be the IN/LIKE result you are looking for.

Upvotes: 1

Related Questions