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

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.

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:

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:

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 “https://www.mssqltips.com/reportserver”), not the Report Manager URL (“https://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:

Next Steps

• Review information on the System.Collections.Generic offered in the Microsoft .NET Framework 2.0
• Learn about Using the WebForms ReportViewer Control on Microsoft’s Developer Network
• Read up on creating report parameters and setting report parameter properties
• Take a look at more Reporting Services tips

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)