CODEWITHSUNDEEP
reporting-servicesssrs-2008
Newbie
Newbie

Reputation: 1714

SSRS multi-value parameter with JOIN() function not working

I have been following this website for implementing an optional multi-value parameter. The weird thing is it only works in following conditions:

  1. "All" (the one with value -1) is selected. Or
  2. Only 1 value is selected.

If I select more than 1 values, it will always come out with no result. I have also tried to print out the value of =IIF(Parameters!Employee_ID.Value(0)=-1,Nothing,Join(Parameters!Employee_ID.Value,",")), the output is correct. When -1 is selected, it's value is nothing, when other results are selected, it will display every value splitted with ','. Any idea? My parameter value is integer.

UDPATE
I found that when I select more than 1 value it will come out no result is because it will always take the 1st value only. For example, if I have selected 4 values which are 4,5,7,9 it will treat it as WHERE id IN (4), instead of WHERE id IN (4,5,7,9). Why?

Upvotes: 0

Views: 2072

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

If your query is directly in the dataset then you don;t really need to use Join or Split or anything else.

Try the following.

Change you parameter back to the default of text (I know it's a number but that's OK).

Then simply change your query to something like

SELECT * 
    FROM MyTable
    WHERE (EmployeeID IN(@Employee_ID) OR @Employee_ID = -1)

SSRS will take you dataset query and inject the parameters for you as comma separated values automatically. There is no need to do anything with the parameter input.

Upvotes: 1

Related Questions