Reporting Made Easy

ControlFreak

LANGUAGES: VB.NET | C#

ASP.NET VERSIONS: 2.x

 

Reporting Made Easy

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

 

By Steve C. Orr

 

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

 

The ReportViewer control is included with Visual Studio 2005 (Professional version and above). A ReportViewer add-in component for Visual Web Developer Express is also freely downloadable from Microsoft. The ReportViewer control integrates quite well with SQL Server 2005 Reporting Services, although this marriage is entirely optional. The control can work on its 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 like when dropped onto a WebForm. If you ve previously created a report (with SQL Server Reporting Services or without) it can be selected from the dropdown list shown in the smart tag. Clicking the Design a new report action item will result in a blank report (*.RDLC file) and a prompt for a data source. After adding the data source, a query must be specified to provide data for the report. Stored procedures can be specified for databases that support such functionality, and a handy query builder tool can be used to build SQL statements for those that don t. The query builder is shown in Figure 2.

 

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

 

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

 

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

 

Figure 3: The TableAdapter Configuration Wizard.

 

The slickness of Visual Studio s ReportViewer wizard might understandably convince a person that data access capabilities are built into the ReportViewer control but this is not the case. The ReportViewer control does not handle data access; it merely displays already-fetched data in an attractive way. The strongly typed dataset that fetches the data is entirely separate, even though Visual Studio conveniently glues them together. If preferred, any other valid data source can be substituted. For example, developers can create their own ObjectDataSource that connects to business objects in an n-tier model instead of connecting directly to a database.

 

Report Designer Controls

At this point the rest of the report creation is primarily a matter of drag and drop simplicity, thanks to the built-in report designer. Figure 4 shows the various windows at your disposal for this process. Data fields can be dragged onto the report and configured independently via the properties window. Report items can be dragged from the toolbox. These tools include simple items such as lines and rectangles, all the way up to rich tools such as the Table and Chart controls. Don t get the report controls confused with their similar ASP.NET Web control counterparts; these controls are for reports only. There are nine report controls in the toolbox, all of which are summarized in the table in Figure 5.

 

Figure 4: Once the data sources are established, creating the report (including fancy embedded charts) is as easy as dragging fields and report objects into place and configuring their properties.

 

Report Designer Controls	Description
TextBox	Useful for displaying static labels, as well as for containing formulas and bindings for display of dynamic data.
Line	Useful for cosmetic purposes. Color, width, and style properties can be adjusted.
Table	The most functionally rich way to display repeating data.
Matrix	Useful primarily for displaying aggregate data, and has a configurable number of rows and columns.
Rectangle	Acts as a container for other controls. Has useful properties for adjusting the background and borders.
List	Can be used to display repeating data, although the Table control is often a better choice.
Image	For displaying images on a report. Can optionally be bound to the underlying data source to display dynamic images.
Subreport	Embeds a child report within the current report.
Chart	Provides the ability to output roughly two dozen distinct chart types to any report.

Figure 5: The report designer provides nine individual toolbox controls to aid in the design and creation of custom reports.

 

The TextBox control can be used to display static text or dynamic fields. It provides a variety of useful properties for styling and formatting the text in nearly any way imaginable. The Action property allows the control to act as a hyperlink to URLs, bookmarks, or drilldown reports. The Value property can be filled with any text value, or it can be bound to an underlying data field by filling it with a value that complies with this syntax:

 

=Fields!MyFieldName.Value

 

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

 

The List control is one of the most useful. It is similar to the ASP.NET repeater control. Any fields dragged into this panel-like control will be repeated once for each row (or grouping) in the underlying data source. This control (or the Table control) is a must for every report that lists multiple records. Figure 6 shows the List control in action (with a red border).

 

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

 

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

 

The Rectangle control can be used for basic cosmetic purposes just like the Line control. However, the Rectangle control also provides useful functionality by acting as a container for other controls so they can be moved, hidden, or shown as a group.

 

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

 

The Chart control is a surprisingly rich solution for reports that need to include charts and graphs. The control provides roughly two dozen distinct chart types, including bar, pie, bubble, scatter, and stock charts (among many others). Configuring fields, series, and categories is as easy as dragging fields onto the marked sections of the grid at design time (as shown 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. It enables viewing of the report Header and Footer area, for example. It also provides access to the report data source(s) and embedded images.

 

The report s properties dialog box can also be invoked from the dropdown menu. This dialog box allows configuration of the page margins, assembly references, and data transformation schemas. It also provides an area to enter custom code. Additionally, it provides a place to configure general information about the report, such as the author, description, and seconds for the (optional) auto-refresh feature that can keep the report data up-to-date in near real-time.

 

The final dialog box available from the report dropdown menu is used to configure any report parameters that may be required. Default parameters can be configured, as well as parameter lists and user prompts for situations where required parameters are not already supplied. Because the ReportViewer control does not execute queries, report parameters are only relevant when used with a server report (which requires SQL Server reporting services). Parameters are ignored in local report mode.

 

Alternatively, most of the items available from the Report dropdown menu are also available in the properties window when the report object is selected in the designer. Most of these items are also available at run time.

 

Code Mode

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

 

RptViewer1.ServerReport.SetParameters(MyParameterCollection)

 

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

 

Protected Sub ReportViewer1_Drillthrough(ByVal _

 sender As Object, ByVal e As _

 Microsoft.Reporting.WebForms.DrillthroughEventArgs) _

 Handles ReportViewer1.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 the DataGridView control has), which I imagine could be quite useful.

 

SQL Server 2005 Reporting Services

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

 

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

 

Reporting Services reports execute their own queries directly on the database server; therefore, you may find it necessary to pass parameters to the report so the query can use them. If Reporting Services is not used, report parameters are ignored because you re expected to pre-execute your own query locally (usually via ADO.NET) and bind the results to the report.

 

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

 

Windows Forms Reports

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

 

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

 

Conclusion

The ReportViewer control in Visual Studio 2005 makes past report creation techniques obsolete. The hand-wringing many of us had to endure when choosing a reporting tool is now history. Every application can now have rich built-in reporting capabilities without relying on expensive, clunky third-party components. Enterprise applications can optionally take advantage of the central management and scalability features of SQL Server Reporting services for all, some, or none of their reports. Reports can be hand crafted with a rich set of tools and reused between Windows and Web applications seamlessly. As far as reporting goes, now we all get to live happily ever after.

 

Steve C. Orr is an MCSD and a Microsoft MVP in ASP.NET. He s been developing software solutions for leading companies in the Seattle area for more than a decade. When he s not busy designing software systems or writing about them, he can often be found loitering at local user groups and habitually lurking in the ASP.NET newsgroup. Find out more about him at http://SteveOrr.net or e-mail him at mailto:[email protected].

 

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