It has been found that in SSRS 2005-2012, when we use direct query as data set we are able to apply multi-value parameter and the report works when we select multiple values in parameter.
E.g., Let the direct query be as follows:
SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country in (@Country)
When we use the above query in dataset a parameter Country will be created in Parameter list, and we can configure parameter as shown below, the report work well when we select multiple values in parameter.
When the same query is executed as stored procedure, the report will fail when we select multiple values in parameter. To overcome this issue we need to create the given function in our database:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MultiParameter]
(
@DelimittedString [varchar](max),
@Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
DECLARE @sTemp [varchar](max)
SET @sTemp = ISNULL(@DelimittedString,'')
+ @Delimiter
WHILE LEN(@sTemp) > 0
BEGIN
INSERT INTO @Table
SELECT SubString(@sTemp,1,
CharIndex(@Delimiter,@sTemp)-1)
SET @sTemp = RIGHT(@sTemp,
LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
END
RETURN
END
GO
And we need to modify the stored procedure as like below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: BabukannanM
-- Create date: September 19, 2012
-- Description: To list of Customer by Countrywise
-- =============================================
CREATE PROCEDURE [dbo].[Rpt_Customer]
@Country NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country IN (Select * from [dbo].[MultiParameter] (@Country, ',') )
END
GO
Now configure the report parameter to select multi-values.
