Multiple parameter ODBC

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Multiple parameter ODBC

Multiple parameter ODBC

Rate Topic

Display Mode

Topic Options

Author

Message

rkaria

Posted Wednesday, January 16, 2013 4:16 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 25, 2013 7:15 AM
Points: 88, Visits: 186

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

Post #1407734

Gazareth

Posted Wednesday, January 16, 2013 8:51 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

Post #1407893

rkaria

Posted Wednesday, January 16, 2013 9:27 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 25, 2013 7:15 AM
Points: 88, Visits: 186

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

Post #1407928

Gazareth

Posted Wednesday, January 16, 2013 10:01 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

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.

Post #1407972

rkaria

Posted Thursday, January 17, 2013 6:33 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 25, 2013 7:15 AM
Points: 88, Visits: 186

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

Post #1408393

Gazareth

Posted Thursday, January 17, 2013 8:10 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

Post #1408439

rkaria

Posted Thursday, January 17, 2013 8:54 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 25, 2013 7:15 AM
Points: 88, Visits: 186

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.

Post #1408475

Gazareth

Posted Thursday, January 17, 2013 9:16 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Excellent, thanks.

Post #1408492

« Prev Topic | Next Topic »

Permissions