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 2006 · by Roger Jennings
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. 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
Click on Advanced Options to open the dialog of the same name, clear the Generate Insert, Update and Delete Statements check box, and click on OK. Note that you don't need DataSet update capability for reports. Click on the Next button, accept the default Fill and Get Data Method Names, clear the Create Methods to Send Updates Directly to the Database check box, and click on the Next and Finish buttons to add DataSet1 to the Website Data Sources window—also called the Field List. You don't need code to populate the DataTable when the page loads.
You're now ready to add and populate a Table control in the Report Designer. Drag a Table control from the Toolbox's Report Items category to the upper left area of Report1.rdlc's empty Body region. The default Table (table1) includes three rows of text boxes—Header, Detail and Footer—and three empty columns. Drag the EmployeeID, LastName, and FirstName field icons from the Website Data Sources window to the text boxes in columns 1, 2 and 3 of the Detail (center) row. Dropping a field in the Detail row adds the formatted field name to the Header text box and an =Fields!FieldName.Value expression to the Detail text box. Right-click on the right-most column header and select Insert Column to the Right to add columns for the Title, Country, and Extension fields (see Figure 2) .
The report width increases automatically as you add columns. (You add the ntext Notes column after formatting the report and page in the next step, because the width of the Notes column depends on available page width.) Expand the width of ReportViewer1 to about 750px, then press F5 to build and load the page in the ASP.NET Development Server (Cassini). Accept the Modify the Web.config File to Enable Debugging option when the Debugging Not Enabled message box opens, and click on the OK button. If the page is empty, close Internet Explorer (IE) 6.0, return to page Design mode, open the Report Viewer Tasks smart tag, and select Report1.rdlc from the Choose Report list. Next, click on the Choose Data Sources link to open the eponymous dialog and verify that the Data Source Instance is ObjectDataSource1 (see Figure 3). Finally, re-run the report. You'll use these features when you repurpose RDLC files from Windows forms later in the article.
Format the Final Report
The fourth and final step for embedding a tabular report in a Web form requires formatting the report to optimize consumption of browser real estate for your users' lowest display resolution or browser-window size, which this article's examples and the downloadable sample projects assume to be 800 by 600 pixels. Start by changing ReportViewer1's width property value from 750px to 100%. Unfortunately, you can't set the ReportViewer control's height in percent and specify XHTML 1.0 standards-mode processing. If you set 100% as the height property value, rendering the report in XHTML 1.0 standards-mode displays only the toolstrip; to view the report with quirks-mode rendering, remove this line from the Default.aspx source code:
Be forewarned: There's no guarantee that IE 7.0 will render quirks-mode reports correctly.
Select table1 in the Report Designer, click on the plain button in the Table's upper left corner to display sizing handles, and drag table1 to the upper left corner of the Body section. Click on the Header button to select the Header row, and click on the toolbar's Bold and Underline buttons to emphasize text. Change the Employee ID header to ID, and reduce the column width to 0.25 in.; reduce the Last Name and First Name column width to 0.88 in.; change the Title column width to 1.75 in.; change the Country and Extension headers to Ctry and Ext and the column widths to 0.38 in. Add a column and drag the Notes field to its Details text box; set the column width to 3.13 in. and drag the right edge of the Body section to the right edge of table1. Next, replace Untitled Page with Northwind Traders Employee Roster in the Default.aspx Source window, and press F5 to build and run the project to test your work so far.
You can delete the report footer row if you want, but that's not required. If you want to add a page header, page footer, or both, simply choose Report | Page Header or Report | Page Footer and add text or expressions. Most Web reports are single-page, so you can simplify the toolstrip by setting ReportViewer1's ShowPageNavigationControls property to False. You can add green-striping to the report by selecting Report1.rdlc's Details row (TableRow2), opening its Properties window, selecting the BackgroundColor property to open the color picker, clicking on the Web tab, and selecting
=IIf(RowNumber(Nothing) Mod 2,
"PaleGreen", "White")
Click on OK, then press F5 to build and render the page in IE (see Figure 4). Typing a search term in the text box or selecting a report export format enables the associated link buttons (see Figure 5). Unlike the Windows toolstrip, the Web form toolstrip has no print-related buttons. You set margins and print the report by executing IE's File | Page Setup, File | Print Preview, and File | Print commands.
Clone Web Report Pages
Report-oriented Web sites usually have multiple pages, enabling users to navigate easily between related reports without moving to another URL. This design model is similar to a Windows form project, such as ReportSmartClient.sln, that uses pages of a Tab control to display different reports (see Figure 6). Cloning pages for multiple reports saves substantial developer/designer time and effort. After you create a workable starting page, such as NWEmployees.aspx, you can copy and paste the page to the project, and then rename the cloned pages. For example, four of the five pages of the sample ReportWebSite project—NWOrders1997.aspx, NWOrdersChart.aspx, AWEmployees.aspx and AWCategories.aspx—began as clones of NWEmployees.aspx.
You can copy and paste RDLC files for the original and cloned pages from a corresponding Windows form project. The NWOrders1997Win.rdlc, NWOrdersChartWin.rdlc, AWEmployeesWin.rdlc, and AWCategoriesWin.rdlc files originate in the ReportSmartClient.sln project. Data source instances for Web-based reports differ considerably from their Windows form counterparts, so you must recreate the ObjectDataSource for each report page except AWCategories.aspx. However, you can create the required ObjectDataSource by copying the DataSetName.xsd file from the Windows form project to the Web site's App_Code folder.
Recreating an ObjectDataSource for a cloned page is simple. Open the ReportViewer Tasks smart tag and select the appropriate RDLC file from the Choose Report list. Most cloned pages have an existing ObjectDataSource1 instance, in which case selecting the new RDLC file adds an ObjectDataSource2 instance. Delete the added instance, open ObjectDataSource1's Tasks smart tag, click on the Configure Data Source link, and verify or change the object name in the Object Data Source Wizard's first dialog. Complete the Wizard's steps, click on the ReportViewer Tasks smart tag's Choose Data Sources link, and verify or change the Data Source Instance to ObjectDataSource1. This technique applies to conventional report pages, such as the ReportWebSite's NWEmployees.aspx, NWOrders1997.aspx, and NWOrdersChart.aspx.
The AWCategories.aspx page, like the AWCategories tab page of the Windows form project, requires code-behind to create and populate a runtime DataSet's DataTable from an XML data document (see Listing 1). The code to create a runtime, untyped DataSet for the Page_Load event handler is almost identical to that for the Form_Load event handler (see Listings 2 and 3). AWCategories.aspx illustrates a complex page that includes a background image, a complex matrix report that resembles an Excel PivotTable, and multiple detail charts. Unlike the corresponding Windows form report, the detail charts are bitmaps, so zooming the charts to 75% or smaller makes the charts impossible to read.
| Use an XML InfoSet Document as a Web ReportViewer Data Source
Listing 1. ReportViewer controls require a typed or untyped DataSet with a DataTable to serve as the report's data source. This code behind the AWCategories.aspx page loads Listing 3's XML data into an untyped, runtime DataSet that's assigned to the ReportViewer's LocalReportDataSources(0) object. The bold attribute emphasizes the modifications required to the corresponding Windows form code. Note that you can't use the "|DataDirectory|" connection string macro, which points to the project's \App_Data folder, in file path statements. Imports System |
AWEmployees.aspx demonstrates an undocumented problem with data sources that execute SELECT queries. They generate this nasty exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." You can use the DataTable.GetErrors( ) method to find out what rows and columns have errors, but you might find that eliminating the errors is impossible. Windows forms let you bypass this problem by adding an AdventureWorksDataset.EnforceConstraints = False statement before the Me.AwEmployeesTableAdapter.Fill(Me.AdventureWorksDataset.AWEmployees) method call. Unfortunately, this simple approach doesn't work for Web form reports because their DataSets don't expose the EnforceConstraints property. The upshot of this omission is that you need to create a new AdventureWorksDataset instance and set its EnforceConstraints property value to False, and then generate and fill a new TableAdapter instance (see Listing 4). Issue a ReportViewer1.LocalReport.DataSources.Clear( ) instruction before adding the new DataSet instance with the ReportViewer1.LocalReport.DataSources.Add( ) method. If you don't clear the DataSources, autogenerated C# DataSet designer code will raise the error when processing the required, but unusable, ObjectDataSet.
| Eliminate Web Forms' "Failed to enable constraints" Exceptions
Listing 4. Some SELECT queries, such as that for the AWEmployees.rdlc report's data source, throw this exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Windows forms let you ignore the exception by adding a DataSetName.EnforceConstraints = False instruction before you invoke the TableAdapterName.Fill( ) method. However, setting the EnforceConstraints property value in a Web form requires creating a new DataSet instance, which requires creating and filling a new TableAdapter. Imports System |
The Report Designer and ReportViewer combination lets you quickly integrate tabular and crosstab reports, as well as charts and graphs, in your .NET 2.0 Windows and Web forms projects. Migrating Windows form RDLC files to corresponding Web-based reports is easy and most cloned Web reports don't require adding code. A limitation of Web reports is the poor readability of zoomed charts and graphs. But the primary ReportViewer drawback for .NET developers is the fact that Microsoft provides even less documentation for Web-based reports than Windows-based reports. Apart from these drawbacks, the .NET 2.0 ReportViewer reports are ready for primetime in Windows and Web forms.
Roger Jennings is an independent architect/database developer and writer with more than 1.25 million English copies printed. Roger's latest .NET book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He''s also a Visual Studio Magazine contributing editor and online columnist, and manages the OakLeaf Systems blog. His Code of Federal Regulations Web services won Microsoft''s 2002 .NET Best Horizontal Solution Award. Reach him at roger_jennings@compuserve.com.
No comments:
Post a Comment