Wednesday, May 23, 2012
Pass Multi Value Parameter to SSRS Report Using URL
I have published a post that explaining about Passing Parameters to SSRS Report using URL few months ago. That post solves one problem but still have some other problems. When we going to pass a multiple value parameter then that is stuck. The reason is we cannot pass 1-dimensional array through an URL.
When we want to pass dynamically changing multiple values to SSRS report through URL try the following way.
We have our original report and target report. The error says exactly cannot pass a string array. So I have to pass values as one string. What I’m going to do is pass all the values as string to temporary parameter in the target report and then split those values.
In Original Report
I want to pass Status (which is a multi-valued parameter) parameter’s values to my target report. Using Join I can pass the parameter values separated by commas as one string. That’s the only thing we should do in our original report. Don’t bother about param1. For the time being use it as following.
(Above code is clearly described in Passing Parameters to SSRS Report using URL)
In Target Report
Then we comes to target report. I have to set those values to parameter called MonthlyStatus in my target report. Before that we need to add new parameter for target report. I named it as ‘param1‘ ( I think now you can get the idea about param1 which I used in above expression. Instead of directly passing to MonthlyStatus I use param1). param1 is a new parameter, which haven’t any available values or any default values. Set parma1 as a hidden parameter.(Because no one want to see that )
Then we come to the simple part. Now we have all the values separated by commas in param1 and need to split each value and set to related parameter. We can do this by editing the default value expression of the related parameter (in this case MonthlyStatus)
Make sure param1 is placed above than MonthlyStatus of the parameter order. Because MonthlyStatus using param1 value as default value.
Note: You cannot see the report preview in BIDS. But after deploying the report you can see that multi-value parameter’s values are passing to target report.
There may be some better solutions for this ! If then please leave as comments.