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.

Aircrew

Answer 1

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

Celestinecelestite

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

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? – Paramedical

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

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

@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. – Celestinecelestite

Ah... gotcha... sorry I misunderstood the question. – Amyotonia

Answer 2

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.

Adversity

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

Answer 3

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

Colo