Hi, 

I have an SSRS report with several variables, some of which are multi-valued.  The main query that feeds the report uses some table value functions.  For example, the query looks something like this:

select tv1.a, tv1.b, tv2.x, tv2.y, tbl.m, tbl.n
from tableValueFunction1(@MultiValueParam) as tv1
inner join tableValueFunction2(@MultiValueParam) as tv2 on tv1.xxx = tv2.xxx
inner join regularTable as tbl on tv1.xxx = tbl.xxx
where tbl.abc in (@MultiValueParam)

When I try to run this, I get an error saying that I'm feeding the wrong number of parameters into the table value functions.  I've fed multi-valued parameters to stored procedures before and used a splitter function to split out the comma separated values, so I was planning to do the same thing in this scenario.  However, this is different.  When I look at the query in SQL Profiler, it shows discrete values for the multi value parameter.  For example, the same query above looks like this when viewed through the profiler (using 1,2,3 as my multi-value selection):

select tv1.a, tv1.b, tv2.x, tv3.y, tbl.m, tbl.n
from tableValueFunction1(1,2,3) as tv1
inner join tableValueFunction2(1,2,3) as tv2 on tv1.xxx = tv2.xxx
inner join regularTable as tbl on tv1.xxx = tbl.xxx
where tbl.abc in (1,2,3)

In other words, SSRS isn't converting the multi-value parameter to a comma separated string, like it normally does.  Any ideas on how to fix/work around this?

Thanks!
Chris

• Edited by Smithochris Friday, July 24, 2015 11:42 PM

Hi Smitochris,

Please use expression-based command string:

="select tv1.a, tv1.b, tv2.x, tv2.y, tbl.m, tbl.n

from tableValueFunction1('" & Join(Parameters!MultiValueParam.Value, ",") & "'" as tv1
inner join tableValueFunction2('" & Join(Parameters!MultiValueParam.Value, ",") & "'" as tv2 on tv1.xxx = tv2.xxx
inner join regularTable as tbl on tv1.xxx = tbl.xxx
where tbl.abc in ('" & Join(Parameters!MultiValueParam.Value, ",") "