Solved

Multi-Value Parameter selections with SQL Reporting Services

Posted on 2009-03-31
4
560 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
  • 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 26

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 26

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

746 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now