Its more straight forward than you think.

 Passing MultiValue paramaters to a Stored Procedure in Reporting Services.

I have been working on a Reporting Services report that contained a MultiValue select list as one of the parameters and I needed to pass this comma separated list of values to my stored procedure. I had prepared my stored procedure so that it contained a user defined function to split out the values into a table and had tested my stored procedure independently of the report. My problems occurred when I tried to run my report and select multiple from my list. I would end up with the following error – ‘must declare scalar variable @VarName’. To confuse things a little further, if I just selected one item from the MultiValue list then my report would run fine.
The answer, according to other enlightend ssrs users, comes from the method used to define your dataset. I had originally defined my dataset using the Text Command Type to call the stored procedure. E.g. EXEC myStoredProc @MultiValueParam. To get stored procedures to use the multivalue paramaters the dataset needs to be created with a Stored Procedure Command Type and then define the parameters within the Parameters tab.
After making the change my report would run successfully using my multivalue prameters.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s