SSRS multi-select (easy way!)

 

Open Visual Studio Business Intellegence Studio

Open an existing project or create a new project.

 

Select the Data Tab
create new DataSet

 

call this dataset Select:

Here is my select statement.
select * from (
select 'basket ball' as ball
union select 'baseball'
union select 'foot ball'
union select 'nerf ball'
) t where t.ball in(@ball)

Call this ddl_select (this will be the drop-down menu you need for the multi-select)

 

select 'basket ball'
union select 'baseball'
union select 'foot ball'
union select 'nerf ball'

if you have a debug table you want to test your values (see how they are written, here’s some code:

 

if object_id('admin_ssrs_log') is null 
  begin
    CREATE TABLE [dbo].[admin_ssrs_log] ([ssrsID] [int] IDENTITY(1, 1)
                                                        NOT NULL,
                                         [PageName] [varchar](255) NULL,
                                         [Username] [nvarchar](520) NOT NULL,
                                         [runDate] [datetime] NOT NULL
                                                              CONSTRAINT [DF_ssrs_log_runDate] DEFAULT (getdate()),
                                         [string] [varchar](max) NULL
                                                                 CONSTRAINT [DF_ssrs_log_string] DEFAULT ('none'),
                                         [Stored Procedure] [nvarchar](255) NULL) 
  end
insert  dbo.admin_ssrs_log (PageName,
                            Username,
                            runDate,
                            string,
                            [Stored Procedure])
values  (
         'test multi-select parameters', -- PageName - varchar(255)
         'domain\username', -- Username - nvarchar(520)
         getdate(), -- runDate - datetime
         @ball, -- string - varchar(max)
         '(code behind execution; no stored proc)'  -- Stored Procedure - nvarchar(255)

 

Now, create the multi-select drop down for “ball”

 

here you can select the options (but you aren’t done yet! the clincher is next):

 

The multi-select parameter is written but SQL Server needs it parsed in a certain way.

(oh my, this is soooo easy!)

 

1.  Go to your data tabl

2.  Edit the dataset that needs the @ball parameter.

 

3.  Select the Parameters tab

 

The key is to use the Replace function and the Join function

 

Chagne your value

from =Parameters!ball.Value

to     =Replace(Join(Parameters!ball.Value,", ")," ", "")

 

voila.  your multi-select will work every time!

 

 

helpful source (kinda obscure, but here it is)