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

0
votes

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?

1
reporting-servicesssrs-2008
Are you using a Stored Procedure or a query directly in your dataset? – Alan Schofield
Try Parameters!Employee_ID.Value.Length > 0 – Lucky
@AlanSchofield dataset – Newbie
@Lucky I think this isn't the problem. The problem in on the Join() function – Newbie

1 Answers

1
votes

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.