Script to split multi-value parameters

Add this script to your database to split up multi-value parameters for SSRS reports.

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[udf_SSRS_SplitMultiValueParams]') 
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]
go
CREATE FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
        DECLARE @tempItemList NVARCHAR(4000)
        SET @tempItemList = @ItemList

        DECLARE @i INT
        DECLARE @Item NVARCHAR(4000)

        SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
        SET @i = CHARINDEX(@delimiter, @tempItemList)

        WHILE (LEN(@tempItemList) > 0)
        BEGIN
        IF @i = 0
        SET @Item = @tempItemList
        ELSE
        SET @Item = LEFT(@tempItemList, @i - 1)

        INSERT INTO @IDTable(Item) VALUES(@Item)

        IF @i = 0
        SET @tempItemList = ''
        ELSE
        SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

        SET @i = CHARINDEX(@delimiter, @tempItemList)
        END
        RETURN
END

/* 
 * Usage:
 * where metric1 in (select item from dbo.udf_SSRS_SplitMultiValueParams(@metric1, ','))
 */