Using Multi-Select Parameters
-
Hi,
What is the simple way to make use of multi-select parameters? I just added one and tried to make use of it but couldn't get it to work.
The only solution I could gind via google was one where the multi-parameter string is passed into a SP and then dynamically constructed into an SQL string which was then executed.
I am hoping that there is a simple way to use this parameter in a query?
Cheers,
Dale
-
You can use it in combination with an IN statement. I see it frequently like this:
wHERE a.mycolumn IN ( @MultiSelect )
-
Oh, thats what I originally assumed but I had some kind of datatype mismatch error - OK - I'll play with that some more.
-
Hey Dale ,
Is this multi-selects in Reporting services ?
Will
-
If you are using the multiple selections in an mdx query then use the STRTOSET function. If you are using in a sql query and the value field of the multi-select parameter is a string then use the IN clause like select * from employees where name IN (@multiparam). If you are using in a sql query and the value of the multi-select parameter is an id value like an int then you will have to use a function to split the string values up into a table and convert to an int. Reporting services returns the multi-selected id values as a concatenated string value so you cannot use the IN clause like you can with strings since you are comparing integers to strings. To use a multi-select param when the selected values are not strings use this: select * from employees where id IN (select cast(Value as int) from dbo.Split(@multiparam, ',')). Or try casting the id to a string before comparing: select * from employees where cast(id as varchar(10)) IN (@multiparam). Below is an example Split function. Hope this helps.
---------------------------------
create function [dbo].[Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
 
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
set @Delimiter = LTrim(RTrim(@Delimiter))
--Initialize
set @NextString = ''
set @CommaCheck = right(@String,len(@Delimiter))
--Check for trailing delimiter, if not exists, INSERT
if (@CommaCheck <> @Delimiter )
begin
set @String = @String + @Delimiter
end
--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +len(@Delimiter),len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
return
end
-
Hi
I'm not using mdx but I'm using a stored procedure.
I have a report where they can choose different departments or the select all.
How do I get this info in my stored procedure ?
Thx in advance
El Jefe
JV -
Just pass the concatenated string into the stored proc and then use the methods in my previous post. When you select multiple parameters in reporting services the parameter value contains a string with all the selected values concatenated and delimited by a comma. Just pass that parameter value into the stored proc as you would any other parameter. Just use a varchar(4000) or text datatype as the stored procedure parameter and then use the methods described in my previous post to compare against that sql proc parameter.
-Alex
-
Maybe I don't completely understand the problem, but...
Create Procedure FillReport
(
@Dept varchar(20) = null
)
AS
BEGIN
Select [whatever] from [someobject] where Department = COALESCE(@Dept, Department)
END
GO
Pass a null value to the sproc when the user selects all depts?
-
When you select All departments as the Department parameter in reporting services report you will wind up with a string containing all the departments delimited by commas like "Accounting, Management, Development, Testing, Human Resources, Customer Service". Now if the report parameter for department has the value field set to an id, instead of the label/name field as before, then you will wind up with a string like "2, 1, 3, 4, 5, 6". So, first off the stored proc parameter needs to be alot bigger in size since you don't know how big the string will be (since its multiple strings concatenated). Then just do comparison against the string using the IN clause as follows:
Create Procedure FillReport
(
@Dept_Names varchar(4000)
)
AS
BEGIN
Select [whatever] from [someobject] where Department_Name IN (@Dept_Names )
END
GO
Or if using IDs then:
Create Procedure FillReport
(
@Dept_IDs varchar(4000)
)
AS
BEGIN
Select [whatever] from [someobject] where cast(Department_ID as varchar(10)) IN @Dept_IDs)
END
GO
Or use the split function for IDs as in previous post:
Create Procedure FillReport
(
@Dept_IDs varchar(4000)
)
AS
BEGIN
Select [whatever] from [someobject] where Department_ID IN (select cast(Value as int) from dbo.Split(@Dept_IDs,','))
END
GO
If using IDs play around and see which method works best for you... one may be faster than the other (probably the first method is faster).
-
I agree that if you pass a comma-delimited string to the sproc that the coalesce method I posted will not work. But, if the possible values are a single dept or all depts then pass the dept name or id if a single dept if desired and pass null if all depts are desired. Coalesce will select the first non null value in it's parameter list. This method will cause the select statement to be evaluated as if it were typed explicity as:
Single Dept:
Select [whatever] from [someobject] where Department = 'Department'
All Depts:
Select [whatever] from [someobject] where Department = [Department]
In the All Depts case, [Department] is equal to the value of the current rows Department field, causing the statement to always be true.
Am I still missing the problem?
-
In order to use multiple value parameter in Reporting services and pass it to the stored procedure you just need to do the following and all will work perfactly. For example if you have Department as multi value parameter then
@Department =join(Parameters!Department .Value,",")
and pass it to the stored procedure and you can use split string function to split this string. i hope this will help and i m using it in my SSRS reports .if you have any question pls ask
many thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply