0

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.

tsqln00b
  • 355
  • 1
  • 4
  • 20
  • What do you mean by 'the query does not work'? Do you get any kind or error? Does the query work when you select a multiple values but not all values? – JodyT Dec 13 '13 at 20:55
  • The report returns no data when I try to pass multiple values. I'm not sure how to setup the parameter on the report and how to assign the values properly. – tsqln00b Dec 13 '13 at 21:00
  • 1
    You don't have a dataset for the values associated with the parameter do you? – ShellNinja Dec 13 '13 at 21:50

1 Answers1

3

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(Parameters!@ReportParameter1.Value,","),",")

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)
Community
  • 1
  • 1
gannaway
  • 1,872
  • 12
  • 14