parameterizing multi valued parameter in data set

Hi,

I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.

To consider a sample code:

EXEC('select * from country where country_key in ('+@.country+')')

where @.country is a multi-valued report parameter.

Regards,

Emil

google is a wonderful thing
http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/9d9739713fe89f5f/11b4fc22ce2e9e0b?lnk=st&q=multi+value+parameter+%22reporting+services%22&rnum=3#11b4fc22ce2e9e0b|||

Hi,

i am facing similar problem. I was trying dynamic SQL for the performance of the report.

But got stuck on multivalued parameter......

For me

Declare @.sql varchar(4000), @.rcc varchar(4000)

set @.rcc='a'; -- now if more than one value will be passed to it, this query will fail, it will execute but return nothing.

select @.sql='SELECT * FROM abc '

select @.sql=@.sql + 'where abc_column in (''' + @.rcc + N''')'

Exec( @.sql)

I am not sure creating a function and using it to split the parameter values finally result as a plus or minus to the performance.

If any one has any other way of doing this, Pls help.

|||and did you bother reading the link?|||

Try this

create another parameter new_param_country which is single valued

make it hidden

give value as

="'" & Join(Parameters!country.Label, "', '") & "'"

Give the same expression for default value too

For eg:- if the countries selected are FRANCE, INDONESIA and ICELAND, the value of the new parameter will be 'FRANCE', 'INDONESIA', 'ICELAND' which is a string

Use this parameter in the dataset instead of the multivalued parameter.

Regards,

Meenu

|||

Thanks a lot Meenu. :-)

Works fine for me. Just did a little change to the expression

=Join(Parameters!country.Value, ", ")

as I needed only value.

Regards,

Emil

|||

This is exactly what I've been looking for to put all if the multi-value parameter values in the page header.

Thanks!

-Marianne