Passing Multi-value parameter to drill through SSRS report.

Last week I was building Report A with a multi value parameter called @Hostname. The values selected for the @Hostname parameter should be passed to Report B with the same multi value parameter. This failed because the dataset expects a valid IN clause. I will explain what happens with an example:
Multi value Parameter: @Hostname
Dataset: Select col1, col2 col3 FROM TableX WHERE Col4 IN (@Hostname)

In this case reporting server tries to add multi values like:
Select col1, col2 col3 FROM TableX WHERE Col4 IN (Server1,Server2,Server3)

This query syntaxt is wrong. It should be
Select col1, col2 col3 FROM TableX WHERE Col4 IN ('Server1','Server2','Server3')

This can be achieved by using next expression in the action for the parameter field:

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

With this expression the correct TSql syntax will be generated.

Happy building your reports. Enjoy It.

Picture: Mount Cook, New Zealand Dec 2011.