Passing a multi-select input parameter as a dataset parameter with IN condition - SSRS

Question

I have a report with an input parameter P_Region that allows the user to select 1-* options. I then need to pass the results of this parameter to a DataSet query. It works if i only select one option but not if i select two.

What i have:

The DataSet parameter for it is set to:
=join(Parameters!P_Region.Value, ",")

The query is:
WHERE ... AND D.City IN(@P_Region)

I have trieda few different things with no luck as well:
=Split(Parameters!Item.Value, ",")
=Split(Join([email protected],","),",")
WHERE D.Region IN (SELECT * FROM dbo.split(@Department)) with no luck.

Any help is much appreciated!

Solution: (Sorry I feel like this is a pretty easy solution but I did not see it anywhere online and I'm super new with SSRS)

Right click on the dataset
Go to the Parameters tab and set the query parameter back to the default value (No Joins or Splits)
Go to the Filters tab and add the expression of the field you want to be in the parameter, choose the operator "In" and set the value as [@<'Parameter Name'>']

Note: Understand that the other records will still be returned by the query but they will just be filtered. If you are returning large sets of data and using very little of it, this may not be the best route to go.

Ross Bush Ross Bush · Accepted Answer · 2014-07-15T13:42:37

I think you where on the right track with WHERE D.Region IN (SELECT * FROM dbo.split(@Department)) with no luck.

I use a tabled valued function to create a filter table and parse the values there.

WHERE D.Region IN (SELECT ID FROM dbo.CreateIntIDTable(@Department)) 


ALTER FUNCTION [dbo].[CreateIntIDTable](@IDList NVARCHAR(3000))
RETURNS @T TABLE (ID INT)
AS BEGIN
    WHILE(CHARINDEX(',',@IDList)>0)BEGIN
        INSERT INTO @T
        SELECT LTRIM(RTRIM(SUBSTRING(@IDList,1,CHARINDEX(',',@IDList)-1)))
        SET @IDList = SUBSTRING(@IDList,CHARINDEX(',',@IDList)+LEN(','),LEN(@IDList)) 
    END
    INSERT INTO @T SELECT  LTRIM(RTRIM(@IDList))
    RETURN
END

Passing a multi-select input parameter as a dataset parameter with IN condition - SSRS

3 Answers