Problem With Multi-Select Parameters -drop-down check box

Question

Problem With Multi-Select Parameters -drop-down check box

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 11, 2012 at 12:03 pm

#266416

I thought that if you changed your Stored Procedure from equals to IN then the parameter drop-down list is not replaced with drop-down check box.
AND City IN (@City)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

If you check the Allow multiple values check box in the parameter properties the drop down should become a multi-select list 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Gazareth (1/11/2012)
If you check the Allow multiple values check box in the parameter properties the drop down should become a multi-select list 🙂

Thanks, I tried that and it did switch to multi-select list but when I click on view report I get an error:

An error occurred during local report processing.

An error has occurred during report processing.

Query execution failed for data set 'DATASETNAME'.

Incorrect syntax near ','.

:w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Hmm, using IN @parameter works in SQL 2008, in 2005 you may need to write a function to delimit the parameter into a proper list.

e.g. I think the report will see: @state IN ('NY, TX')

You'll need to write something that will return 'NY', 'TX'

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

Gazareth (1/11/2012)
Hmm, using IN @parameter works in SQL 2008, in 2005 you may need to write a function to delimit the parameter into a proper list.
e.g. I think the report will see: @state IN ('NY, TX')
You'll need to write something that will return 'NY', 'TX'

This is exactly what you need to do.

Here is a good blog on the function:

http://www.bidn.com/blogs/MikeDavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks everyone and thanks:-) for the link.

I had goggled it prior and I read a blog that hinted that I needed a function.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Daniel Bowlin (1/11/2012)
Gazareth (1/11/2012)
Hmm, using IN @parameter works in SQL 2008, in 2005 you may need to write a function to delimit the parameter into a proper list.
e.g. I think the report will see: @state IN ('NY, TX')
You'll need to write something that will return 'NY', 'TX'
This is exactly what you need to do.
Here is a good blog on the function:
http://www.bidn.com/blogs/MikeDavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs

I had to put the report on hold so that I could perform some DBA Tasks but I created the FUnction and modified the Stored Procedure and I stll get the same error.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I ran profiler and it list all of the state but it did not enclose each state with quotes, e.g. 'TX'.

It placed a quote to the left of the first state and another to the right of the last state.

exec usp_CompaniesbyState @City=N'AL,AK,AZ,WY'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 8

Is your splitter function used inside the proc on the @City parameter?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Gazareth (1/13/2012)
Is your splitter function used inside the proc on the @City parameter?

Yes.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 10

I'm not sure what's going on then. I'd expect SSRS to pass the parameter to the proc as one string, as you are seeing in profiler.

What's going wrong inside the proc I can't tell you!

Does it work if you run the report as T-SQL rather than call the proc?