CODEWITHSUNDEEP
t-sqlreporting-servicesbidssql-server-2012-express
tsqln00b
tsqln00b

Reputation: 355

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

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.

Upvotes: 0

Views: 7922

Answers (1)

gannaway
gannaway

Reputation: 1882

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)

Upvotes: 3

Related Questions