Passing values for multi-value parameter in SSRS query string

Question

I have two reports built using SSRS 2005. The first report is set to navigate to the second when a specific field is clicked. I am using an expression similar to the following in the "Jump to URL" property of the textbox:

="javascript:void(window.open('http://server/reportserver?%2fFolder%2fMyReport&rs:Command=Render&Date=" & Fields!Date.Value & "&MachineId=" & Fields!Machine.Value & "'))"

There is a multi-value parameter on the second report. I need to pass multiple values for this parameter in the URL query string when calling this report. Is there a way to pass multiple values for a parameter in the query string of a report? Or can you pass a parameter that will cause the Select All value to be selected?

Thanks.

Mitch · Accepted Answer · 2016-10-25 17:41:59Z

31

Just add additional query string parameters.

For example, to pass the parameters

Date:       2009-06-01
MachineID:  Machine1, Machine2, Machine3, Machine4

to a report named Folder\MyReport on a server named server, you would use the URL:

http://server/reportserver?%2fFolder%2fMyReport&rs:Command=Render&Date=2009-06-01&MachineId=Machine1&MachineId=Machine2&MachineId=Machine3&MachineId=Machine4

edited Oct 25, 2016 at 17:41

answered Aug 11, 2009 at 16:25

Mitch

22.6k8 gold badges70 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shawn Over a year ago

Working Perfect, I was stuck with this one long time ago. thanks

MarceloBarbosa Over a year ago

This is not working for me...

System.ServiceModel.FaultException: This report requires a default or user-defined value for the report parameter 'codListaPrioridade'. To run or subscribe to this report, you must provide a parameter value.

Any advice?

Mitch Over a year ago

@MarceloBarbosa, it sounds like you are not passing codListaPrioridade. Your query should look like [...]&codListaPrioridade=value1&codListaPrioridade=value2.

Brian Pressler Over a year ago

This link is invalid now. Can you explain your answer in text format?

Mitch Over a year ago

@BrianPressler, the link was never valid. It is the format of the URL you should use if you are passing multiple values to a paramter. Replace http://server/reportserver/ with your server, and the report name and parameters with your report's name and parameters.

|

Jeff Wu · Accepted Answer · 2010-12-10 20:18:01Z

7

Use join(Parameters!<name>.Value,"&<param_name>=") in the url for multivalued parameters.

If you are passing these parameters into a dataset you need to do a join(Parameters!<param name>.Value) when you pass the parameter in and then use a split function in SQL. This one works well:

ALTER FUNCTION [dbo].[fnSplitParam]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece nvarchar(100)
  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 VARCHAR))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END

I recommend using the single valued method if the end user does not have to select the parameters directly since it saves you 2 characters per parameter in the url.

answered Dec 10, 2010 at 20:18

Jeff Wu

2,5881 gold badge23 silver badges25 bronze badges

1 Comment

Sev09 Over a year ago

Thanks, Jeff!! This is exactly what I needed - couldn't find this anywhere else on the web.

nopepad · Accepted Answer · 2016-07-29 00:45:26Z

0

I've been trying to do a similar thing to OP, and found putting this &rp:mySelectAllParameter=<ALL> in the url works to select all

answered Jul 29, 2016 at 0:45

nopepad

445 bronze badges

Collectives™ on Stack Overflow

Passing values for multi-value parameter in SSRS query string

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related