Passing Multiple Values For a Single Parameter in Reporting Services

Passing multiple values for a single parameter in Reporting Services

Although John Sansom's solution works, there's another way to do this, without having to use a potentially inefficient scalar valued UDF.
In the SSRS report, on the parameters tab of the query definition, set the parameter value to

=join(Parameters!<your param name>.Value,",")

In your query, you can then reference the value like so:

where yourColumn in (@<your param name>)

Passing multiple values to subreport parameter - SSRS

The easiest way to do this is to add a dataset to your main report that returns one row per year.

If you have a dates table or similar in your database then you could do something like

SELECT DISTINCT Year(myDateColumn) as [myYear]
FROM myDatesTable
WHERE Year(myDateColumn) IN (@Year)

If you don't have a date table then (other than suggesting you add one...) you could create one on the fly with something like

SELECT * FROM (
    SELECT top 20 
        ROW_NUMBER() OVER(ORDER BY name) + 2000 as [myYear]
        FROM sysobjects) o 
    WHERE myYear IN (@Year)

(adjust the top 20 and +2000 as required to get a range of years that covers all your potential data)

Now set the dataset property of the tablix in your main report to point to this new dataset.

In your subreport object's parameters, set the value for the Year parameter to the [myYear] field in your dataset by selecting it from the drop down or using =Fields!myYear.Value as the expression.

Now that the tablix is bound to the dataset, it will create one row for each record returned from the "dates" dataset, each row will have a different year which is passed to the subreport, so the subreport is called once for each row/year.

SSRS Report: Combine and pass multiple 'text' parameter in SQL IN clause

Multi-Value parameters are an option. You do not have to use a list with them although they are not very intuitive.

I've created a simple example with a list of country names and codes (e.g. [Country Code] = "IT" and [Country] = "Italy")

A created a multi-value parameter called CountryCode but I did not set the available values.

I added a datset that looked like this

SELECT 
*
FROM myCountryTable
WHERE [Country Code] in (@CountryCode)

When I run the report, I get a blank multi-value parameter field. When you click this field you see an empty list but you can simply type values hitting Enter between each to start a new line.

Once all the values had been entered, I just hit "View Report" and got the following (I've included the parameter as I was editing it for clarity

The advantage of this approach is that the report can handle as few or as many values as you like.

Passing values for multi-value parameter in SSRS query string

Just add additional query string parameters.

For example, to pass the parameters

Date:       2009-06-01
MachineID:  Machine1, Machine2, Machine3, Machine4

to a report named Folder\MyReport on a server named server, you would use the URL:

http://server/reportserver?%2fFolder%2fMyReport&rs:Command=Render&Date=2009-06-01&MachineId=Machine1&MachineId=Machine2&MachineId=Machine3&MachineId=Machine4

---

Related Topics