ControlFreak

LANGUAGES:VB.NET | C#

ASP.NETVERSIONS: 2.x

Reporting Made Easy

The New ReportViewer Control in Visual Studio 2005 ProvidesRich Capabilities to Fulfill All Your Reporting Needs

By Steve C. Orr

One of the more powerful and underrated new controlsavailable for ASP.NET 2.0 is ReportViewer. This control, and its Windows Formscounterpart, solves a long-standing need for which every developer must find asolution: Reporting. In the past, developers typically needed to use CrystalReports or roll their own reporting solution. Although Crystal Reports can be agood solution for large enterprise reporting systems, its complexity is oftenoverkill for smaller software systems.

The ReportViewer control is included with Visual Studio2005 (Professional version and above). A ReportViewer add-in component forVisual Web Developer Express is also freely downloadable from Microsoft. TheReportViewer control integrates quite well with SQL Server 2005 ReportingServices, although this marriage is entirely optional. The control can work onits own without SQL Server or Reporting Services; its only dependency is the.NET 2.0 Framework.

Creating a New Report

Figure 1 shows what the ReportViewer control looks likewhen dropped onto a WebForm. If you?ve previously created a report (with SQLServer Reporting Services or without) it can be selected from the dropdown listshown in the smart tag. Clicking the Design a new report action item willresult in a blank report (*.RDLC file) and a prompt for a data source. Afteradding the data source, a query must be specified to provide data for thereport. Stored procedures can be specified for databases that support suchfunctionality, and a handy query builder tool can be used to build SQLstatements for those that don?t. The query builder is shown in Figure 2.

Figure 1: The new ASP.NET 2.0ReportViewer control is thoroughly configurable at design time.

Figure 2: The built-in query buildertool makes constructing SQL statements easy.

After completing the TableAdapter Configuration Wizard shownin Figure 3, a strongly typed dataset is automatically created and added to theproject. A related ObjectDataSource object is also automatically created andconfigured to bind the report to the data.

Figure 3: The TableAdapterConfiguration Wizard.

The slickness of Visual Studio?s ReportViewer wizard mightunderstandably convince a person that data access capabilities are built intothe ReportViewer control ? but this is not the case. The ReportViewer controldoes not handle data access; it merely displays already-fetched data in anattractive way. The strongly typed dataset that fetches the data is entirelyseparate, even though Visual Studio conveniently glues them together. Ifpreferred, any other valid data source can be substituted. For example,developers can create their own ObjectDataSource that connects to businessobjects in an n-tier model instead ofconnecting directly to a database.

Report Designer Controls

At this point the rest of the report creation is primarilya matter of drag and drop simplicity, thanks to the built-in report designer. Figure4 shows the various windows at your disposal for this process. Data fields canbe dragged onto the report and configured independently via the propertieswindow. Report items can be dragged from the toolbox. These tools includesimple items such as lines and rectangles, all the way up to rich tools such asthe Table and Chart controls. Don?t get the report controls confused with theirsimilar ASP.NET Web control counterparts; these controls are for reports only. Thereare nine report controls in the toolbox, all of which are summarized in thetable in Figure 5.

Figure 4: Once the data sources areestablished, creating the report (including fancy embedded charts) is as easyas dragging fields and report objects into place and configuring theirproperties.

Figure 5: Thereport designer provides nine individual toolbox controls to aid in the designand creation of custom reports.

The TextBox control can be used to display static text ordynamic fields. It provides a variety of useful properties for styling andformatting the text in nearly any way imaginable. The Action property allowsthe control to act as a hyperlink to URLs, bookmarks, or drilldown reports. TheValue property can be filled with any text value, or it can be bound to anunderlying data field by filling it with a value that complies with thissyntax:

=Fields!MyFieldName.Value

Similarly, the Image control can be used to display anembedded, external, or dynamic image. (Images can be embedded in the report viathe report?s EmbeddedImages property.) The Image control also has an Actionproperty to allow it to act as a bookmark/hyperlink. The border can be styledin a variety of ways, and a flexible Sizing property allows images to beauto-sized.

The List control is one of the most useful. It is similarto the ASP.NET repeater control. Any fields dragged into this panel-likecontrol will be repeated once for each row (or grouping) in the underlying datasource. This control (or the Table control) is a must for every report thatlists multiple records. Figure 6 shows the List control in action (with a redborder).

Figure 6: The ASP.NET ReportViewercontrol has rich run-time capabilities, such as built-in paging, zooming,searching, and exporting. Reports can be automatically exported in Excel or PDFformat, with zero lines of code required.

While the List control is great for freestyle placement ofrepeating records, the Table control is usually a better way to get headers,footers, and detail sections working together in an organized fashion, as isusually required by professional-quality reports. After dragging a Tablecontrol onto the report surface, it will appear with three columns and threerows. Columns can be added and removed via the context (right click) menu. Thethree rows are fixed in design mode for a good reason. The top row is theheader, generally used for labels and column descriptions. Fields dragged tothe middle row will repeat for each row or group defined by the underlyingquery. Fields dragged into the bottom (footer) row will automatically be filledwith aggregate clauses so they can show totals, averages, etc. The Tablecontrol has an important property named RepeatHeaderOnNewPage, which is auseful feature the List control doesn?t have. The Table control is highlyconfigurable, with many properties for each row and column. There are manyuseful dialog boxes to explore, which allows for myriad customizations.

The Rectangle control can be used for basic cosmeticpurposes just like the Line control. However, the Rectangle control alsoprovides useful functionality by acting as a container for other controls sothey can be moved, hidden, or shown as a group.

The Subreport control can be used to embed child reportswithin the current report.

The Chart control is a surprisingly rich solution forreports that need to include charts and graphs. The control provides roughly twodozen distinct chart types, including bar, pie, bubble, scatter, and stockcharts (among many others). Configuring fields, series, and categories is aseasy as dragging fields onto the marked sections of the grid at design time (asshown in Figure 4).

Fine Tuning

When a report is active in the designer of Visual Studio,a Report dropdown menu is available that contains many useful commands. Itenables viewing of the report Header and Footer area, for example. It alsoprovides access to the report data source(s) and embedded images.

The report?s properties dialog box can also be invokedfrom the dropdown menu. This dialog box allows configuration of the pagemargins, assembly references, and data transformation schemas. It also providesan area to enter custom code. Additionally, it provides a place to configuregeneral information about the report, such as the author, description, andseconds for the (optional) auto-refresh feature that can keep the report dataup-to-date in near real-time.

The final dialog box available from the report dropdownmenu is used to configure any report parameters that may be required. Defaultparameters can be configured, as well as parameter lists and user prompts forsituations where required parameters are not already supplied. Because theReportViewer control does not execute queries, report parameters are onlyrelevant when used with a server report (which requires SQL Server reportingservices). Parameters are ignored in local report mode.

Alternatively, most of the items available from the Reportdropdown menu are also available in the properties window when the reportobject is selected in the designer. Most of these items are also available atrun time.

Code Mode

It?s possible (and sometimes necessary) to interact withthe report at run time. For example, the following code can programmaticallyset parameters for a report:

RptViewer1.ServerReport.SetParameters(MyParameterCollection)

There are also more than a dozen potentially useful eventsthat the ReportViewer control raises. The Search event can be useful if you?dlike to implement custom searching functionality, although the built-in searchfeature works well for most situations. The DrillThrough event can certainly beuseful for drilldown reports, and the Back event is raised when the user exitsthe drilldown report on their way back to the main report:

Protected Sub ReportViewer1_Drillthrough(ByVal _

 sender As Object, ByVal eAs _

 Microsoft.Reporting.WebForms.DrillthroughEventArgs)_

 HandlesReportViewer1.Drillthrough

 Dim localReport =e.Report

 localReport.DataSources.Add(New _

   ReportDataSource("Employees", ObjectDataSource2))

End Sub

Other useful events include: Init, Load, PreRender,ReportError, Sort, BookmarkNavigation, DocumentMapNavigation, Unload, and more.Unfortunately, there is nothing resembling a RowDataBound event (like theDataGridView control has), which I imagine could be quite useful.

SQL Server 2005 Reporting Services

SQL Server 2005 Reporting Services shares a lot of codewith the .NET 2.0 ReportViewer control. The report creation experience is verysimilar, no matter which tool is chosen to create reports, because they both sharethe Visual Studio report designer. They even share the same file format ? sortof. Reporting Service?s *.RDL files share the same XML Schema with theReportViewer?s *.RDLC files, although minor differences exist between the waythe two programs use the files.

Because the report design experience is so similar in bothtools, why would a person opt for the more expensive solution of SQL Server2005 Reporting Services? One of the biggest reasons is scalability; theconfigurability and caching features of Reporting Services are difficult tomatch with custom code. Central report management, storage, and security arealso enticing reasons. Additional report export formats are also an incentiveto consider using Reporting Services. The ReportViewer control can export onlyto Excel and PDF formats, but SQL Server 2005 Reporting Services can export to suchformats as Excel, PDF, TIFF, JPEG, HTML, XML, and Word.

Reporting Services reports execute their own queriesdirectly on the database server; therefore, you may find it necessary to passparameters to the report so the query can use them. If Reporting Services isnot used, report parameters are ignored because you?re expected to pre-executeyour own query locally (usually via ADO.NET) and bind the results to thereport.

While the ASP.NET 2.0 ReportViewer control integrates wellwith SQL Server 2005 Reporting Services, you should be aware that it is notdirectly compatible with SQL Server 2000 Reporting Services. However, as anexperiment I renamed a SQL Server 2000 Reporting Services file, gave it an RDLCextension, then pasted it into an ASP.NET project where Visual Studio 2005seamlessly upgraded it. Then it was simply a matter of hooking it up to anADO.NET data source to get it to work.

Windows Forms Reports

The Windows Forms ReportViewer control contains reportingfunctionality virtually identical to its ASP.NET counterpart, such asrendering, paging, zooming, sorting, exporting, etc. The Windows FormsReportViewer control can even use the same report files, thus increasing thereusability of reports. The data sources that the reports use may or may not beas easy to reuse between projects, depending on how you?ve set them up. Thesame Visual Studio report designer is used, so the report creation experienceis pretty much identical whether creating a Web or Windows application.

As you?d expect, the Windows Forms ReportViewer controlhas a richer and more responsive set of client-side features, including nearly90 events ? and almost as many properties. This makes it easy to integrate thecontrol within a custom Windows application in nearly any way imaginable.

Conclusion

The ReportViewer control in Visual Studio 2005 makes pastreport creation techniques obsolete. The hand-wringing many of us had to endurewhen choosing a reporting tool is now history. Every application can now haverich built-in reporting capabilities without relying on expensive, clunky third-partycomponents. Enterprise applicationscan optionally take advantage of the central management and scalabilityfeatures of SQL Server Reporting services for all, some, or none of theirreports. Reports can be hand crafted with a rich set of tools and reusedbetween Windows and Web applications seamlessly. As far as reporting goes, nowwe all get to live happily ever after.

Steve C. Orr is anMCSD and a Microsoft MVP in ASP.NET. He?s been developing software solutionsfor leading companies in the Seattlearea for more than a decade. When he?s not busy designing software systems orwriting about them, he can often be found loitering at local user groups andhabitually lurking in the ASP.NET newsgroup. Find out more about him at http://SteveOrr.netor e-mail him at mailto:Steve@Orr.net.

Useful References

Sample Code and FAQ: http://www.gotreportviewer.com

Microsoft Report Viewer Redistributable 2005: http://www.microsoft.com/downloads/details.aspx?FamilyID=8a166cac-758d-45c8-b637-dd7726e61367&DisplayLang=en