SSRS R2- compare single values in multi value parameters

Question

I've got a multi value parameter that pulls available country codes to filter my table on.

if I make it single value, no problem - @country default value to "US", and there are about 15 other countries to choose from - whichever one you pick, only the results that contain that country code pop up. However, I want the user to be able to select multiple countries to display simultaneously. the filter at the moment is

Expression:[Country] text
Operator: = 
Value: @Country

I thought the join() function would work here, and it might yet, but that seems to be useful only for displaying the selection - if I use it, join(@Country,",") for instance, and the user has selected "US","HK" it is going to compare the results to make sure that the query contains both of those, not one or the other. thoughts?

do you have a stored procedure or adhoc sql for your dataset ??? — M.Ali, Dec 27 '13 at 21:25
SSRS doesn't give you that kind of control as far as I can tell — nbpeth, Dec 27 '13 at 21:26
In simple words do you want your users to select multiple countries and see results for all them selected countries ??? — M.Ali, Dec 27 '13 at 21:42

score 2 · Accepted Answer · answered Dec 27 '13 at 21:56

if you want your users to select Multiple countries and see results for selected multiple countries you will need to do two things

1) Create a Report parameter which allows "Multiple Values"

2) Modify your query to accept that multi value parameter something like this

say in your case you countries parameter is @Country which is a multiple value parameter your query will be something like as follows

SELECT * 
FROM Table_Name 
WHERE (Country IN (@Country))   --<-- the key thing to notice is the extra set of parenthesis 
      |                     |       -- around the column Name as well
      |                     |
      \                     /
       \                   /
          these extra two parenthesis does the trick when we have a multiple value
          parameter.

this was the right idea, however not how I implemented it - I just changed my operator to IN rather than = on the report side — nbpeth, Dec 27 '13 at 22:39

SSRS R2- compare single values in multi value parameters

1 Answers1