In my experience with SSRS, I have noticed that there are two types of report developers:
1) SQL Developer that naturally flowed into SSRS, uses Stored Procedures for database development and has a very solid background for development.
2) Developers from other categories that somehow fell into SSRS.
I come from the second school but I have an endless desire for learning about technology and have been fortunate enough to work with some really intelligent developers.
Which brings me to one of the more common issues in SSRS - Passing multi-value parameter into a stored procedure.
Munish Batel does an excellent job of explaining the issue here:
http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/
However, I am going to go into an even crazier amount of detail and provide some tips and things to remember.
1) Know your stored procedure. When you are getting into the realm of passing of multi-value parameter into a stored procedure, make sure you know the total size of the parameter in your SP as well as the total number of choices you can have in the multi-value (MV) parameter.
- For example - we had a developer setup a MV parameter that could have up to 10,000 items checked (!!!) in the list. In the stored procedure, the parameter definition was varchar(50). You can see where this would go wrong.
2) If you are using the solution posted above, it is important you understand you will need to code a UDF function to split up the parameter - Split in VB land. This URL does a good job of explaining that as well:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
I am partial to this function:
-- 7998 bytes max (but faster)
CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @param + ',', Number + 1,
charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @param + ',') - 1
AND substring(',' + @param + ',', Number, 1) = ',')