Multiple parameter ODBC

Question

Post reply

Multiple parameter ODBC

rkaria

Ten Centuries

Points: 1363
More actions
January 16, 2013 at 4:16 am

#393949

Hi
I have created a report in BIDS 2008 that connects via ODBC connection.
I am having issue with the parameters for multiple values.
In the Dataset query I have :
(M10.PARTNO in (:Alloy))
and in the dataset properites under parameter I have:
=join(Parameters!Alloy.Value, ", ")
This works if I select one value from the drop down, but if i select more than one it returns no data.
Can anyone please advise?
Thanks
R

Viewing 8 posts - 1 through 8 (of 8 total)

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

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

Try changing =join(Parameters!Alloy.Value, ", ") to just =Parameters!Alloy.Value

rkaria Ten Centuries Points: 1363 More actions · Answer 2

Thanks for the reply. Unfortunately still no data. Work if I select one value but more than one gives no data.

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

Ok, think you'll need to split the parameter out in the query like the one here[/url]

So M10.PARTNO in (:Alloy)) becomes M10.PARTNO in ((SELECT Item FROM dbo.DelimitedSplit8K(:Alloy, ',')))

Assuming you're using MS SQL as your datasource, which I'm not sure you are.

rkaria Ten Centuries Points: 1363 More actions · Answer 4

Thanks

Im writing it against Oracle 11g

Is the "dbo.DelimitedSplit8K" part of the query you have written what I should type or should i replacing it with something else? At the moment it is saying 'Right paraenthisis missing' I have checked and there are the right amount of brackets there.

Thanks

Rakhee

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

dbo.DelimitedSplit8K is the user-defined function described in the article I linked to in my second reply. It's only valid in MS SQL though.

It's basically a bit of code that will convert the parameters into the correct format:

The parameters are being passed to the report query as 'Brass, Bronze' - you need something to convert it into 'Brass', 'Bronze'

This link might help you further:

http://social.msdn.microsoft.com/Forums/uk/sqlreportingservices/thread/31c1ab35-2b78-4d30-9fec-7c95e1cd7d0a

Cheers

Gaz

rkaria Ten Centuries Points: 1363 More actions · Answer 6

Thank you for your help, I found the issue.

I changed my conenction type to Oracle rather than ODBC and now your first suggestions works.

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

Gazareth

One Orange Chip

Points: 27737

January 17, 2013 at 9:16 am

#1578372

Excellent, thanks.

Multiple parameter ODBC

Cookies on SQLServerCentral