Allow multiselect on a CSV column in SSRS

Question

I am trying to make a parameter multiselect on a CSV column in a report, but I am not sure how to do this or if it's even possible. Example dataset that is returned:

|  ID  |  Name  |      Types      |
|  1   |  Test  |  Type A, Type B |

The current dataset is using something like this in the WHERE clause for the parameter:

WHERE Types LIKE '%' + @Types + '%'

This works fine as a single select, but of course doesn't work for multiselect. How can I make it so the parameter will allow multiple values and still search this CSV column? I wish something like WHERE Types LIKE IN '%' + @Types + '%' would work, where it basically would end up as WHERE Types LIKE IN ('%Type A%, %Type B%'). Or is there a better way I can display the column so it appears CSV even if the SQL is not doing it? I would still want it to display all the values and only see if the selected type is in the list, so if the parameter is set to Type B it should still show Type A, Type B for ID = 1. I know CSVs are generally disliked in SQL, but it seems to be a frequent thing that's asked to make CSV columns and then allow multiselect on it.

possible duplicate of [SSRS multi-value parameter using a stored procedure](http://stackoverflow.com/questions/1256925/ssrs-multi-value-parameter-using-a-stored-procedure) — Tab Alleman, Apr 22 '15 at 18:10

score 0 · Answer 1 · answered Apr 23 '15 at 05:16

there would be 2 posibilities (1) your filter would have single value like 'Type A' (2) your filter would have multiple values like 'Type A, Type B' here i am giving solution for both 1 and 2. here i have used #table to make it understand easy

dummy data would be:

If Object_Id('tempdb.dbo.#temp') Is Not Null
Begin
    Drop Table #temp;
End

If Object_Id('tempdb.dbo.#temp') Is Null
Begin
    Create Table #temp
    (
         Id         Int Identity(1,1) Primary Key
        ,Name       Varchar(100)
        ,Types      Varchar(2000)
    )
End

Insert Into #temp(Name,Types) Values
('Test','Type A, Type B'),
('Test1','Type A, Type C'),
('Test2','Type B'),
('Test3','Type C, Type A'),
('Test4','Type C');

Declare  @TypeFilter        Varchar(2000)

posibility 1:

Select   @TypeFilter = 'Type C'

Select  *
From    #temp As t With (Nolock)
Where   (',' + t.Types + ',') Like ('%,' + @TypeFilter + ',%')

posibility 2:

Select   @TypeFilter = 'Type C, Type B'

Select  Distinct
        t.*
From    #temp As t With (Nolock)
        Join dbo.UDF_GetSplitValues(@TypeFilter,',') As p On (',' + t.Types + ',') Like ('%,' + p.stItems + ',%')

here for posibility 2, I have used table-value function which returns CSV values into table and applied join with primary table.

hope this would be helpful.

score 0 · Answer 2 · edited Mar 19 '19 at 07:05

0

You can try your where clause as

WHERE PATINDEX('%' + Types + '%',@Types)>0

edited Mar 19 '19 at 07:05

Hadi

36,233
13
65
124

answered Apr 23 '15 at 13:38

Aftab Ansari

926
9
17

Allow multiselect on a CSV column in SSRS

2 Answers2