Tuesday, November 4, 2008

SQL Server Reporting Services, and ASP.NET

This post I would like to talk about delivering Microsoft SQL Reporting Services via an ASP.NET application.

Reporting Services includes an ASP.NET web control. I've found this control to be useful when I want to make the most of Reporting Services' ease of report definition (.rdl files), but don't like the way Reporting Services' renders their parameter options. Using ASP.NET, I suddenly have available to me, all the flexibility of ASP.NET and it's web form controls.

For example, you ou may want to control how those form controls are loaded in your web form - e.g. by writing some code in the On_Load event, you could pre-fills the text value of a TextBox, or pre-ticks some tickboxes in a CheckBoxList. This way, default values can be defined which are commonly used by business users.

As well as the report parameters, another key area of interest is security. In general terms, the SQL Server from which you're sourcing your report data will be configured one of two ways: a) all data is accessed via a single, generic, application account, or b) specific user accounts are created for each individual user (hopefully using their windows logon account).

A third key area is printing. More often than not, business users want to print their reports. In practice with the ASP.NET Reporting web control, this aspect is, oddly enough, intrinsictly linked to the security of your reports (see paragraph above). This is due to the architecture Microsoft have used for report printing, which will be discussed later.

OK, enough with the introductions, let's look at some code.

First, some quick background info:
  • Development environment is Microsoft Visual Studio 2008
  • Working with a project that is a plain ASP.NET web site
  • The targetted framework is .NET 2.0
  • The website can be deployed on the same server on which Reporting Services runs, but this is not a requirement
You need to add the following ReportViewer components as references to the Visual Studio project:
  • Microsoft.ReportViewer.Common
  • Microsoft.ReportViewer.Web
These two components can be found in the Microsoft Report Viewer Redistributable 2005.

In your .aspx page, register the WebForms assembly, with a line like the following:
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

Note that the TagPrefix property will of course refer to the prefix you will use to create the Reporting Services web control in the .aspx source.

Next, we need to add the web control to the .aspx source:
<rsweb:ReportViewer ID="rptOverallTotals" runat="server" height="100%" ProcessingMode="Remote" Visible="false" AsyncRendering="true" ZoomMode="PageWidth" ShowPrintButton="false" ShowDocumentMapButton="false">
<ServerReport
ReportServerUrl="http://localhost/ReportServer"
ReportPath="/MyReportExample/FirstReport" DisplayName="My First Report" />
</rsweb:ReportViewer>

These two things alone are all you need to display a report in .aspx page, assuming the report doesn't require any parameters to be filled in. You will notice some attributes are specified in the tag. More on those later, however it's important that the ProcessingMode attribute is set to Remote in order for this example to work.

It's not often a report is needed that doesn't need any parameters specified. In order to get those parameters parsed in to the ReportViewer control, you need to specify them during the page's On_Load event. You will need to do the following to the page's code-behind file:

Add a reference to the Microsoft.Reporting.WebForms library
using Microsoft.Reporting.WebForms;

In the Page_Load function, create an array of ReportParameter variables:
ReportParameter[] theseParameters = new ReportParameter[1];

Note the number of items in this ReportParameter array should reflect the number of parameters your report contains.

Let's assume the parameter you want to parse in is a string, and its name is "DisplayText". Specify the first parameter array's item as a ReportParameter indicating this:
theseParameters[0] = new ReportParameter("DisplayText", "Hello World");

And finally, parse the ReportParameter array into the web control's ServerReport object:
rptOverallTotals.ServerReport.SetParameters(theseParameters);

Hopefully this is enough information to set you on your way. In my next post, I'll talk about the different attributes of the ReportViewer web control, including Visible, ProcessingMode, AsyncRendering, ZoomMode, ShowDocumentMapButton, and ShowPrintButton. I will also talk about security, and how to:
  • Parse the credentials of the user authenticating to your site through to Reporting Services, or
  • Take on the credentials of a generic application user

No comments:

Post a Comment