SSRS Report. Passing multiple values to a single parameter in a new report

0
votes

I have a two charts in a SSRS report that jump to the same report. I would like to pass values to the new report that are different for each chart. I would like to define those values myself and pass them through a parameter to the dataset of the jump report. How can I do this? The datatype for Division is INT and I'm getting a conversion to ncharvalue error.

Example:

Chart1 - Parameter @Division - Values - 1000,2000,3000,3500,5000,8000

Chart2 - Parameter @Division - Values - 6000,7000

ds_JumpToReport

SELECT * FROM Table WHERE Division IN (@Division)

I have tried using the following as a value for the parameter in the dataset:

=JOIN(Parameters!Division.Value,",")

However, I get an error with that too. "The value provided for the report parameter 'Division' is not valid for its type."

If I DECLARE @Division nvarchar(max); in the query, I get no results back on the report.

1
reporting-servicesssrs-2008-r2
You are comparing an int to nvarchar when you use the join which creates a comma-separated string. – ShellNinja
So, how do I resolve that? – tsqln00b
That's why I left a comment not an answer. – ShellNinja

1 Answers

0
votes

You can use one of the many forms of the Split function (google "tsql split function" for some examples) to turn your comma-delimited string into a table. Then just change your ds_JumpToReport to:

SELECT * 
FROM Table 
WHERE Division IN (SELECT * FROM dbo.Split(@Division))