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: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