SSRS - multiple values for TEXT data type parameter

Question

I am new to SSRS reports and have created a report based on a TEXT query:

select * from customers
where residence_state = :state -- oracle, or @state for SQL Server

This generated the parameters and I am able to give a value, such as 'CA' in the 'Report Parameter Properties - Available Values' Window. This works well, however, suppose that I want the :state(or @state) parameter to include 'CA' + 'AZ' + 'WA'. What would be the easiest way to accomplish this?

Answer 1

for MSSQL, change your query to

select * from customers
where residence_state in (@state)

This will make the parameter multivalued. And also, check the parameter properties in SSRS. Make sure

Allow multiple values

is checked.

Answer 2

For multi select parameters SSRS passes a comma delimited list of values. The code below is from a report where the parameter @program is a comma delimited set of uniqueidentifiers.

--I create a table variable

    declare @programs table
    (
        program_id uniqueidentifier
    )

    declare @myid uniqueidentifier

-- Then I parse the parameter values (which was declare as @program varchar(max) -- as you can see I know the length of each parameter. -- If you did use charindex to find the location of the next value while len(@program) > 0 begin

        set @myid = convert(uniqueidentifier, left(@program, 36))
        set @program = substring(@program, 38, len(@program))
        --print @program

        insert @programs values(@myid)
    End

I hope this helps.