SQL Server Reporting Services (SSRS) – How to pass multiple values to a multi value parameter

The short answer is to pass a string array in the value portion of the parameter object.

First, let’s look at the code in a visual basic forms application that builds the parameter and calls the report.

Let’s say we have a collection of last names:

Dim mNameCollection As New List(Of String)
 With mNameCollection
 .Add("DOE")
 .Add("SMITH")
 .Add("MOORE")
 .Add("OBRIEN")
 .Add("LIVINGSTON")
 End With

I am now going to iterate through that collection and build a string array (mStringArray), using the count -1 of my collection to first determine the size of my array.

Dim mStringArray(mNameCollection.Count - 1) As String
Dim mInt As Integer = 0
For Each pObj In mNameCollection
        mStringArray(mInt) = pObj
        mInt += 1
Next

Next, I instantiate a parameter array with one occurrence and pass to it my parameter name of my SSRS report (“LastName” – called @LastName in the SSRS report), and the string array, “mStringArray” as the value for the parameter.

myParms(0) = New Microsoft.Reporting.WinForms.ReportParameter("LastName", mString)

I set the parameters for my report and lastly (not shown here), call the report.

Me.ReportViewer1.ServerReport.SetParameters(myParms)

The SSRS Report

Define an SSRS report. Create a report parameter and name it LastName. Check the box that states ‘Allow multiple values’.  Also, set the parameter visibility to ‘Hidden’.

You can set a default if you want so you can test the report in the solution.

Lastly, in your dataset, use the parameter with the IN clause of your where statement. For example:

SELECT * from name_table
 WHERE last_name IN (@LastName)

Leave a Reply

Your email address will not be published. Required fields are marked *