Search code examples
reporting-servicesssrs-2008ssrs-2012

SSRS multivalue parameter to multi-row table

I want to generate a multi-row table with a multi-value parameter.

The only thing i can do now is passing the specified parameter into every row like below: 

=Parameters!P.Value(0)  
=Parameters!P.Value(1)  
...  
=Parameters!P.Value(n)

Is there a way to do it dynamically?

Solution

  • Interesting question, the only I can think of is converting the parameter array into xml in a dataset, and then converting it into a table via xml nodes.

    The data set parameter (lets call it @par) then should be set in a expression as:

    = "<n>" + join(Parameters!P.Value,"</n><n>") + "</n>"

    Then the query for the dataset will be this:

    declare @parxml xml = @par
Select p.query('./text()')
from @parxml.nodes('/n') as T(p)

    this will provide a table with one row per each selected parameter value.