What sort of syntax should I be using for the following scenario:
Select * from vs_hgo_contributions where campaign in (@camp)
I've clicked the multi-value checkbox under the properties for the @camp report parameter so that people may choose more than one. Do I need single quotes around it? How does Reporting Services insert that code in there in the aforementioned case, as text? numerical values separated by commas? Me not know. Me am ignorant.
Thank you in advance for your assistance, Tessiturians.
THIS DAY IN HISTORY: The first colonial constitution (1639).
No takers on this one, eh? Oh well.
Two options for you Matt:
Select * from vs_hgo_contributions where campaign in (select element from dbo.FT_SPLIT_LIST(@camp,',')
or
select * from vs_hgo_contributions
where charindex(','+convert(varchar,campaign)+',' , ','+@camp+',') > 0
Hope this helps.
David
There are a few differences between multi-select parameters in Tessitura, which are just comma-delimited strings, and multi-select parameters in BIDS (Report Designer/Visual Studio), which are arrays.
For one, you can’t pass an SSRS multi-select parameter to a stored procedure without first reformatting it (most likely to a delimited string). You also can’t directly pass a Tessitura “multi-select” parameter value to a multi-select parameter in SSRS.
If you are doing a direct SQL call inside the SSRS report dataset, you CAN do exactly what you have below using an SSRS multi-select parameter:
Select * From SomeTable Where Column in (@MyMultiSelectParameter), which gets translated at runtime to:
Select * From SomeTable Where Column in (1,2,3,4)
Otherwise, you’ll need to translate it into a delimited string if you are passing this value to a stored procedure. An online search should give you a few ways of accomplishing this (you can try the Join() function).
-Ryan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matt GonzalesSent: Monday, January 17, 2011 6:35 PMTo: Ryan CrepsSubject: Re: [Tessitura Technical Forum] Visual Studio Multi-value parameters
From: Matt Gonzales <bounce-mattgonzales9808@tessituranetwork.com>Sent: 1/14/2011 2:01:39 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Matt,
I didn't see that this was SSRS. Here is a forum post where I talked about getting the multivalued parameters into the stored procedure, where the techniques I talked about above will work:
http://www.tessituranetwork.com/COMMUNITY/forums/t/3603.aspx