Add Reports and Charts to Web Pages
The ASP.NET 2.0 version of Visual Studio 2005's ReportViewer control and its Report Designer let Web page developers quickly embed, format, and export interactive reports and charts without running a SQL Server Report Server.
March 1, 2006
Technology Toolbox: Visual Basic, SQL Server, ASP.NET, XML, SQL Server 2005 Express Edition or higher and Visual Studio 2005 or Visual Basic, Visual C#, or Visual Web Developer Express editions with the Report Builder and ReportViewer add-in
It's convenient to be able to embed reports and charts in Windows forms, but you often need to make these same reports and charts available to those who must view them from the Internet or an intranet.
I described how to build Windows form (smart client) applications that use VS 2005's new ReportViewer controls to incorporate embedded reports and charts in a recent issue (see "Build Reports More Easily," VSM November 2005). Now I'll show you how to accomplish the next step, which is using the Report Designer and ASP.NET 2.0 ReportViewer server control to create Web pages that contain reports and charts. I'll also describe how to repurpose the Report Definition Language Client (RDLC) XML files you created for smart client projects in the earlier article for use with the ASP.NET 2.0 ReportViewer server control. You'll also learn how to take advantage of XML InfoSet documents as the data source for smart client and Web-based projects, and overcome fatal "Failed to enable constraints" exceptions in Web reports. Along the way, I'll walk you through the important code-behind, design, and rendering differences between Windows and Web forms reports.
ReportViewer controls let you dispense with SQL Server Reporting Services (SSRS), which now requires a licensed edition of SQL Server 2005. The current version of SQL Server 2005 Express (SSX) doesn't support SSRS, but Microsoft promises a future SSX upgrade with a limited SSRS feature set. Unlike SSRS, which requires SQL Server 2005 or SSX, ReportViewer controls are database agnostic. You can use any database engine that has a native .NET, OLE DB, or ODBC driver that meets ADO.NET 2.0's requirements. You also can create a data source from an XML data document or instances of custom business objects.
This article's downloadable VB 2005 sample code includes Windows and Web form examples of tabular and crosstab reports, and charts that you generate from the AdventureWorks 2005 and Northwind sample databases (see the Go Online Box for download details). You must run the Transact-SQL scripts to install these two databases on your local instance of SQL Server 2005 or SSX to run the sample projects. If you use SSX, change the SQL Server connection string's localhost to localhost\SQLEXPRESS. The sample code installs by default in the current drive's \ReportViewer\ReportSmartClient and \ReportViewer\ReportWebSite folders. If you're running Visual Web Developer Express, install the Report Builder and ReportViewer (SQLServer2005_ReportAddIn.msi) add-ins before running the sample source code (see Additional Resources for links to the required files.)
Designing and embedding a tabular report in a Web form requires four basic steps: embed the ReportViewer server control; specify a report data source with the Table Adapter Configuration Wizard; add and populate a Table control in the Report Designer; and format the report Web page. Together, these steps let you create a simple report Web page. For the sake of simplicity, the example in this article uses the Northwind Employees table.
Embed the ReportViewer
Begin by opening a new file-system Web site and naming it ReportWebTest. Next, open Default.aspx in Design mode and drag a ReportViewer server control from the toolbox's Data category to the form to generate a ReportViewer1 server control (see Figure 1).
Now open ReportViewer1's Report Viewer Tasks smart tag, click on the Design a New Report link to add an empty Report1.rdlc file to the project, and open the Web Site Data Sources window. Next, click on the window's Add New Data Source link to generate a DataSet1.xsd file with an empty TableAdapter and start the Table Adapter Configuration Wizard. Add a new connection to localhost or localhost\SQLEXPRESS with Windows authentication and Northwind as the default database, click on the Next button, and save the connection with its default name, NorthwindConnectionString. Click on the Next button again, accept the default Use SQL Statements option, and click on the Next button to open the Enter a SQL Statement dialog.
Use this T-SQL statement to generate a simple employee roster report:
SELECT EmployeeID, FirstName, LastName, Title,
Country, Extension, Notes FROM Employees
Back to top