title: SSRS pass multiple parameters. Given Available Values. Is it possible with LIKE operator
body: <p>I am new to SSRS Reports. I have a stored procedure to extract all the items that have been invoiced for less than 14.9% GP based on the salesman parameter.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_Lessthan15GPAlert]
  -- Add the parameters for the stored procedure here
   @salesrep varchar(100)
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  SELECT  distinct
      oh.CustomerCode
      , c.CustomerName
      , ohl.ProductCode
      , ohl.ProductDescription1
      , ohl.ProductDescription2
      , oh.InvoiceNumber
      , CONVERT(VARCHAR(10), oh.InvoiceDate,126) InvoiceDate
      , ((ohl.NetPrice-ohl.AccountingCost)/ohl.NetPrice) * 100 AS GP
      , OHL.PriceSourceCode
      , OH.SellingWarehouseNumber
      , s.description
  FROM OrderHistory oh 
      inner join orderhistoryline ohl on oh.CompanyID = ohl.CompanyID and oh.OrderNumber = ohl.OrderNumber
      inner join Customer c on c.CompanyID = oh.companyid and c.customercode = oh.CustomerCode
      inner join salesman s on s.companyid = c.CompanyID and s.SalesmanID = c.SalesRepOne
  WHERE oh.CompanyID = '002' 
      and oh.InvoiceDate = CONVERT(varchar(10),dateadd(d,-4,GetDate()),126)
      AND ((ohl.NetPrice - ohl.AccountingCost)/ohl.NetPrice) * 100 &lt; 14.9 
      and ohl.NetPrice &lt;&gt; 0.00
      AND OHL.PRICESOURCECODE NOT LIKE 'M%'
      and s.description like @salesrep
      AND COALESCE(OHL.ShippedQuantity,0) &lt;&gt; 0
  END
  </code></pre>
  
  <p>The description column in salesman table has sample data as </p>
  
  <pre><code>R02 702 SMITH, JOHN TSR
  R07 707 FRANKLIN, ROB TSR
  R04 704 SCOTT, KEVIN TSR
  R04 704 PAUL, KEITH RSM
  R02 702 JOSEPH, MARY TSR
  R04 704 CRIPPS, ROBERT TSR
  </code></pre>
  
  <p>Keith Paul manages two locations both 702 and 704.</p>
  
  <p>I have set up available values for parameter @salesrep as follows</p>
  
  <pre><code>| Label | Value
  
  %SMITH%JOHN% | %SMITH%JOHN% 
  
  %FRANKLIN%ROB% | %FRANKLIN%ROB%
  
  %SCOTT%KEVIN% | %SCOTT%KEVIN%
  
  %PAUL%KEITH% | %PAUL%KEITH% 
  
  % | %  
  
  L702 | R02%702%
  
  L707 | R07%707%
  
  L704 | R04%704%
  </code></pre>
  
  <p>Now, I can run the report for each salesman and each region but I also want to run for each RSM (Regional Sales Manager). Suppose I want to run everything under Keith Paul. How can I add one more in available values for parameter such as</p>
  
  <p>Label  | Value</p>
  
  <p>Keith Paul  | R02%702%, R04%704%</p>
  
  <p>So I would get results both from 702 and 704. How can I achieve this? </p>
  
  <pre><code>R02 702 SMITH, JOHN TSR
  R04 704 SCOTT, KEVIN TSR
  R04 704 PAUL, KEITH RSM
  R02 702 JOSEPH, MARY TSR
  R04 704 CRIPPS, ROBERT TSR
  </code></pre>
  
  <p>Edited: I used case statement in where clause, and added Keith-Western in available values and it worked. Advise me if there is a different solution to this by changing the query.</p>
  
  <pre><code>WHERE oh.CompanyID = '002' 
  and oh.InvoiceDate = CONVERT(varchar(10),dateadd(d,-4,GetDate()),126)
  AND ((ohl.NetPrice - ohl.AccountingCost)/ohl.NetPrice) * 100 &lt; 14.9 
  and ohl.NetPrice &lt;&gt; 0.00
  AND OHL.PRICESOURCECODE NOT LIKE 'M%'
  and (CASE @salesrep WHEN 'Keith-Western' then s.description end like 'R02%702%'
          or
  CASE @salesrep WHEN 'Keith-Western' then s.description end like 'R04%704%'
          or s.description like @salesrep)
  AND COALESCE(OHL.ShippedQuantity,0) &lt;&gt; 0
  </code></pre>