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?
