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)
