Multivalues Paramater Using RS2003

• Hendra Eka Putra

  SSCrazy

  Points: 2015

  More actions

  October 4, 2005 at 5:03 am

  #166224

  Any idea how to use a multivalued parameter using RS2003. Do not suggest me to use RS2005

   

• Eddie Wuerch

  SSChampion

  Points: 12533

  More actions

  October 5, 2005 at 1:10 pm

  #595247

  It's not something built in to the current Reporting Services.  It's still possible, though.  In order to use a multivalued parameter, you have to do some extra work, and it's basically a kludge.

  There's no way to set an RS drop-down parameter list to accept multiple values, so you'll need to start by rolling your own method of collecting the multiple values, such as building your own input page that includes a multi-select drop-down, then including the results when you call the report either with the web service Render method or in a URL.

  Getting RS to do anything with the multivalued parameter then falls back to the usual tricks of passing multiple values as a single parameter.  This traditionally means passing a single varchar value this is a comma separated list, then doing something with the list on the procedure that gets the data for your report.

  For example, if you wanted to send a list of names to a procedure...

  CREATE PROC dbo.SalesRepInfo(
  @SalesRepList   varchar(500) = ''
  )
  <......>

  ...you would execute the call with the list as a single value (example: user chooses Bob, Dave, and Jen)

  EXEC dbo.SalesRepInfo 'Bob, Dave, Jan'

  Then you get to split the @SalesRepList up inside the proc and work with it there.  There's a couple ways of doing that, I prefer to use a table variable.  At the bottom of this reply, I copied the definition SplitList function from the sample AdventureWorksDW database.  There are similar scripts to be found by earching around SSC.

  Sample proc to return data to report:

  CREATE PROC dbo.SalesRepInfo(
  @SalesRepList   varchar(500) = ''
  )
  AS
  SET NOCOUNT ON
  SET ISOLATION LEVEL READ UNCOMMITTED

    SELECT SplitList.ListItem, sr.RepInfo
      FROM dbo.SalesRep sr INNER JOIN 
          dbo.SplitList(',', @SalesRepList) ON sr.RepName = SplitList.ListItem
  
    RETURN
  GO
  

  Good luck with that stuff,

  -Eddie

  -- SplitList function used in example above (taken from 
  --    the AdventureWorksDW sample database)
  CREATE FUNCTION dbo.SplitList(@separator char(1), @List varchar(8000))
   RETURNS @ReturnTable TABLE(ListItem varchar(1000))
  AS 
  BEGIN
   DECLARE @Index int
   DECLARE @NewText varchar(8000) 
   IF @List = null
    RETURN
   SET @Index = CHARINDEX(@separator, @List)
   WHILE NOT(@Index = 0)
   BEGIN
    SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
    SET @List = RIGHT(@List, LEN(@List) - @Index)
    INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
    SET @Index = CHARINDEX(@separator, @List)
       END
     
   INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
           
       RETURN
  END
  

  Eddie Wuerch
  MCM: SQL