Using web services to generating reports
David is a consultant at Softagon Corp. He can be reached at [email protected].
As anyone who has developed a business-oriented ASP.NET solution knows, the need for snazzy-looking reports is both a recurring theme and (more often than not) a pain in the neck. Ignorant of layout requests, end users have a magical way of trying your data-sculpting abilities to the max. In the past, you would have had to purchase a reporting application that integrated (with varying degrees of success) with Visual Studio, and hope that the vendor's integration was worth the price of admission. Luckily, Microsoft heard our collective moans and responded with a free add-on to SQL Server that lets you develop reports similar to Microsoft Access and Crystal Reports for .NET. In this article, I examine the process of designing and deploying a report using SQL Server Reporting Services and show how to stream the report as a PDF document from a web service in ASP.NET applications.
Reporting Services is a free/evaluation add-on to SQL Server 2000 (http://www.microsoft.com/sql/reporting/) that does an excellent job of addressing most major concerns in terms of creating and delivering reports. Out of the box, Reporting Services offers features such as conditional formatting and suppression based on grouping "level" of the data, pivot table-like functionality, and the ability to export documents to e-mail, XML, and PDF. Reporting Services also lets you call your own .NET assemblies to retrieve data and perform other customizations.
XML Architecture
Microsoft implements Reporting Services through a series of .NET web services and a form of XML called the "Report Definition Language" (RDL). All report definitions and metadata are defined in an RDL document. When a report instance needs to be created (ad hoc or for a given schedule), the Reporting Services Server retrieves the RDL document, interprets the design elements, retrieves the data, and then renders the report in the requested format. Figure 1 illustrates this architecture. Though Reporting Services makes extensive use of XML, designing reports is straightforward and can be done without ever seeing the underlying RDL. How is this possible? It is possible because Reporting Services' inherent XML/.NET web-services architecture is particularly well-suited for integration into existing ASP.NET applications. Since the RDL is interpreted at runtime, reports do not have to be compiled either as part of your ASP.NET application or as a separate assembly. Modifying the layout or the data in a report becomes as simple as publishing a new version of the RDL document to the Reporting Services server. This feature alone would have saved me countless hours over the years trying to troubleshoot deployment-related issues, especially considering about one out of every three deployments seemed to be only for report-related issues.
Setting up Reporting Services
Although Reporting Services is designed for various multiserver configurations, the example I present here uses the same machine for the ASP.NET, Report Services, and database server. At a bare minimum, your computer needs:
- Windows 2003 Server or Windows XP Professional with SP1 (or later).
- SQL Server 2000 with SP3a (or later).
- Microsoft Visual Studio .NET 2003.
- Internet Explorer 6.0.
When you go through the default installation of Reporting Services, it creates the necessary databases in SQL Server and a web-based Report Server administration tool called "Report Manager." It then installs the project template type for Reporting Services reports in Visual Studio .NET 2003. My examples use the Northwind database that comes with the default installation of SQL Server 2000 (the complete source code for this example is available electronically; see "Resource Center," page 5). If you do not have (or have modified) the Northwind database, you can install or reinstall it as described in an MSDN article: http://msdn.microsoft.com/library/ default.asp?url=/library/en-us/tsqlref/ts_north_2ch1.asp.
Start by launching Visual Studio .NET 2003, creating a new blank solution, and adding a new project. Notice that in the New Project dialog box, you have a new Project Types folder called "Business Intelligence Projects," as in Figure 2. The first project type is the Report Project Wizard that steps you through the process of creating a simple report. I use the stored procedure Employee Sales By Country in the Northwind database for the example report. Select the Report Project project type to create a blank project.
Getting the Data Together
You will have two folders in the project—Shared Data Sources and Reports. Shared Data Sources are similar to ODBC files in that they let you select the OLE DB provider for the connection, specify the server and database, and the login authentication mechanism. Using Shared Data Sources, you can manage how reports connect to data sources independently of each report's design. Reports can also use more than one Shared Data Source, which is useful for cross-server reports and reports with subreports. For this example, I am only using the Northwind database on my local machine. To add a Shared Data Source, right-click on the Shared Data Sources folder and select Add New Data Source. In the Data Link Properties dialog box that appears, select your local machine for the server name and the Northwind database for the database on that server. Click OK to continue. Figure 3 shows the connection properties I used to configure the connection to that database.
On the Reports folder in your Report project, right-click and select Add New Item and select Report. Call the report SalesByYear. You are then presented with a design screen with three panels: Data, Layout, and Preview. The first task is to configure the report to use the Employee Sales By Country stored procedure.
Records from your database are bound to Reporting Services reports through Datasets. Datasets are results of a query or stored procedure against a Shared Data Source or a report-specific data source. Just as you can use more than one Shared Data Source in a report, you can use more than one Dataset. One important thing to note: Dataset objects (lowercase "s") are not the same as ADO.NET DataSet objects (uppercase "S"). ADO.NET DataSet objects cannot be used as a Dataset object, although I hope that Microsoft adds this feature in the future.
To set up the Dataset for this example, select <New Dataset...> in the drop-down box on the Data tab of the report. You get a dialog box like that in Figure 4. For this example, you want to name your Dataset SalesByYearResults, select the Northwind database, select Command Type StoredProcedure, and type Employee Sales By Country for the query string. Once you have entered this information, click on the Parameters tab.
Since this Dataset utilizes a stored procedure, you have to supply two parameters—@Beginning_Date and @Ending_Date. Just to set up the Dataset, type in the two parameters, set their values to =null, and click OK. When you close this dialog box and return the Data tab, notice that nothing really happened, which is not surprising considering that no dates were selected. To make this report useful, you need to add Report Parameters that can be fed to this Dataset.
To add Report Parameters, click the Reports item on the Standard Menu Bar and select Report Parameters. Click Add on the dialogue box that opens. Here, type Beginning_Date for the Name and the Prompt, select DateTime for the Data Type, and click OK. Repeat this process to add an Ending_Date parameter. Although this example uses Report Parameters with client-supplied values, you can create Report Parameters who derive their value from another query (implemented as another Dataset). This is a useful feature if your reports involve more than data source or reports that require a combination of stored procedures and custom SQL.
Once you have added both Report Parameters, edit the SalesByYearResults Dataset by clicking on the ellipses next to the Dataset name on the Data tab. Click on the Parameters tab. If you select the drop-down boxes for the parameter values, you notice the new report parameters. Select the appropriate Report Parameter for each and click OK. Now when you return to the Data tab of your report designer and hit the exclamation point (the Run query command), you are prompted for the Beginning and Ending dates. I used "05/19/1996" and "05/20/1998" for testing, but you can pick whatever date ranges you wish. If all is well, you see the results of the query on this tab.
A Simple Report Design to Get You Started
Once you have the data issues sorted out, you can start designing the report. Reporting Services has much to offer when it comes to customizing and formatting reports. Because this article focuses on integrating Reporting Services into existing ASP.NET applications (and not about how to create exceptionally mind-blowing reports), I will keep the example simple. (For information about creating those mind-blowing reports, refer to the samples provided with Reporting Services, assuming you chose to install them.)
Designing elements of the report is as easy as dragging elements from the Report Items tool bar and editing their properties. Almost all properties of the elements you can add to a report can be assigned through an expression. When you click <Expression...> in the drop-down list for various properties (Visibility, Value, BackgroundColor, and so on), you get a dialog box that lets you use a combination of Report Parameters, Dataset Fields, and report-specific information (like report execution time) to determine the value of a property. You can even use the IIF ("if and only if") operator for conditional assignment and aggregation functions such as SUM and COUNT.
To begin making the report, select the Layout tab of the report. On the Standard menu bar, click the Reports item and select Page Header. A Page Header section becomes visible on the report designer. Drag a TextBox item to the Page Header Section. Click on the TextBox and type in Employee Sales By Year. Now click on the Preview tab of the report. Fill in the two text boxes for beginning and ending date and click View Report; the report compiles and is previewed. You'll find yourself going to the Preview tab often, as you check to make sure the custom expression properly format elements within the report.
To add something interesting to the report, drag a Table item from the Report Items Toolbox on to the Body of your report. Tables such as ASP.NET DataGrid objects are a tabular way to represent data bound to a set of records. The first thing you want to do is to bind the Table to your Dataset. This can be done by right-clicking the properties and assigning the Dataset in the General tab in Figure 5. Although it is not necessary, this step enables Intellisense-like functionality for fields used in expressions.
In the Table Header section of the Table, type Shipping Date at the top of one column and Sales Amount at the top of another one. In the Detail section of the Table, right-click on the cell under Shipping Date. Click the drop-down box for the Value property and select Fields!ShippingDate.Value. Repeat these steps for the cell under the Sale Amount column, selecting Fields!SalesAmount.Value. In the end, the Design Tab should look like Figure 6.
Deploying the Report
Now that you have a functional, albeit boring, report, it is time to deploy it to your Report Server. Going to the Reporting Services project properties, you see a dialog box like Figure 7. Enter "http://localhost/ ReportServer" into the TargetServerURL field. You can also optionally set the TargetFolder field if you want your code deployed to a particular folder. Click OK to close this dialog box. Now when you select Deploy Solution from the Build Menu, it copies the Shared Data Source file and your report to your Report Server.
To view the report on the Report Server, open the Report Manager web site on your local machine by going to http:// localhost/Reports/Pages/Folder.aspx. Here you can navigate to your report and it provides you with an interface similar to the Preview tab of your report in Visual Studios 2003.
Calling the Report Through a Web Service
Listings One and Two present code for a report launcher page I used in a namespace called "NorthwindBI." Create a new ASP.NET project called "NorthwindBI" and include the code.
To call the Report Server web service, you must first add a new Web Reference to your project. In your NorthwindBI ASP.NET project, right-click on references and select Add Web Reference... In the URL field, type http://localhost/ReportServer/ReportService.asmx as in Figure 8. You may leave the Web Reference Name field localhost or give it another name. If you give the Web Reference a different name, make sure you update your Page directives accordingly.
As you can see, the render method of the ReportingService object rs takes numerous arguments. The ones you should be most concerned with are the ParameterValue array, the format string, and the Report Path. (For information on the other parameters that get passed to and get returned from the render method, see http://msdn.microsoft.com/library/ default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp.)
When running the ASP.NET application, it takes the date variables from your page and passes it to the render method. In this example, I set the format for report to be in PDF. You can select any of the standard export types Reporting Services supports; however, PDF is a good choice in an ASP.NET environment as it is supported by many browsers and operating systems. There is also less variation on how the report is displayed among these platforms.
When you receive the byte array back from the render method, the only task left is to stream that to the requesting client. The code in the method StreamToPDF (Listing Two) adds the proper header code to the Response object for the binary stream. Notice that the <form> tag in Listing One has the properties: encType="multipart/form-data "target="_blank". This ensures that the page properly handles the returning stream and opens the PDF document in a new window. You may also want to check that the MIME type application/pdf is properly configured in IIS.
Conclusion
Given its XML roots and .NET infrastructure, there are all kinds of ways that Reporting Services can be extended. But for now, if you have an ASP.NET application that uses SQL Server and don't particularly feel like going through the cost and frustration of using a third-party reporting tool outside of Visual Studio, choosing Reporting Services is a no-brainer.
DDJ
<%@ Page language="c#" Codebehind="ReportLauncher.aspx.cs"
AutoEventWireup="false" Inherits="NorthwindBI.ReportLauncher" %>
<HTML>
<HEAD>
<title>Report Launcher Web Form</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body>
<form id="ReportForm" method="post" runat="server"
encType="multipart/form-data" target="_blank">
<table>
<tr>
<td><b>Report Launcher Web Form</b></td>
</tr>
<tr>
<td><asp:label id="lblMessage"
Runat="server"></asp:label></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td>
<table>
<tr>
<td colSpan="2">Please enter information
for the Report</td>
</tr>
<tr>
<td colSpan="2"> </td>
</tr>
<tr>
<td>Beginning Date:
</td>
<td><asp:textbox id="txtBeginningDate"
Runat="server"></asp:textbox>
<asp:requiredfieldvalidator
id="rfvBeginningDate"
Runat="server"
ErrorMessage="Beginning
Date is required."
ControlToValidate=
"txtBeginningDate">
</asp:requiredfieldvalidator>
<asp:comparevalidator id=
"cvBeginningDate" Runat="server"
ErrorMessage="Beginnging Date
must be a Date."
Type="Date" Operator="DataTypeCheck"
ControlToValidate="txtBeginningDate">
</asp:comparevalidator></td>
</tr>
<tr>
<td>End Date:
</td>
<td><asp:textbox id="txtEndDate"
Runat="server"></asp:textbox>
<asp:requiredfieldvalidator id="rfvEndDate"
Runat="server" ErrorMessage="End Date
is required." ControlToValidate=
"txtEndDate">
</asp:requiredfieldvalidator>
<asp:comparevalidator id="cvEndDate"
Runat="server" ErrorMessage=
"End Date must be a Date."Type="Date"
Operator="DataTypeCheck"
ControlToValidate="txtEndDate">
</asp:comparevalidator></td>
</tr>
<tr>
<td colSpan="2"> </td>
</tr>
<tr>
<td align="right" colSpan="2"><asp:button
id="btnSubmit" Runat="server"
Text="Run Report"></asp:button></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</HTML>
Back to articleListing Two
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using NorthwindBI.localhost; // To reference the Web Service, it
// will be <project name>.<web reference name>
namespace NorthwindBI
{
/// <summary>
/// Summary description for ReportLauncher.
/// </summary>
public class ReportLauncher : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox txtBeginningDate;
protected System.Web.UI.WebControls.Button btnSubmit;
protected System.Web.UI.WebControls.RequiredFieldValidator
rfvBeginningDate;
protected System.Web.UI.WebControls.CompareValidator cvBeginningDate;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvEndDate;
protected System.Web.UI.WebControls.CompareValidator cvEndDate;
protected System.Web.UI.WebControls.Label lblMessage;
protected System.Web.UI.WebControls.TextBox txtEndDate;
private void Page_Load(object sender, System.EventArgs e)
{
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnSubmit.Click +=
new System.EventHandler(this.btnSubmit_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void btnSubmit_Click(object sender, System.EventArgs e)
{
// Initialize Web Service
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//Setup Parameters for Report
ParameterValue[] parms = new ParameterValue[2];
parms[0]= new ParameterValue();
parms[0].Name = "Beginning_Date";
parms[0].Value = txtBeginningDate.Text;
parms[1]= new ParameterValue();
parms[1].Name = "Ending_Date";
parms[1].Value = txtEndDate.Text;
//Setup Arguements for the render method
byte[] results = null; // Results always returns as a byte array
string reportPath = "/NorthwindBusinessIntelligence/SalesByYear";
//Do not include root path (i.e "http://...")
string format = "PDF";
//other formats include "IMAGE", "HTML5", et. al
string historyID = null;
string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar>
</DeviceInfo>"; //Turns off menu bar for the page
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
try
{
// Call the Reporting Service "render" method to get byte[]
results = rs.Render(reportPath, format, historyID, devInfo,
parms, credentials, showHideToggle, out encoding,
out mimeType, out reportHistoryParameters, out warnings,
out streamIDs);
StreamToPDF(results);
}
catch (Exception ex)
{
this.lblMessage.Text = ex.Message;
}
}
private void StreamToPDF(byte[] report)
{
// Buffer this pages output until the PDF is complete.
Response.Buffer = true;
// Tell the browser this is a PDF document so it
// will use an appropriate viewer.
Response.ContentType = "application/pdf";
// Added appropriate headers
Response.AddHeader("Content-Disposition","inline;
filename=Report.pdf");
Response.AddHeader("Content-Length", report.Length.ToString());
// Write the PDF stream out
Response.BinaryWrite(report);
// Send all buffered content to the client
Response.Flush();
}
}
}
Back to article
