Passing parameter via url to sql server reporting service

AwkwardCoder picture AwkwardCoder · Jul 3, 2009 · Viewed 152.5k times · Source

I'm trying to pass a parameter via the url to SSRS and it appears not to work!

I'm trying to pass a userId (string) via the url which will be passed to the database and used by the query.

base url: http://blah/Reports/Pages/Report.aspx?ItemPath=MyReport

tried this but it doesn't work: http://blah/Reports/Pages/Report.aspx?ItemPath=MyReport&UserId=fred

Any ideas

Answer

snyderj picture snyderj · Jun 6, 2013

I had the same question and more, and though this thread is old, it is still a good one, so in summary for SSRS 2008R2 I found...

Situations

  1. You want to use a value from a URL to look up data
  2. You want to display a parameter from a URL in a report
  3. You want to pass a parameter from one report to another report

Actions

If applicable, be sure to replace Reports/Pages/Report.aspx?ItemPath= with ReportServer?. In other words: Instead of this:

http://server/Reports/Pages/Report.aspx?ItemPath=/ReportFolder/ReportSubfolder/ReportName

Use this syntax:

http://server/ReportServer?/ReportFolder/ReportSubfolder/ReportName

Add parameter(s) to the report and set as hidden (or visible if user action allowed, though keep in mind that while the report parameter will change, the URL will not change based on an updated entry).

Attach parameters to URL with &ParameterName=Value

Parameters can be referenced or displayed in report using @ParameterName, whether they're set in the report or in the URL

To hide the toolbar where parameters are displayed, add &rc:Toolbar=false to the URL (reference)

Putting that all together, you can run a URL with embedded values, or call this as an action from one report and read by another report:

http://server.domain.com/ReportServer?/ReportFolder1/ReportSubfolder1/ReportName&UserID=ABC123&rc:Toolbar=false

In report dataset properties query: SELECT stuff FROM view WHERE User = @UserID

In report, set expression value to [UserID] (or =Fields!UserID.Value)

Keep in mind that if a report has multiple parameters, you might need to include all parameters in the URL, even if blank, depending on how your dataset query is written.

To pass a parameter using Action = Go to URL, set expression to:

="http://server.domain.com/ReportServer?/ReportFolder1/ReportSubfolder1/ReportName&UserID="
 &Fields!UserID.Value 
 &"&rc:Toolbar=false"
 &"&rs:ClearSession=True"

Be sure to have a space after an expression if followed by & (a line break is isn't enough). No space is required before an expression. This method can pass a parameter but does not hide it as it is visible in the URL.

If you don't include &rs:ClearSession=True then the report won't refresh until browser session cache is cleared.

To pass a parameter using Action = Go to report:

  • Specify the report
  • Add parameter(s) to run the report
  • Add parameter(s) you wish to pass (the parameters need to be defined in the destination report, so to my knowledge you can't use URL-specific commands such as rc:toolbar using this method); however, I suppose it would be possible to read or set the Prompt User checkbox, as seen in reporting sever parameters, through custom code in the report.)

For reference, / = %2f