Pass parameters and options with a URL in SQL Server Reporting Services

By:   |   Updated: 2007-09-24   |   Comments (25)   |   Related: > Reporting Services Parameters


Problem
Reporting Services has many great built in features, but accessing them externally can be tricky. Suppose you wanted to pass a variable directly within a URL? Wouldn't it be great if you could provide a URL from a website or email that renders a report directly in Excel? Do you need to display a report (perhaps a chart) while hiding the reporting toolbar?  Let's take a look at these items as well as a few others and how you can use parameters in your URL to change the report behavior.
Solution

Follow these steps to discover various solutions to these and other issues.

Solution Setup

For this demo we will be using the same sample database I used in my tip on drill down techniques,  (Follow the instructions in the "Sample Data" box.)

Create a new report using the VISIBILITY_EXAMPLE_TABLE as a datasource. Use the following SQL statement as the report's first dataset:

SELECT ProductCode, ProductName, ProductColor, QuantityAvailable, SupplierName, SupplierRegion
FROM VISIBILITY_EXAMPLE_TABLE
query data from table

From the Layout tab, add the six fields to a new data table.

layout tab

Add a second dataset to the report and call it AvailableColors.

available colors

Enter this for the SQL statement of the new dataset:

SELECT DISTINCT ProductColor
FROM VISIBILITY_EXAMPLE_TABLE
ORDER BY ProductColor

Return to the first dataset and add the following line:

WHERE ProductColor=@ProdColor

The entered dataset should look like:

SELECT ProductCode, ProductName, ProductColor, QuantityAvailable, SupplierName, SupplierRegion
FROM VISIBILITY_EXAMPLE_TABLE
WHERE ProductColor=@ProdColor

From the Report menu, select Report Parameters. In Available values, select From query, and change the Dataset to AvailableColors, and select ProductColor in both the Value and Label fieldsLeave the Default to Null. The entire Report Parameter screen should like this below:

report parameters

Now save and publish the report to your Reporting Services web server. Access your report from the web server using the Report Server (not the Report Manager).

Your browser screen should look like this before clicking the report. (The name of my server is Zorro. Henceforth, replace "Zorro" with the machine name of your SQL server.) The name of my report is called "zPass_Params_with_HTML". You can name your report whatever you like.

reporting services screen

When you first run the report, you should be prompted for a parameter:

report viewer

Just to try it out, select a parameter and run the report.

As long as everything works we're ready to have some fun....

Passing the parameter entirely through HTML

Start over with your browser at the ProdColor prompt. Instead of choosing the color from the dropdown, add the following at the end of the URL:

&ProdColor=Green.

It should look like this:

report viewer

DO NOT click the View Report button, but instead, click the go button in the browser. You should get the same results as if you used the dropdown within the report. This should give you the idea that you can programmatically pass parameters from another program or web site.

Controlling Report Options

So you want to hide the parameter bar by default, try adding this to the end of the URL:

&rc:Parameters=false

The full URL should look like this:

http://zorro/ReportServer/?%2fEdgewoodLab%2fzPass_Params_with_HTML&rs:Command=Render&ProdColor=Green&rc:Parameters=false

Hiding the Toolbar

Add this to the end of the URL:

&rc:Toolbar=false

Controlling Report Format

(Opening Excel or Acrobat by default)

If you haven't already done it, let's roll back to the original URL.

http://zorro/ReportServer/?%2fEdgewoodLab%2fzPass_Params_with_HTML&rs:Command=Render

(Remember to replace "Zorro" with the name of your server and use whatever you named your report.)

Now add the following on the end of the URL:

&rs:Format=Excel

Try this at the end:

&rs:Format=PDF

As you can see, there are many different options for presenting your report directly from a URL. This introduction should give you a foundation to read further on this topic. There is extensive documentation in Books Online.

Next Steps
  • Try combining the &rc:Toolbar=false tip with a series of charts that you can put together in a dashboard on a web site. This can be done from a series of iframes on a standard website or from Sharepoint in Page Viewer web parts.
  • Check Books Online or Microsoft for more detailed documentation on passing parameters in a URL and other report options.
  • Take a look at these other Reporting Services tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-09-24

Comments For This Article




Wednesday, February 26, 2020 - 12:42:42 PM - Gilmer Pajarez Back To Top (84814)

I have a report that open a subreport with 2 parameters: prmSERVPRV and prmSPEC2. If I use only one parameter it works fine. But when I try to pass the values of 2 parameters it fails. Below is the URL that I am trying to execute. Any help will be greatly appreciated.

TESTING: OPENNING IN THE SAME WINDOW  PASSING 2 PARAMETERS

“http://test-ssrs.humana.com:8081/ReportServer/Pages/ReportViewer.aspx?%2f12730%2fSFL%2fTestSSRS%2fDataview+Report+101+To+SSRS+-+Specialist+Claims+Subreport_TESTING&rs:Command=Render&prmServprv="& Fields!SERVPRV.Value & "&prmSpec2="& Fields!SPEC2.Value


Monday, November 4, 2019 - 6:26:13 PM - Ilana Ochman Back To Top (82971)

Please help!

I need to pass function to linked report as parameters

example 

="javascript:void(window.open('http://reports.companyname.local/ReportServer?/Procurement/MyReport&rs:Command=render&rc:Parameters=true&monthlist="  & [my function] & "','_blank'))"

[my function] CODE=SPLIT(JOIN(Parameters!DimCalendarMonth.Value,","),",")

What is correct format to incorporate this fuction my report?

Thank you for your help


Wednesday, September 4, 2019 - 4:39:32 PM - Lydia Back To Top (82246)

Hi,

Thanks for this post. Please i want to know what to do if i want the parameter to give more colors such as Green, Red, Yellow? How do i include more colors in the url? Thank you


Wednesday, May 22, 2019 - 9:51:48 AM - tony king Back To Top (80156)

Hi, I found this helpful but cannot get my URL and parameter to work I get the error

"The path of the item '/GEMINI REPORTING/BY DEPARTMENT/PP/Manual Production_order_tracker_V31&rs:Command=render&SALES_ORDER=2900252422' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath) Get Online Help "

My jump tp code is :

http://SERVER/Reports/report/GEMINI%20REPORTING/OPERATIONS/PROJECT%20VEHICLE%20DASHBOARD&RS:@PROJECT=Parameters!PROJECT.Value.

If I run it without the param :

http://server/Reports/report/GEMINI%20REPORTING/OPERATIONS/PROJECT%20VEHICLE%20DASHBOARD

I do get taken to my report correctly then have to enter the param manually.

TK


Friday, October 3, 2014 - 11:26:07 AM - Noel Back To Top (34831)

Hey Rob. Thanks for this great piece of insight.  Just one query. If I have several values for one parameter to pass in, do I represent that as comma separated list? Consider if my report had a date parameter, which is set to accept multiple values, this allowing a user to select several dates. Let's say I want to pass in 2 or more dates via the url to that one parameter, can I write the following>

 &date=2014-04-30,2014-04-31 


Friday, July 25, 2014 - 10:36:40 AM - Ravin Back To Top (32883)

Hi,

How can record the report URL with server name and URL on go??

I tried by this inbuilt features in ssrs ([&ReportServerUrl][&ReportFolder]/[&ReportName]). which records the path but not the parameters and if I use that output link in URL it goes to report server home page only not to the report.

Can anyone help me in this please??

Thanks

Ravin

 


Monday, April 7, 2014 - 11:42:48 AM - Nikhila Back To Top (29992)

Hi

I have a client requirement where 6 paramaters are declared in a report. And these parameters are explicitly entered by user who accesses the report. After entering parameters user clicks on "View Report".

Requirement is "View Report" should directly export the report to PDF. Instead of showing it in default viewer.

Some one please help.

It is very critical and urgent requirement which I should complete.

Thanks in Advance!!


Wednesday, October 23, 2013 - 4:06:44 PM - prasannalakshmi Back To Top (27245)

Hi

I am have a issue reg the SSRS 2005 site. I am new to this feature. The site hosted in the server is SSL enabled and all the feature are also seems to work fine. But in the top right corner we have a HOME option when I click it the site is being redirected in "HTTP" and I am getting the error message to change it to "HTTPS". Help me to resolve the same.


Tuesday, September 17, 2013 - 10:10:21 PM - Prabhakar Boddapati Back To Top (26836)

Hi,

 

I am also facing same issue what venkat was facing. I could able to pass parameters in URL for AX 2009 report but its not reflecting. Manually need to update the parameters. 

Pls do share the information Venkat if you are done  with it.


Friday, August 16, 2013 - 8:19:37 AM - venkat Back To Top (26348)

Hi,

 

I having done SSRS report in .moxl format for ax2009 application. in url i am able to pass the parametars of month and year,  but does't reflect in report parameters values. report is opening and manually again need to give the month & year parametars values.

kindly give me the solution.

 

details:

http://mysys:81/Reports/Pages/Report.aspx?ItemPath=%2fDynamics%2fSubreports_Mysys.YDR.PrecisionDesign1&month=8&year-2013



Thursday, July 25, 2013 - 7:11:11 AM - Rob Fisch Back To Top (25997)

Yes that seems like a problem. Here's a workaround....you could create a calculated column in the dataset that converts the time to text, using the REPLACE function you can substitute another character like a dash (-) for the colon, and using this new data field to send with the URL parameters.


Thursday, July 25, 2013 - 2:26:42 AM - pavan Back To Top (25992)

HI,

we have a small problem while sending url

we need to indicate time in the url. But while sending time 04:00, colon is reserved in url 

thus how to send time as parameter

thanks!!

 


Friday, April 5, 2013 - 7:42:24 PM - Saul Back To Top (23200)

Hi!! I know this a very old post, but I need help!! I'm traying to use RS in an ASP aplication. I undarstand all you say here but, i don't know how to acces de Server (the report server) using credentials. I mean this server is not in my localhost and obviously, I can't let the user know this credentials.


I know this is the double hop problem but i don't know how to solve it in ASP (I am a .NET programmer). 


Thnks!!!


Wednesday, February 13, 2013 - 12:31:30 PM - Lawrence Back To Top (22103)

Hi. I setup SSRS 2005 and it's running beautifully. However, I want to change the authentication type to custom since my environment is somewhat unusual. My app uses Windows Authentication via a user account on my web server coupled with Anonymous Access. This can't change because users are being authenticated in different ways depending how they arrive at the site. I'm considering creating a web page from which to redirect authenticated users to a link to my reports and programmatically passing NetworkCredentials to the report server. Would you possibly have an example of something similar? All suggestions are welcome . Thanks in advance.


Monday, February 11, 2013 - 6:02:22 PM - quasar_phoenicis Back To Top (22050)

Hi,

I have the same thing as well, pretty simple report with 4 parameters. I used the link above and filled in the value for this parameters and it works fine in dev. However, when the report is moved to prod, the last parameter value is not being passed (blank). The only difference between dev and prod is that in dev, the access is windows authentication already and in prod, user must enter credentials.

any help please?


Thursday, October 25, 2012 - 8:13:09 AM - abhijeet Back To Top (20085)

Thanks it worked for me....

Really helps


Wednesday, August 8, 2012 - 5:27:41 PM - umut Back To Top (18966)

Thanks for your helps.But i wanna ask something if u still alive in this subject :)...i did my report successfully.But when i open my report in android or iphone aplication wiith url mode i didnt see my report on full screen.Please help me for this..how ican i resolve this...


Monday, May 21, 2012 - 8:16:59 AM - Andrew Back To Top (17567)

Okay, now I am getting:

 

  • An error has occurred during report processing. (rsProcessingAborted)
    • Query execution failed for data set 'spZZR_FullPayDetailsReport'. (rsErrorExecutingCommand)
      • Conversion failed when converting the nvarchar value 'Name' to data type int.

I sent:

http://localhost/ReportServer/Pages/ReportViewer.aspx?/praxima/rptZZ-FullPayDetails&rc:Parameters=False&rs:Command=Render&ClientName=ZZZZ&CostCentFrom=&CostCentTo=ZZZZZZZZ&EmpNameFrom=&EmpNameTo=ZZZZZZZZ&EmpNumFrom=&EmpNumTo=ZZZZZZZZ&EndDateString=31-May-2012&EntireYear=No&GiveBlankOutput=Yes&Level2From=&Level2To=ZZZZZZZZ&Level3From=&Level3To=ZZZZZZZZ&Level4From=&Level4To=ZZZZZZZZ&Level5From=&Level5To=ZZZZZZZZ&OutputSeq=Name&ResourceTagString=Name&StartDateString=01-May-2012&StartYearString=01-Mar-2012&sUserId=andrewm 

 

and the 'nvarchar' it is referring to is the VALUE contained in a dropdown list that populates OutputSeq=Name !!!

 

Any ideas. It seems to be inconsistent, these errors.

 

Mac


Monday, May 21, 2012 - 8:10:32 AM - Andrew Back To Top (17566)

Hi I am passing the following string (URL) of parameters:

http://localhost/ReportServer/Pages/ReportViewer.aspx?/praxima/rptZZ-FullPayDetails&rc:Parameters=True&rs:Command=Render&ClientName=ZZZZ&CostCentFrom=&CostCentTo=ZZZZZZZZ&EmpNameFrom=&EmpNameTo=ZZZZZZZZ&EmpNumFrom=&EmpNumTo=ZZZZZZZZ&EndDateString=31-May-2012&EntireYear=&GiveBlankOutput=&Level2From=&Level2To=ZZZZZZZZ&Level3From=&Level3To=ZZZZZZZZ&Level4From=&Level4To=ZZZZZZZZ&Level5From=&Level5To=ZZZZZZZZ&OutputSeq=Cost%20Centre/Name&ResourceTagString=Cost%20Centre/Name&StartDateString=01-May2012&StartYearString=2011&sUserId=andrewm 

But it dowesnt seem to get all the values across. i.e - if I enable the Parameters (=TRUE) I can see the values have not been carried through. This is USUALLY when I am passing to a multiselect parameters (i.e. dropdown) type (in the report.

Any ideas?

 

Thanks

 

MAC

 


Monday, March 19, 2012 - 7:44:46 PM - Sri Back To Top (16518)

Hi

Found out the issue, I was using Report Manager instead of ReportServer.

Thanks,

Sri.


Monday, March 19, 2012 - 7:23:48 PM - Sri Back To Top (16517)

Hi,

I am still not able to get succeed, I spent already 3 hours. Is there anything I am missing to set any of the properties.

I am using VisualStiod 2008 and SQL R2.

 

Thanks,

Sri.

 


Wednesday, March 14, 2012 - 5:54:31 AM - Ron Prowse Back To Top (16376)

Eliana, try html encoding the string


Tuesday, March 6, 2012 - 7:08:12 PM - Eliana Back To Top (16271)

 

hi Thanks for the post, It was very helpfully for me, but what happen when one of my parameter have and & sign?

I have the parameter

CompanyName="H&P company"

How can I write my URL to represent the & sign

Thanks a lots

Eliana


Monday, October 24, 2011 - 6:21:21 AM - Noor Back To Top (14898)

Hi ,

 

Thanks for the post.

I have one question here.

have a report and am passing parameter values in URL and able to get the result successfully in development environement but in production am not.

This is because on the Prod environment when we go into any report it asks for user credentials (At this step the link parameters get lost).

Any inputs?
Thanks in advance.

 

 


Thursday, July 3, 2008 - 7:13:59 PM - satshivam Back To Top (1363)

Hi This article is good and going to help me but please let me know how to pass paarameter by Querystring.... if I am using Stored procedure















get free sql tips
agree to terms