SSRS 2012 - Sending multiple values in parameter to main dataset

0
votes

I have a report that is up and running but I need to restrict the data on a parameter that takes multiple string-valued staff codes.

  1. I created a parameter called Practioner which is a text type, ticked on Allow Multiple values and also on Visible.

  2. In the available values, I made this code come from the field called mbillaty from the dataset3 under the Get values from a query option. I've checked data3 is fine in SQL server.

3.Now, I've gone to my dataset1 where all the reporting data sits then put the parameter, with the name ? and =Join(Parameters!Practitioner.Value,", ") for the parameter value. Then in the query, in the where clause, I've put: ......mbility in (?) What should this SSRS be interpreting as is : .......mbility in ('AAB','KKR','RDR'), if the user picked these three staffs.

When I run the report, it definitley works when I check one of the practioners, but as soon as I more than one, the report wouldn't run. It does not return any error, but just the header shows, which I think means that no data is found.

Experts, do you see where I've gone wrong with this?

Thanks

1
visual-studio-2012reporting-services
This seems to be a duplicate of stackoverflow.com/a/15251406/2514839. Your join statement is creating a single value that is a concatenation of the selected parameter values. You don't need this part. Just make sure the where clause in your query contains "where FIELD IN (@Practitioner)" and SSRS will parse it out correctly. – mmarie
possible duplicate of How to use multivalue parameters in SSRS – mmarie
Thanks guys. I actually read this thread but I still couldn't resolve my issue. I've put =Parameters!Practitioner.Value instead of what I had, and the report is not working at all even for one parameter. SSRS says "Cannot add multi value query paramter '?' for dataset DataSet1 because it is not supported by the data extention'. What is this error about? – Will Kim
where do you have a question mark? – mmarie
social.technet.microsoft.com/Forums/sharepoint/en-US/… – mmarie

1 Answers

0
votes

If you're able to create UDFs, this link might be helpful. How do I split a string so I can access item x?

The parameter would be parsed in the SQL/function, and SSRS would simple pass the concatenated string.