Passing a parameter via URL to SQL Server Reporting Services
Asked Answered
D

9

48

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

base URL:

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

I tried this, but it doesn't work:

http://blah/Reports/Pages/Report.aspx?ItemPath=MyReport&UserId=fred

Any ideas?

Disremember answered 3/7, 2009 at 11:4 Comment(1)
The problem was Iw as trying to access the report via a 'static' url, this urlonly shows a report already generated, it doesn't do 'dynamic' report generation - if you want this you have to use a url something like 'http://<server>/ReportServer/Pages/ReportViewer.aspx...' As stated below.Disremember
S
49

As well as what Shiraz said, try something like this:

http://<server>/ReportServer/Pages/ReportViewer.aspx?%2f<path>%2f<ReportName>&rs:Command=Render&UserID='fred'

Note the path would only work if you are in a single folder. When I have to do this I simply browse to the report using the reportserver path ("reports" is the report manager) and copy the url then add &<ParameterName>=<value> to the end.

Searby answered 14/7, 2009 at 23:51 Comment(3)
What if I want to pass a dynamic parameter through something like @ModelFantasist
@selectDistinct: Long time ago, I know. But I just stumbled over this post and want to add that in order to pass a dynamic param, you simply just add another URL param: eg. "&Model=<value>". Do not forget the & in front. Final thing: IF you want to trigger a "render" of the report immediately, you add the "&rs:command=Render" option. Here, DO make sure that all custom parameters have a default value set. Otherwise the Render command is ignored. (I struggled with this, b/c I wanted to integrate a report in a .NET app, where the entire URL is built in code, for direct display of the report).Aquatic
I figured it out... make sure strings are not enclosed, ie: instead of &ProjectName='Unknown' it should be &ProjectName=UnknownAvocado
M
62

First, 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

Parameters can be referenced or displayed in a report using @ParameterName, whether they're set in the report or in the URL. You can attach parameters to the URL with &ParameterName=Value.

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

Putting that 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 the report's dataset properties query: SELECT stuff FROM view WHERE User = @UserID

In the report, set the 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. That means repeating the parameter name for multiple values of the same parameter, too.

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, see this page on URL encoding, e.g. / = %2f

Maltese answered 6/6, 2013 at 17:42 Comment(0)
S
49

As well as what Shiraz said, try something like this:

http://<server>/ReportServer/Pages/ReportViewer.aspx?%2f<path>%2f<ReportName>&rs:Command=Render&UserID='fred'

Note the path would only work if you are in a single folder. When I have to do this I simply browse to the report using the reportserver path ("reports" is the report manager) and copy the url then add &<ParameterName>=<value> to the end.

Searby answered 14/7, 2009 at 23:51 Comment(3)
What if I want to pass a dynamic parameter through something like @ModelFantasist
@selectDistinct: Long time ago, I know. But I just stumbled over this post and want to add that in order to pass a dynamic param, you simply just add another URL param: eg. "&Model=<value>". Do not forget the & in front. Final thing: IF you want to trigger a "render" of the report immediately, you add the "&rs:command=Render" option. Here, DO make sure that all custom parameters have a default value set. Otherwise the Render command is ignored. (I struggled with this, b/c I wanted to integrate a report in a .NET app, where the entire URL is built in code, for direct display of the report).Aquatic
I figured it out... make sure strings are not enclosed, ie: instead of &ProjectName='Unknown' it should be &ProjectName=UnknownAvocado
N
8

Try changing "Reports" to "ReportServer" in your url

Novanovaculite answered 3/7, 2009 at 14:47 Comment(0)
C
8

According to Microsoft, the format basically is:

http://<server>/reportserver?/<path>/<report>&rs:Command=Render&<parameter>=<value>
Clarisclarisa answered 31/8, 2009 at 14:25 Comment(0)
I
4

Try passing multiple values via url:

/ReportServer?%2fService+Specific+Reports%2fFilings%2fDrillDown%2f&StartDate=01/01/2010&EndDate=01/05/2010&statuses=1&statuses=2&rs%3AFormat=PDF

This should work.

Insecure answered 9/5, 2011 at 10:44 Comment(0)
C
1

I solved a similar problem by passing the value of the available parameter in the URL instead of the label of the parameter.

For instance, I have a report with a parameter named viewName and the predefined Available Values for the parameter are: (labels/values) orders/sub_orders, orderDetail/sub_orderDetail, product/sub_product.

To call this report with a URL to render automatically for parameter=product, you must specify the value not the label.
This would be wrong: http://server/reportserver?/Data+Dictionary/DetailedInfo&viewName=product&rs:Command=Render

This is correct: http://server/reportserver?/Data+Dictionary/DetailedInfo&viewName=sub_product&rs:Command=Render

Cimah answered 10/12, 2015 at 16:11 Comment(0)
K
0

Use the URL, mentioned below to open the report in a new window with dynamic parameters.

="javascript:void(window.open('http://Servername/ReportServer?/Foldername/Reportname&rs:Commnd=Render&parameter1=" & Fields!A.Value & "&parameter2=" & Fields!B.Value & "'))"
Kiva answered 12/2, 2022 at 1:33 Comment(0)
C
-1

Try changing "Reports" to "ReportServer" in your url. For that just access this http://host/ReportServer/ and from there you can go to the report pages. There append your parmaters like this &<parameter>=<value>

For more detailed information:

http://dobrzanski.net/2008/08/11/reporting-services-problem-with-passing-parameters-directly-in-the-url/

https://www.mssqltips.com/sqlservertip/1336/pass-parameters-and-options-with-a-url-in-sql-reporting-services/

Calciferol answered 11/4, 2017 at 15:6 Comment(1)
This just repeats Shiraz' answer from 8 years prior.Odawa
S
-2

http://desktop-qr277sp/Reports01/report/Reports/reportName?Log%In%Name=serverUsername&paramName=value

Pass parameter to the report with server authentication

Schell answered 16/10, 2020 at 7:31 Comment(1)
your answer is of very low quality,Cespitose

© 2022 - 2025 — McMap. All rights reserved.