0

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

Will Kim
  • 163
  • 1
  • 9
  • 1
    This seems to be a duplicate of http://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 Feb 11 '14 at 07:14
  • possible duplicate of [How to use multivalue parameters in SSRS](http://stackoverflow.com/questions/15235518/how-to-use-multivalue-parameters-in-ssrs) – mmarie Feb 11 '14 at 07:14
  • 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 Feb 11 '14 at 21:51
  • where do you have a question mark? – mmarie Feb 11 '14 at 22:33
  • http://social.technet.microsoft.com/Forums/sharepoint/en-US/fd1d4ea1-abdd-47b0-a97b-0446501f460f/how-to-use-multi-value-paramter-in-ssrs-report-for-sharepoint-2010?forum=sharepointdevelopmentprevious – mmarie Feb 11 '14 at 22:34
  • In the where clause of my dataset1 query. mbility is of a varchar type. – Will Kim Feb 11 '14 at 22:38
  • Thank you so much for suggesting using filters instead. I followed what the guy said in the link and it is working now. I think another issue was having that TOP 2000, which was restriction a lot of data which I should've taken out.(a bit slow tho, is there any faster alternative? I would imagaine if I filter my query by my parameter, this reporting would be quite fast to load on the portal). Nevertheless, thank you so so much! – Will Kim Feb 12 '14 at 00:08

1 Answers1

0

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.

Community
  • 1
  • 1
Pops
  • 468
  • 2
  • 15