CODEWITHSUNDEEP
sql-server-2008-r2ssrs-2008reporting-services
user1046415
user1046415

Reputation: 789

Optional multi-valued parameters in SSRS

I have 3 multivalued parameters in my report.Out of them 2 are optional. Is there any way to get the results in the report with out selecting any from the 2 optional parameters.

My stored proc checks the mulivalued parameters as (States IN (SELECT * FROM Split(@State,',')))

I have done this for a single valued parameter with WHERE CustomerId = @CustId OR @CustId IS NULL.

WorkAround: I selected 'Allow null value', it is throwing this error "A multi-value parameter cannot include null values"

Can anyone point me in the right direction?

Upvotes: 12

Views: 31113

Answers (1)

Aftab Ansari
Aftab Ansari

Reputation: 946

Have a look at this article on Passing Multivalued parameters.

Basically the above link contains a workaround to meet your requirement for an optional multivalued parameter. The steps to achieve this are as follows:

  1. Replace "ALL" with " " (BLANK) in the parameter's dataset query.
  2. Assign -1 as default parameter value for your multivalued parameter.

Upvotes: 12

Related Questions