Query to add Multi List Parameter in SSRS 2005 Reporting Server

 Step 1: Add a Table-valued function 'charlist_to_table' in your db.

 Check for function charlist_to_table or create one using below Stored Proc:

USE [Timesheet]
GO
/****** Object:  UserDefinedFunction [dbo].[charlist_to_table]    Script Date: 11/24/2010 18:47:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[charlist_to_table]
    (@list ntext,
    @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

            str varchar(4000),

            nstr nvarchar(2000)) AS

            BEGIN

            DECLARE @pos int,

            @textpos int,

            @chunklen smallint,

            @tmpstr nvarchar(4000),

            @leftover nvarchar(4000),

            @tmpval nvarchar(4000)

            SET @textpos = 1

            SET @leftover = ''

            WHILE @textpos <= datalength(@list) / 2

            BEGIN

            SET @chunklen = 4000 - datalength(@leftover) / 2

            SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

            SET @textpos = @textpos + @chunklen

            SET @pos = charindex(@delimiter, @tmpstr)

            WHILE @pos > 0

            BEGIN

            SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

            SET @pos = charindex(@delimiter, @tmpstr)

            END

            SET @leftover = @tmpstr

            END

            INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),

            ltrim(rtrim(@leftover)))

RETURN

END

Step 2: Call that function in your Stored Proc as shown in sample procedure below:

USE [Timesheet]
GO
/****** Object:  StoredProcedure [dbo].[sp_CalculateProjectRevenue]    Script Date: 11/24/2010 18:28:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:       
-- Create date:
-- Description:   
-- =============================================
ALTER PROCEDURE [dbo].[sp_CalculateProjectRevenue]
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL,
    @ProjectID VARCHAR(2000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements. --165118
    SET NOCOUNT ON;
BEGIN

  SELECT    Main.TrackID
            ,Main.ProjectName
            ,Main.ProjectID
            ,Main.EstimationNo
            ,Main.EstRevenue
            ,Main.TotalHours
            ,Projects.IsBillable AS ProjBill
            ,Main.RegionID
            ,Main.Firstname
            ,Main.ResourceID
            ,CONVERT(VARCHAR(10),Main.Datee,101) AS DATED
            --,Main.IsBillable
            ,Tasks.TaskName
            ,Timetrack.Description
            ,CASE Main.IsBillable WHEN 1 THEN Main.Duration ELSE 0 END AS BillHrs
            ,CASE Main.IsBillable WHEN 0 THEN Main.Duration ELSE 0 END AS NonBillHrs
            ,ISNULL(Main.HourlyRate,0) AS HourlyRate
            ,ISNULL(Main.OffshoreRate,0) AS OffshoreRate
            ,ISNULL(Main.OnsiteRate,0) AS OnsiteRate
           
FROM Main
WHERE Main.Datee >= @FromDate AND Main.Datee <= @ToDate
        AND Main.ProjectID  IN (SELECT CAST([str]AS INT) FROM charlist_to_table(@ProjectID,','))

END
SET NOCOUNT OFF;
END







Regards

Mahadevan