Solved

Multi-Value Parameter selections with SQL Reporting Services

Posted on 2009-03-31
4
576 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:AxleWack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 25 total points
ID: 24028772
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
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 25 total points
ID: 24044488
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24044514
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
 
LVL 3

Author Comment

by:AxleWack
ID: 24057302
Got it sorted thanks.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question