SSRS Multi-Select

,

Regarding:

Multi-Select Parameters for Reporting Services
Regular Columnist : Adriaan Davel
Posted: 08/13/2007 

Our solution:
Create a table function to parse the values into a table.

Usage:
In a stored procedure called by SSRS, pass the multi-select values into this function to return a table of key/value pairs.

ie:
select * from fnCSV_Parse (',' , 'ca,de,ny,wa')

yeilds:
tblID       Keys
-----------------
1           ca
2           de
3           ny
4           wa

This way you can have a query that simply joins to the function.
Select s.*
from state_codes as s
    Inner Join dbo.fnCSV_Parse (',' , 'ca,de,ny,wa') as b
    on s.State_Code = b.Keys

--or to get an indexed join

declare @s_codes table
    (State_Code char(6) primary key
     )

insert @s_codes(State_Code)
select Keys from fnCSV_Parse (',' , 'ca,de,ny,wa')

Select s.*
from state_codes as s
    Inner Join @s_codes as b
    on s.State_Code = b.State_Code


Performance has been great against multi million row tables.

CREATE function dbo.fnCSV_Parse (@delimiter char(1), @csv nText)
returns @tbl table (tblID int identity(1,1) primary key, keys varchar(255))
as
/*
Created by Chuck Carmody with nText conversion by Lane Sandness
Circa 2007
*/

BEGIN
declare @len int
set @len=datalength(@csv)   if not @len>0 return


declare @l int
declare @m int

set @l =0
set @m =0

declare @s varchar(255)
declare @slen int

while @l<=@len begin

set @l=@m+1--current position
set @m=charindex(@delimiter,substring(@csv,@l + 1,255))--next delimiter or 0

if @m <> 0
	set @m = @m + @l 
	--insert @tbl(keys) values(@m)

select @slen= case 
	when @m=0 then 255 --returns the remainder of the string
	else @m-@l end --returns number of characters up to next delimiter


if @slen>0 begin
	set @s=substring(@csv, @l, @slen)
	insert into @tbl (keys) select @s

	end

select @l= case
	when @m=0 then @len+1 --breaks the loop
	  else @m+1 end --sets current position to 1 after next delimiter
end

return
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON

Rate

Share

Share

Rate