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?

If you want to make this work without getting complicated, normalize your data. — Tab Alleman, Jun 10 '15 at 18:05

score 0 · Answer 1 · edited May 23 '17 at 12:14

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.

How to implement multiselect with new columns in SSRS

1 Answers1