Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 621
user1046415 Avatar asked Oct 16 '12 14:10

user1046415


People also ask

How do I pass multiple values to a parameter in SSRS?

In the Report Data pane, expand the Parameters node, right-click the report parameter that was automatically created for the dataset parameter, and then click Parameter Properties. In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter.

How do I select all by default in multi-value parameter SSRS?

In order for Select All to be the default option, the Available Values dataset must contain at least 2 columns: value and label. They can return the same data, but their names have to be different. The Default Values dataset will then use value column and then Select All will be the default value.


1 Answers

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.
like image 130
Aftab Ansari Avatar answered Sep 28 '22 10:09

Aftab Ansari