How do I utilize a multi-value Parameter in a query for SSRS report?

Question

I am working on a report in SSRS that has a Parameter that can have multiple values. I can pass one Parameter value to the query just fine. I am using IN @Paramter in the query for multiple values and I also made the Parameter accept multiple values. However, when I want ALL the values, the query does not work.

Answer 1

There are several answers on SO related to passing multi-valued parameters to a dataset query in SSRS. See this --> Passing multiple values for a single parameter in Reporting Services.

I wasn't able to reproduce the scenario you described (choosing one value works, but ALL values don't work). However, I implemented one of the answers from the link above (Minks) using SQL Server 2008 as the data source. Below are the details:

I have a parameter named @ReportParameter1 that is populated from the following Dataset query (Value field = id, Label field = description):

select 1 as id, 'choice1' as description union
select 2 as id, 'choice2' as description union
select 3 as id, 'choice3' as description union
select 4 as id, 'choice4' as description

Then I have my report Dataset query as:

select * from 
(select 1 as id union
 select 2 as id union
 select 3 as id union
 select 4 as id) x
 where id in (@ReportParameter1)

Under the Parameters tab for the report Dataset, I have the following expression set for @ReportParameter1:

=Split(Join([email protected],","),",")

If I choose ALL values for @ReportParameter1, then the query effectively becomes:

select * from 
(select 1 as id union
 select 2 as id union
 select 3 as id union
 select 4 as id) x
 where id in (1,2,3,4)