none
Filter not working for report on SSRS 2005

    Question

  • My filter does not work in a SSRS report for two the two paramters.

        @BarcodeList varchar(max) = null,

        @HospitalList varchar(max) = null

    I have delcared them as strings, mutivalue and the dropdown list gets populated through a stored procedure.

    In SQL Works fine.

    Stored Proc used.

    ALTER PROCEDURE [dbo].[GetRecipientInfo]

          @StartSurguryDate varchar(50) = null,

          @EndSurguryDate varchar(50)= null,

          @StartCreateDate varchar(50)= null,

        @EndCreateDate varchar(50)= null,

        @ProductList varchar(max) = null,

        @BarcodeList varchar(max) = null,

        @HospitalList varchar(max) = null

    AS

    BEGIN

          -- SET NOCOUNT ON added to prevent extra result sets

          SET NOCOUNT ON;

    SELECT      recip.PatientName AS 'Recipient Name',

                recip.PatientID AS 'Medical Record Number',

                recip.Hospital,

                recip.Sex AS 'Gender',

                recip.Age,

                CONVERT(VARCHAR(10), recip.SurgeryDate, 110) AS 'Surgery Date',

                surgeon.Surgeon_Name AS 'Surgeon Name',

            recip.Remarks AS 'Surgeon Name / Remarks',

                graft.TissueDepositNo AS 'Barcode',

                deposit.TissueTypeNo AS 'Product Code',

                deposit.[Description] AS 'Product Name',

                U.User_Name As 'Creater of Record login User Name',

            U.User_First_Name As 'Creator of Record First Name',

                U.User_Last_Name AS 'Creator of Record Last Name',

            convert(varchar(30),recip.Create_DateTime,101) as 'Create Date'

    FROM

                RECP_RECIPIENT_INFO_MASTER recip

                INNER JOIN RECP_RECIPIENT_INFO graft ON graft.RecipientId = recip.RecipientId

                LEFT OUTER JOIN PR_Deposit deposit ON deposit.TissueDepositNo = graft.TissueDepositNo

                LEFT OUTER JOIN SETUP_SURGEON surgeon ON surgeon.Surgeon_Id = recip.SurgeonName

                INNER JOIN Users  U on recip.BTM_User = u.User_name

    Where   (recip.SurgeryDate >= @StartSurguryDate or @StartSurguryDate is Null)

            and   (recip.SurgeryDate < @EndSurguryDate or @EndSurguryDate is Null)

            and   (recip.Create_DateTime >= @StartCreateDate or @StartCreateDate is Null)

            and   (recip.Create_DateTime < @EndCreateDate or @EndCreateDate is Null)

            and (ISNULL(@ProductList, 'ALL') = 'ALL' or CHARINDEX(',' + deposit.Description + ',', ',' + @ProductList + ',') > 0)

            and (ISNULL(@BarCodeList, 'ALL') = 'ALL' or CHARINDEX(',' + graft.TissueDepositNo + ',', ',' + @BarCodeList + ',') > 0)

            and (ISNULL(@HospitalList, 'ALL') = 'ALL' or CHARINDEX(',' + recip.Hospital + ',', ',' + @HospitalList + ',') > 0)

    Order By recip.SurgeryDate,recip.Create_DateTime 

    END

    Monday, June 24, 2013 4:00 PM

Answers