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.
2 comments:
Hi André,
I came across your post about this topic and it is exactly what I am running into. I too am trying to use an "Action" to call upon another RDL and I need to re-use a multi-select parameter from the 1st RDL and again use it in the 2nd RDL.
Can you please tell me how you are setting this up in the 2 RDLs? I understand your explanation of the error, but what I don't understand is what your dataset and parameter setups look like to accomplish this challenge.
Thanks!
Hello André,
5+ years later and this was by far the easiest solution compared to everything else out there! Many thanks!
davelr
Post a Comment