SSRS Passing multi-select parameter to report

0
votes

I have this report which gets data from a storedprocedure. I need to pass CustomerIDs from a multi-select dropdown to my report.

My storedprocedure looks like this :

@CustomerIds varchar(500)

select *
  FROM [Application].[ApplicationVersion] APPVERS 
 WHERE APPVERS.CustomerId IN ( @CustomerIds )

  • On my report I have the parameter "CustomerIds", the data type is set to Integer (I tried it with "TEXT" but it didn't work either). The parameter is set to "Allow multiple values"

  • On the report where my dataset is defined, I have the @CustomerIds defined as follows : "=SPLIT(JOIN(Parameters!CustomerIds.Value,","),",") " (I tried it with the SPLIT only, with the JOIN only and it didn't work)

Can anyone help me?

1
reporting-servicesparametersmulti-selectssrs-2008-r2

1 Answers

7
votes

The way I usually approach this is to use a filter.

  • Remove the where clause from your SQL Query.
  • Right click the dataset and select Properties > Filters.
  • Add.
  • Choose the field you want to filter on in the expression drop down. In your case, "CustomerId"
  • Choose 'In' as the operator.
  • Press the 'fx' on the value to create an expression, click parameters, then elect your parameter from the list. Make sure you remove '(0)' from the end, as this only takes the first selected value.

enter image description here