Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Custom control and setup of SSRS report parameters from a web page


By:   |   Last Updated: 2009-05-14   |   Comments (5)   |   Related Tips: > Reporting Services Parameters

Problem
I have a ASP.NET web application that I would like to present SQL Server Reporting Services reports in. I understand that reports can be rendered using the ReportViewer control, but the interface for parameters is a little bland and doesn't match our site colors. Is there a way that report parameters can be passed to the Report Services instance without having to use the Reporting Services parameter prompts?

Solution
Luckily, there is a fairly straightforward way of passing report parameters to the Reporting Services instance. Let's say we have a web site that tracks the hourly weather observations of a number of airports in the area. We can offer a report using Reporting Services that will allow the users to select a site, a start date, and an end date for the observations they want to see. Once you open Visual Studio 2005 and the page you want to add the report to, you'll see a ReportViewer control in the Data tab of the toolbox. If you don't see the Report Viewer control, it's because the control has a separate installation package from Visual Studio 2005 that you can download here:

Location of the ReportViewer control in Visual Studio 2005

Once you have installed the ReportViewer control (or see it in the Data tab of the toolbox), you can start building the page. For this example I create a simple page with three text boxes, two image buttons to control the calendars and a link button that will pass the parameters and render the report:

Web page used to collect parameters

In addition to the controls above, I dragged a ReportViewer control onto the page while in Design mode. The benefit to dragging it over in Design mode is that the reference to the Microsoft.Reporting.WebForms namespace is automatically added to the page. Once the control is on the page, the rest of the work is done using either Visual Basic or C# (this example will use Visual Basic). One setting you can add to the ReportViewer control in Design or HTML mode is the Processing Mode. The ReportViewer control can render reports from a local source, called a Client Report Definition File, or RDLC. They can also render reports from a remote source, like a SQL Server Reporting Services instance. In this case, we have a Reporting Services instance available to us, so we will use the remote processing mode:

HTML code where the processing mode is indicated

Now that we have the ReportViewer control in the page and the general layout taken care of, we can write the code that fires when the View report link button is clicked. First we declare a generic list of report parameters. This involves both the System.Collections.Generic namespace as well as the Microsoft.Reporting.WebForms namespace. On the SQL Server Reporting Services report there are three parameters: SiteID, StartDate, and EndDate. You'll also notice in the code below that we use the actual report server URL (like "http://www.mssqltips.com/reportserver"), not the Report Manager URL ("http://www.mssqltips.com/reports"). We then add a report parameter for each one to the generic list of parameters and specify the report path. The ServerReport.SetParameters function is used to apply the report parameters specified in the generic list to the actual report parameters on the Reporting Services report:

Code for Passing Report Parameters to the Reporting Services Instance
Protected Sub cmdGetReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdGetReport.Click 
Dim colRP As New System.Collections.Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter) 
colRP.Add(New Microsoft.Reporting.WebForms.ReportParameter("SiteID", UCase(txtSiteID.Text.Trim))) 
If IsDate(txtStartDate.Text.Trim) Then 
      Dim dteStart As Date = txtStartDate.Text.Trim 
      colRP.Add(New Microsoft.Reporting.WebForms.ReportParameter("StartDate", Right("0" & dteStart.Month, 2) & "/" & _ 
     Right("0" & dteStart.Day, 2) & "/" & dteStart.Year)) 
End If 
If IsDate(txtEndDate.Text.Trim) Then 
     Dim dteEnd As Date = txtEndDate.Text.Trim 
     colRP.Add(New Microsoft.Reporting.WebForms.ReportParameter("EndDate", Right("0" & dteEnd.Month, 2) & "/" & _ 
     Right("0" & dteEnd.Day, 2) & "/" & dteEnd.Year)) 
End If 
rvWeather.ServerReport.ReportServerUrl = New Uri("http://www.cullensolutions.com/ReportServer$SQL2K5") 
     rvWeather.ServerReport.ReportPath = "/Weather/Site Report" 
     rvWeather.ServerReport.SetParameters(colRP) 
     rvWeather.ServerReport.Refresh() 
     rvWeather.Visible = True 
End Sub 

When the user reaches the page, all they have to do is enter the parameters they want and click the View Report button:

Specifying the criteria in the web application

The report generated after applying the report parameters

Next Steps



Last Updated: 2009-05-14


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 03, 2014 - 7:06:52 AM - Ramu Back To Top

Hi,

 We are having few issues with SSRS reports when we click on any report it prompts to add "Compatibility Mode" should be enable and it is working fine. however i do not want to use "Compatibility Mode" for IE 9 so are there any parameters can be set in browser or ssrs report viewer URL as my clients users should not use compatibility mode and all the reports should work.

 

Thanks in advance.

Regards,

Ramu 


Friday, November 15, 2013 - 5:52:39 PM - daguyse Back To Top

zaboomafoo this is a great way to do this


Thursday, October 14, 2010 - 12:16:26 PM - Tim Cullen Back To Top
In the situation I based the tip on there were a number of multi-valued parameters and I used AJAX (http://www.asp.net/ajax) to handle the callbacks for the ones that were dependent on the selected value.  You can concatenate all of the selected values, pass it as one of the report and stored procedure parameters, and have SQL parse the string (see this tip for how to do that: http://www.mssqltips.com/tip.asp?tip=1665).  That would avoid having to use dynamic SQL in the stored procedure.  

If you're going to use AJAX keep in mind that the Report Viewer control will have to reside outside of the Update panel that the parameter fields are located, since at the time the tip was published the Report Viewer control did not work properly inside an Update Panel.  Please post if you news on your next attempt-good or bad news!


Thursday, October 14, 2010 - 9:09:42 AM - Tatyana Back To Top
I am totally with Oded Dror (who posted a question above).  The method described in the article could lead a way to a solution to a problem of a page being refreshed on each parameter selection in the report. When there is a number of multivalue parameters in a report, and each of them is depeneding on a previous one, the page refreshement becomes very irritating and time consuming issue. It seems that moving parameters from report into the application could do a trick. It's just I don't know how to deal with multivalue parameters in the asp.net application :) I'll try to find it out, but if anyone knows the answer please share it.

Thanks, -

Tatyana


Tuesday, August 18, 2009 - 8:02:16 AM - odeddror Back To Top

Hi there,

My question is lets assume that the siteid is like that

Select siteid from my table where siteid in(@siteid) - multi value

(in reporting services you will get a drop down list with multi select option)

How do you set your asp.net  dropdown list control to pick more than one siteid and pass these into  sql server?

one value it easy the problems begin when it multi value (I did not see any example of multi values with dropdown list control)

Thanks,

Oded Dror

 


Learn more about SQL Server tools