SSRS – Passing Multi-Value parameters between reports

Not posted for a while but this one was worth remembering…

In order to pass multi-value parameters from one report to another you need to use a combination of the following:

StrToSet – MDX function that converts a string to an MDX set

Split – An SSRS expression function that create an array object from a comma seperated string

Join – An SSRS expression function that create a comma seperated string (in this case from a set of parameter values)

In both reports you need a parameter that is set to accept multiple values – Lets call it prmMulti (in both reports, although the name could be different in each report)

The parameter should have values that look like MDX members ie [dimension].[attribute].&[member] – You can use currentmember.uniquename for this.

In the parent report go to the text box you want to click to drill to the child report and under navigation/action select prmMulti and enter an expression for the value to pass down as follows:

=Split(Join(Parameters!prmMulti.Value,”,”),”,”)

The join function creates a comma seperated list from all the selected parameter values and the split function then converts this into an array object – This is what gets passed to the child report.

Thanks go to Erika for this one – http://erikasblog.datainspirations.com/2010/01/21/passing-multi-value-sql-parameters-to-an-mdx-query-in-reporting-services/