How to pass multiple values to a multivalued parameter in SSRS

Question

I will try to explain the issue as best as I can by oversimplifying the report structure. Report one contains 1 group called ResourceCenter and then one line of totals under it. The totals are actually a group but the grouping is done in SQL and are presented in a detail group. The report looks something like this:

Report 1

ResourceCenter 1
Total1 11 
Total2 4
Total3 8

ResourceCenter2
Total1 12
Total2 11
Total3 6

From this report, I need to drill through to another report that has a bunch of multi-valued parameters. For the drillthrough, I am able to use single values for everything except for EmployeeNumber. For that, I need to be able to pass a list of EmployeeNumbers to the multi-valued parameter in Report 2. The EmployeeNumbers are not currently present in any DataSet or parameter in Report 1 but are based on ResourceCenter. So, if the user has run Report 1 and clicks on ResourceCenter 1, I need to be able to pass a list of EmployeeNumbers associated with ResourceCenter 1 to the multi-valued parameter in Report 2 in a way that Report 2 will handle it correctly.

NOTE: I should add that I have created two SQL functions that accept an input of ResourceCenter and then return a list of employees. One is a table-valued function that returns a single column of EmployeeNumbers. The other is a scalar-valued function that returns the EmployeeNumbers as comma-separated values. I then have some custom code that runs the SQL function in the background and returns the list. I have not had any success with returning a dataset that SSRS can use but I have been able to get the scalar-valued function to 'work' in the sense that I can create a field on a dummy report and see the output. I have not had any luck getting Report 2 to accept a comma-separated list, though.

Genteelism

Answer 1

This person was doing a drill-through and appears to have solved a similar problem with a multi-value parameter. In that case it had to be formatted for an IN clause.

=SPLIT(JOIN(Parameters!SomeParameterName.Value,","),",")

If Report2 won't take it in this format, you might have to add a separate single-valued parameter that will accept a comma-separated string, which you then have to parse.

Veer

Thanks for your reply. I am familiar with using SPLIT/JOIN but that will not work in this situation. I do not have a parameter or a field to pass, only a value that can be passed to derive a list. – Genteelism

You may have to derive a list from a delimited string. – Veer

Hi. Can you please elaborate on how I could accomplish that? I added a note to my original question explaining what I have so far. It sounds like I may be on the right path but I will need some assistance in seeing it come to fruition. – Genteelism

Report 2 accepts a single-value parameter Par1, which is the comma-delimited list. Report 2 also has a (hidden) multi-value parameter Par2, which has a Default value "From query", where the query is a Dataset you rigged up to parse the comma-delimited value Par1 and return a table of the individual values. Then you use Par2. This looks a bit different in SSRS 2005 and 2008 but I think it should work in either one. – Veer

Intriguing. I will give that a shot and report back. – Genteelism

So, I was able to do what you suggested. I have custom code in Report 1 that creates a string from table values. When the user clicks on a Resource Center in Report 1, this string is passed to a string paramter in Report 2 which is then parsed in SQL code and used to run the report. This runs perfectly in Visual Studio but does not run when deployed to the server. There is no error but no string is passed from Report 1 to Report 2. The paramter appears as blank. – Genteelism

Odd - I haven't had reports drop parameters on drillthrough like that. So Report2 comes up blank or error? You've uploaded the latest version of both reports? And they both run individually? Can you execute Report2 manually and pass it the list? Since you're using SSRS 2005, I might drop the reports entirely and redeploy them from scratch. I'm puzzled. – Veer

Answer 2

I'm using SSRS 2016, and my datasets are based on stored procedures, but passing multi-value parameters to a drill down doesn't seem to be an issue anymore. By default, when you select a multi-value parameter it gives you something like Parameters!ParName(0).Value which would pass just the first value. But if you remove the (0) and just leave it as Parameters!ParName.Value, it seems to be passing all values fine.

Ventilate