sql server reporting service multi select parameter

解决reporting service 多选列表参数的办法有两种：

 1. 给tablix添加Filter。

 2. 使用存储过程parse多值参数。

具体操作如下。

I agree, its a little messy to use multi-parameters.  There are two primary methods I use to handle this:  Filters and SQL.  The Filter method is easier.

Filter Method:

1. Create a multi-value parameter.  Let's call it @Animals and you select Dog, Cat, Bird (or Select All)
2. Your main Dataset, which will return a result set to your report, does not reference @Animals in it's where clause.
3. Instead, click on Dataset Properties and select Filters
4. In the Filter setup dialog, click on the column in your result set that corresponds to the Animal value.
5. For the operator, select the "In" operator
6. For the value, type in [@Animals]
7. Done!

This will "post-filter" your SQL query and only return the values that have been filtered by your multi-value parameter.  Only Dog, Cat, Bird records will return to your report.  The downside to this approach is that the processing occurs at the Report Server level and not by your Database server, because you are not using SQL to do the work.  In many cases (most cases!) I find this the easiest and quickest way to do what you want.

SQL Method:

1. Create a multi-value parameter.  Let's call it @Animals and you select Dog, Cat, Bird (or Select All).  Same as the Filter Method!
2. You will need a stored procedure for your main report Result Dataset.  Let's call it sp_get_animals.
3. sp_get_animals will take one argument, @Animals, so the calling mechanism looks like this:  exec sp_get_animals @Animals
4. When you are configuring your parameters in the Query dialog, use the following expression to define the value of your parameter:  =join(Parameters!Animals.Value,",")
5. This will create a string that looks like this:  "Bird,Dog,Cat"
6. In the body of your stored procedure, you will have to parse @Animals to pick off Bird, Dog, Cat.
7. Once you have parsed @Animals, you can use the SQL IN clause to actually process it.

This method is definitely more complicated, but it has the advantage of passing the parameters directly to SQL and allows you to take advantage of your Database Server.

When in doubt, use the Filter method if you can get away with it.  It has the advantage of simpler SQL, and more intuitive to other members of your team.

There are many examples of the SQL method on this website, and a quick Google search will also reveal examples of using SQL to do this.  But like I said, it can be messy.

 

附上Parse多值参数的function

CREATE function [dbo].[SplitString]
(
    @Input nvarchar(max), --input string to be separated
    @Separator nvarchar(max)=',', --a string that delimit the substrings in the input string
    @RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string
)
returns @TABLE table 
(
    [Id] int identity(1,1),
    [Value] nvarchar(max)
) 
as
begin 
    declare @Index int, @Entry nvarchar(max)
    set @Index = charindex(@Separator,@Input)

    while (@Index>0)
    begin
        set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
        
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
                insert into @TABLE([Value]) Values(@Entry)
            end

        set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
        set @Index = charindex(@Separator, @Input)
    end
    
    set @Entry=ltrim(rtrim(@Input))
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
        begin
            insert into @TABLE([Value]) Values(@Entry)
        end

    return
end