Report Builder parameter LIKE multiple values

0
votes

My dataset query selects values using a LIKE statement:

WHERE unitcode LIKE 'A2%'

So this returns A200, A201, A202, etc.

My report replaces this with a parameter:

WHERE unitcode LIKE @code

where @code can be 'A2%' or 'B2%', selectable via dropdown, to show a report featuring either A2 codes or B2 codes.

Now I want to add a third option that selects both A2 and B2 codes. Is this possible? The way LIKE works seems to prevent me from tying multiple LIKEs up in a single parameter, and I've tried doing WHERE @code with @code being unitcode LIKE 'A2%' OR unitcode LIKE 'B2%' but this causes an error.

2
sql-serverreportbuilder3.0
What is the error? I don't see anything wrong with unitcode LIKE 'A2%' OR unitcode LIKE 'B2%' - Tab Alleman
"An expression of non-Boolean type specified in a context where a condition is expected." You can't just put whole statements inside parameters, I guess. - dhwu

2 Answers

2
votes

You might be able to use SQL Server's enhanced LIKE here:

SELECT *
FROM yourTable
WHERE unitcode LIKE '[AB]2%';

This may solve the query side of your problem, but you'll still need a way to collect the parameter in such a way that the above can be written.

0
votes

I'm running on memory here, as I don't have access to SSRS or report builder right now, however, if I recall correctly both pass a group of values as a delimited string if you use a stored procedure. This is Pseudo-SQL as a result, but you could therefore do something like this using a string splitter (assumes SQL Server 2016+ without a version tagged):

CREATE PROC YourProc @code varchar(100) AS
BEGIN
    SELECT {Your SELECT statement}
    FROM {Your FROM and JOINs}
    WHERE EXISTS (SELECT 1
                  FROM STRING_SPLIT(@code,',') SS
                  WHERE {YT}.unitcode LIKE SS.[value]) --assumes that the report passed values with a % at the end
      AND {Rest of WHERE};                             --Otherwise use LIKE SS.[value] + '%'
END