Multi-Value Parameter selections with SQL Reporting Services

Hi,
I have a list of items selected in SQL Reporting Service which I would like to be displayed. I am reading data from a Stored Procedure. When I select one value, the information is displayed, but when I select more than one, no data is displayed.... anyone know how to sort this out ?
My SP's parameter is as below. Ive tried using JOIN(Parameters!SP.values,",") in the Reporting Service, but this also does not work.
WHERE (dbo.ServiceProvider.ServiceProvider IN (@SP)
	AND (MSISDN_Master.Network_Sub > 0))

Open in new window

LVL 3
AxleWackAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Auric1983Connect With a Mentor Commented:
SSRS turns a multivalue parameter into an array... I don't believe the "Join" will work to pass as a parameter to the SP because it will just be one big comma seperated string. "value1,value2,value3" etc.  and what you really need is 'value1','value2','value3' etc. So if you use the Join function as describe to pass into the Stored Procedure, you  need a function to split that comma seperated list into individual values.

I found a blog post that may explain it a little more in detail http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/
0
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
We pass in the parameter string and use the table valued function below to split it into rows to join with.
/****** Object:  UserDefinedFunction [Tools].[fnMultiValue_Split]    Script Date: 04/01/2009 18:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [Tools].[fnMultiValue_Split]
   (@InputStr nvarchar(4000), @Delim char(1)= ',', @MVDelim char(1)= '|')
RETURNS @Values TABLE (Id INT IDENTITY(1,1), MainValue nvarchar(1000), SubValue nvarchar(1000))AS
  BEGIN
  DECLARE @chrind INT, @chrind2 INT
  DECLARE @Piece nvarchar(2000), @MainValue nvarchar(1000), @SubValue nvarchar(1000)
  SELECT @chrind = 1,  @chrind2 = 1
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@InputStr)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@InputStr,@chrind - 1)
      ELSE
        SELECT @Piece = @InputStr
      -- now split on sub value if there
      SELECT @chrind2 = CHARINDEX(@MVDelim,@Piece)
      IF @chrind2  > 0
        SELECT @MainValue = LEFT(@Piece,@chrind2 - 1), @SubValue = SUBSTRING(@Piece,@chrind2+1, LEN(@Piece))
      ELSE
        SELECT @MainValue = @Piece, @SubValue = NULL
      INSERT  @Values(MainValue, SubValue) VALUES(@MainValue, @SubValue)
      SELECT @InputStr = RIGHT(@InputStr,LEN(@InputStr) - @chrind)
      IF LEN(@InputStr) = 0 BREAK
    END
  RETURN
  END

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
This one is simpler as it has only one Delimiter.  Same logic applies as to how to use.
/****** Object:  UserDefinedFunction [Tools].[fnMultiValue_Param]    Script Date: 04/01/2009 18:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [Tools].[fnMultiValue_Param]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece nvarchar(10)
  SELECT @chrind = 1 
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(Param) VALUES(Cast(@Piece AS INT))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END

Open in new window

0
 
AxleWackAuthor Commented:
Got it sorted thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.