locked
Multi-value parameter is not working in SSRS RRS feed

  • Question

  • I am having a problem with the multi-value parameter in SSRS 2005.  When I render my report in BIDS with the multi-value parameters defined, and I select multiple values then no results are returned, but if I select a single value then the correct results are returned.  I am using a sproc to pass the parameters, but it is not working.  Here is an example of my code:

    USE [dbSample]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROC [dbo].[spSampleSProc]
    @start datetime,
    @end datetime,
    @scorecardccprod varchar(100),
    @scoreregion varchar(100),
    @servicefamilyname varchar(150),
    @subsidiaryname varchar(150)
    AS

    SELECT T.c1, T.c3, T.c2, T.c4, T.fiscalmonth, T.Countryname, T.Scoreregion, T.scorecardccprod, T.ServiceFamilyname, p.SurveyMonth
    FROM RSReport.dbo.tbl1 AS T
    INNER JOIN tbl2 AS p ON T.fiscalmonth = p.MonthMap
    LEFT OUTER JOIN dbSample2.dbo.tbl3 AS e ON T.Countryname = e.CountryName
    WHERE
    (
    (CAST(CAST(p.CaseCloseFiscalYear AS char(4)) + '/' + CAST(p.CaseCloseFiscalMonth AS char(2)) + '/01' AS datetime) >= @start)
    AND (CAST(CAST(p.CaseCloseFiscalYear AS char(4)) + '/' + CAST(p.CaseCloseFiscalMonth AS char(2)) + '/01' AS datetime) <= @end)
    AND (T.Scoreregion IS NOT NULL) AND (T.scorecardccprod IS NOT NULL)
    AND (e.AreaName IN (@scoreregion))
    AND (e.SubsidiaryName IN (@subsidiaryname))
    AND (T.scorecardccprod IN (@scorecardccprod))
    AND (T.ServiceFamilyname IN (@servicefamilyname))
    )

    If I hard code values into the sproc and I execute it from SSMS then the results are returned corrected whether it's a single or multiple value.  So why is it working in SSMS and not in SSRS?  Any ideas?

    Thursday, June 5, 2008 11:09 PM

Answers