SSRS Parameter not accepting multiple values

Question

I'm trying to create a parameter that will accept multiple values for the report. The report runs off a procedure and works if I only select one collection operation but when I try to select multiple, I get:

An error has occurred during report processing.
Query execution failed for dataset 'proc_Incentives'
Error converting data type nvarchar to int

In my procedure, I declared @CO_ID int as a variable.

In the WHERE clause, I set CO.CodeID in (@CO_ID).

Also, CO.CodeID is stored as an INT in the database.

In my SSRS report, I created a new dataset named 'prmCollectionOp' in which I wrote a simple query:

Select CodeID, Description from IDViewCollectionOp Order by Description;

Then I went created a new parameter named 'CO_ID' and made the data type Integer and checked "Allow Multiple Values".

Under Available Values, I chose Get values from a query, chose prmCollectionOp and placed CodeID in the value field and Description in the label field.

For Default Values, I also chose Get values from a query and picked dataset prmCollectionOp with Value Field of CodeID.

This all works with one single collection operation chosen, but not with multiple.

Any suggestions on what I might have missed to allow for multiple values to be selected in the parameter?

Thanks,

Bactos Bactos · Accepted Answer · 2015-01-28T18:17:09

I know this is a tricky one: I am going to recommend several articles which explain how to do this.

One is from Scott Murray and the other is from Jeff Moden (Scott actually refers to Jeff's article in his).

For a stored proc you can do the following

Scott explains:

First we are passing in the concatenated multi-value parameter as a single entity and we use a varchar parameter (which must be large enough to accept the maximum length of all the parameters which might be selected). Next using the splitter function, the values are parsed out and placed into a temporary table, #YEAR_LIST. Last, the year criteria is moved from being part of the where clause to being part of the joins.

There are other ways as well but this should solve the issue.

Hope this helps!

SSRS Parameter not accepting multiple values

2 Answers