Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple parameter ODBC Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 4:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
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
Posted Wednesday, January 16, 2013 8:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
Try changing =join(Parameters!Alloy.Value, ", ") to just =Parameters!Alloy.Value
Post #1407893
Posted Wednesday, January 16, 2013 9:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
Thanks for the reply. Unfortunately still no data. Work if I select one value but more than one gives no data.
Post #1407928
Posted Wednesday, January 16, 2013 10:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Posted Thursday, January 17, 2013 6:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
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
Posted Thursday, January 17, 2013 8:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Posted Thursday, January 17, 2013 8:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:32 AM
Points: 92, Visits: 189
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
Posted Thursday, January 17, 2013 9:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
Excellent, thanks.
Post #1408492
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse