using a multi-valued parameter in SSRS 2012

Question

using a multi-valued parameter in SSRS 2012

pietlinden

SSC Guru

Points: 63428
More actions
August 8, 2013 at 11:54 pm

#296782

If I use a single-valued parameter, it works a champ.
I'm trying (and apparently failing) to follow Brian Knight's example, which is here: [/url]
I changed my stored procedure, so now it looks like this:
ALTER PROC uspEnrollReport
@StartWeek INT,
@EndWeek INT,
@ProtocolList VARCHAR(1000)
AS
BEGIN
WITH EnrollData (Protocol, StudyWeek, WeeklyGoal, ActualEnrolls) AS
(
SELECT eg.ProtocolNo, eg.WeekNumber, eg.Goal, COUNT(e_PatientID) AS EnrollCount
FROM EnrollmentGoal eg
INNER JOIN enroll e ON eg.ProtocolNo=e_ProtocolNo
AND eg.WeekNumber=PWeek
GROUP BY eg.ProtocolNo, eg.WeekNumber, eg.Goal
)
SELECT Protocol, StudyWeek, WeeklyGoal, ActualEnrolls, ActualEnrolls - WeeklyGoal AS OverUnder
FROM EnrollData
WHERE StudyWeek BETWEEN @StartWeek AND @EndWeek
AND Protocol IN (@ProtocolList);
END
I changed the parameter to be multi-value (check the box) - simple enough.
Everything appears to work, but when I run the report, it only filters for the first checked item. What blindingly obvious thing am I doing wrong?
Thanks!
Pieter

Viewing 6 posts - 1 through 5 (of 5 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

As I read this thread, it doesn't seem to be that easy (maybe Brian used integers in his video, which is a lot easier than strings):

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/reporting-services-multi-value-parameter

You need to parse the values of the @ProtocolList in your stored procedure before you use it in the IN clause.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pietlinden SSC Guru Points: 63428 More actions · Answer 2

Koen Verbeeck (8/9/2013)
As I read this thread, it doesn't seem to be that easy (maybe Brian used integers in his video, which is a lot easier than strings):
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/reporting-services-multi-value-parameter
You need to parse the values of the @ProtocolList in your stored procedure before you use it in the IN clause.

How and where do I do that?

(FWIW, if I select a single value from the multi-select, it works...)

Thanks!

Pieter

RandomEvent SSCrazy Points: 2963 More actions · Answer 3

Have you tried outputting the value produced to a table or something to have a look at the results. This might give a greater insight into why the values are not working correctly.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

The thread I mentioned gives some examples.

This article takes a lightly different approach:

SQL Server Reporting Services Using Multi-value Parameters[/url]

It uses a splitter to split the parameters, put it into a table on which you can join in your stored proc.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pietlinden SSC Guru Points: 63428 More actions · Answer 5

Okay, got it finally. Thanks!

The trick that I wasn't seeing was that you can't filter on the column with the multi-valued parameter in the stored procedure, you have to filter in the report. Once I got my head around that part, the rest was easy!