SSRS Filter a dataset with a multiple value parameter

  • Article

Problem: Passing a multiple value parameter to a report source

 

Solution 1:

 

You can pass the parameter to a stored procedure as a comma separated string and then split it in the where clause.

 

CREATE PROC [dbo].[example_procedure]

(

@CheckDateFrom DATE         = NULL

   , @CheckDateTo   DATE         = NULL

   , @BankCode      VARCHAR(MAX) = NULL

)

AS

 

   BEGIN

 

   SELECT

[c].[BANK_CODE]

   , [c].[CHECK_DATE]

   , [c].[CHECK_NUM]

   FROM

  [dbo].[CHECK] AS [c]

   WHERE

 1=1

 AND c.[CHECK_DATE] BETWEEN @CheckDateFrom AND @CheckDateTo

 AND c.[BANK_CODE] IN(SELECT value FROM STRING_SPLIT(@BankCode, ',') WHERE RTRIM(value) != '');

 

   END;

Solution 2:

 

You can create a Boolean filter in the dataset.

 

Expression  =IIF(InStr(("'" + Join(Parameters!BankCode.Value, "', '") + "'"), ("'" + Trim(Fields!BANK_CODE.Value) + "'") ) > 0, True, False)