How to implement multiselect with new columns in SSRS

Question

I need to allow multi select on a column where the values are all CSV. I had been using a LIKE statement and a single select parameter but I need the parameter to allow multiselect. An idea I had was to create columns for each of the parameter values (there are only 6) and then use a CASE statement to set them to True/False and then I was thinking if Type A is selected, then it will look at ColumnA and if they also select Type B then it will also look at ColumnB.

The entire query is very large, but here's the excerpt I'm working on

CASE WHEN T1.Types LIKE '%Type A%' THEN 1 ELSE 0 END AS TypeA

This is in a CTE, in my final select statement it references TypeA only, etc. The final query should be something like this

SELECT ID, Types, TypeA, TypeB, TypeC
FROM TableA
WHERE Types IN [parameter]

Here's some sample data for the Types column.

|________Types________|
|        TypeA        |
|     TypeA, TypeC    |
| TypeA, TypeB, TypeC |

How can I get this to work without making this too complicated? I know this could easily work if I wanted 6 different parameters for each of the Types and then just had a True/False for each one, but how can I just make it a multiselect with a single parameter?

Answer 1

If you only have 6 types, you could do something like this in your final select:

SELECT DISTINCT TableA.*
FROM TableA
JOIN
    (
    Select '%Type A%' TypeCode where @TypeCSVParameter like '%Type A%'
    union all
    select '%Type B%' TypeCode where @TypeCSVParameter like '%Type B%'
    union all
    select '%Type C%' TypeCode where @TypeCSVParameter like '%Type C%'
    union all
    select '%Type D%' TypeCode where @TypeCSVParameter like '%Type D%'
    union all
    select '%Type E%' TypeCode where @TypeCSVParameter like '%Type E%'
    union all
    select '%Type F%' TypeCode where @TypeCSVParameter like '%Type F%'
    ) ValidTypes
on TableA.Types like TypeCode

Another way you could do it instead of this hard coded list is to use a function to parse your CSV list as suggested in this question SSRS multi-value parameter using a stored procedure

I'm in agreement with Tab Allerman's comment though, you really should restructure the database. Having a column with a list makes things difficult. You should probably have a TypeCode table with all valid types, and a relationship table to link your Table1.ID to applicable TypeCode. This way you can avoid hard coding special cases like this and can more easily add or change types.