Build Client-Side Reports Easily
VS 2005's new ReportViewer control and its built-in Report Designer enable smart-client and Web page designers to lay out, format, embed, export, and print interactive reports without running an SQL Server Report Server.
Technology Toolbox: Visual Basic; C#; SQL Server; ASP.NET; XML; SQL Server 2000, MSDE, 2005, or Express; Visual Studio 2005 Standard Edition or higher, Visual Basic or Visual C# Express, or Visual Web Developer edition; ReportViewer.exe redistributable installer for Express or Web Developer editions
Microsoft's Reporting Services add-on for SQL Server 2000 offered developers an effective and scalable alternative to Crystal Reports, Microsoft Access, or third-party reporting and charting add-ins for VS 2002/3.
SQL Server 2005 integrates Reporting Services (SSRS) installation and configuration with the database server setup program and adds a Business Intelligence (BI) Projects node to VS 2005's New Projects dialog. BI reporting options include Report Model Project, Report Server Project, and Report Server Project Wizard. Report Models provide information workers with a business layer to simplify creation of ad hoc reports for a predefined data source. The new client-side Report Buildera click-once WinForms application that users download from the Report Serverwants a Report Model as a starting point. Both Report Server project types use VS 2005's more versatile Report Designer UI to lay out tabular and crosstab (matrix) reports, charts, or any combination of reports and charts. Developers create and users open SSRS reports and models through the browser-based Report Manager.
.NET developers commonly add data-reporting features to smart-client and Web-based projects. Crystal Reports and several other third-party reporting add-ins offer VS 2005compatible report controls but have more features than many projects need. SSRS is a standalone, server-based application that's likely to be overkill for applications that need to display, print, or export a few basic reports only. Microsoft admits in its SQL Server 2005 Express (SSX) white paper that "[c]reating reporting applications is often a cumbersome process." SSRS is limited to SQL Server data sources only.
The new ReportViewer controlsintroduced by VS 2005 beta 2enable VS 2005 and Express Edition developers to design and then embed custom reports in WinForms and WebForms projects. ReportViewer controls ordinarily use a strongly typed or untyped DataSet's DataTable as a data source. You can create a ReportViewer data source from a query against any database with a native or OLE DB data provider, an XML document, or instances of business objects. You also can use the ReportViewer control to display, print, or export server-based SSRS reports. In this article, I'll show you how to get the most out of client-side ReportViewer controls and their Report Designer window. The downloadable VB 2005 sample code includes smart-client examples of tabular and crosstab reports generated from the AdventureWorks and Northwind sample databases.
Design and Embed a Local WinForm Report
You create a new local report in five steps: Create a new Windows application with a ReportViewer control; add a data source and an empty DataSet; add to the DataSet a TableAdapter with a Fill method query to define and populate a DataTable; add the report's header and data fields; and associate the report definition file with the ReportViewer control to display the initial report. I used the following steps to create the sample AWTestWinForm.sln project. You need SQL Server 2005 or SQL Express with the AdventureWorks 2005 sample database installed to run the project.
First, open a new Windows application in VS 2005, increase the size of Form1, and drag a ReportViewer control from the Toolbox's Data section to the form. The ReportViewer control consists of a pre-built ToolStrip above a report-viewing region and a ReportViewer Tasks smart tag. Select the Dock in Parent Container task to maximize the form's report-viewing area.
Second, choose Data | Add New Data Source to start the Data Source Configuration Wizard. Next, accept the default Database data source type, and click on Next. If you've specified a data connection to the source database (AdventureWorks on localhost, for this example), select it; otherwise, create a connection using Windows authentication. Click on Next, save the connection string, and click on Finish to create an empty DataSet.
Third, open the DataSet schema (AdventureWorksDataSet.xsd in this example) in the DataSet designer and drag a TableAdapter from the Toolbox to start the TableAdapter Configuration Wizard; accept the saved connection string (AdventureWorksConnectionString), then click on Next, accept the Use SQL Statements default, and click on Next again to display the Enter a SQL Statement dialog. Add a SQL statement (AWEmployess.sql) to generate an employee roster that you can group by HumanResources.Department values (see Listing 1). Now click on Finish to create the Fill and GetData expressions, right-click on DataTable1, choose Rename, change the DataTable's name to AWEmployees, and save your DataSet changes.
Fourth, return to Form1, open the smart tag, and click on the Design a New Report link to open an empty Report1.rdlc file in the Report Designer window. Drag a Table control from the Toolbox to generate the default tabular report with Header, Details, and Footer rows and three columns. Open the Data Sources window, and drag each column icon to successive cells of the Details row. (To add new columns, right-click on the right-most column's header and choose Insert Column to the Right.) For this example, add the columns in the order of their appearance in the Data Sources window. Select the Header row and apply the Bold and Underline attributes to its content (see Figure 1).
Fifth, return to Form1, activate the smart tag, open the Choose Report list, and select ProjectName.Report1.rdlc, which materializes AdventureWorksDataSet, AWEmployeesBindingSource, and AWEmployeesTableAdapter data components in the tray. Report Definition Language Client (RDLC) files are similar to SSRS RDL files, but RDLC files don't include data source or query definitions. Open Form1.vb and add this instruction as the first line of the Form1_Load event handler:
Me.AdventureWorksDataSet.EnforceConstraints = _
If you don't disable constraints, null Person.Contact.MiddleName values throw exceptions during the TableAdapter.Fill operation. Press F5 to build and run the project (see Figure 2).
The ReportViewer control's auto-generated toolstrip includes buttons that let users page the interactive report, which can have a different heightand thus a different number of pagesthan the printed report. For example, Report1.rdlc has two pages in interactive (HTML) mode and 40 pages when printed on 8.5-by-11 paper in landscape orientation. You set the InteractiveSize (Width and Height) property values by clicking on an empty region of the Report Designer and opening the Report Properties windownot the Report Properties dialog. Setting Height to 0 inches delivers a single interactive page, and a Height of 40 inches generates two pages of approximately equal length. You can set the default printing PageSize (Width and Height) and Margins (Left, Right, Top, and Bottom) in the Report Properties window or on the Report Properties dialog's Layout page. To open the Report or Table Properties dialog, right-click on an empty region of the table in the Report Designer and choose Properties. Set page Width to 11 inches and Height to 8.5 inches to specify landscape orientation. Users can override default page orientation and margin settings by clicking on the Page Setup button to open the dialog of the same name.
Other active ReportViewer toolstrip controls include a Stop Rendering button for long-running reports, a standard Print button, a Print Layout button to toggle interactive and print display, an Export button for saving reports as Excel worksheets or PDF files, a Zoom listbox with a set of fixed display scaling ratios, and a Search Text textbox with a Find/Next button combination for browser-style report searches.
Group and Format Details Rows
The Report Designer simplifies grouping and sorting tabular reports. AdventureWorks departments fall into one of six groups. To group employee details rows by department group, right-click on any Report Designer table row selector and choose Insert Group to open the Details Grouping dialog. This creates a new group with the default name TableName_Group1. Open the General page's Expression listbox and select the grouping field=Fields!Group.Value for this example (see Figure 3). Mark the "Page break at end" to separate the interactive display into six pages. Next, mark the "Repeat group header" to add a group header to each of the 24 printed pages; you need a descriptive group header for succeeding pages because the Group details field is missing. You can display groups in alphabetic order by opening the Sorting page's Expression list and making the same selection.
To add group header text, delete the redundant Group table column, and use the mouse to select the group header row's empty Last Name, First Name, M.I., and Department cells. Right-click on the selection and choose Merge Cells to provide space to display the group name by adding an "=Fields!Group.Value & ' group'" expression. Add expressions to the group footer and details footer rows, and add and populate Page Header and Page Footer elements (see Table 1). Finally, format the header and footer fonts and change the ForeColor with the Formatting toolbar (see Figure 4). Press F5 to build and run the report (see Figure 5).
ReportViewer and SSRS substitute a matrix for the conventional table structure to create crosstab reports. The most common data sources for matrix reports are aggregate rowsets delivered by queries against online transaction processing (OLTP) tables or rollups stored in data warehouses. Laying out the matrix report is similar to creating a PivotTable, but adding rows, columns, and totals isn't an intuitive process. Thus I've provided the downloadable NWTestWinForm.sln project as an example of basic and advanced Matrix report layout. You need SQL Server 2005 or SQL Express with the Northwind sample database installed to run this demonstration project.
The first step is to add a Matrix control to the Report Designer, and then drag row header, column header, and data (numeric) fields from the Data Sources window to the Matrix control's Rows, Columns, and Data cells. This example, which displays Northwind's 1997 net quarterly orders by product category and product name, has a rowset with CategoryName, ProductName, Quarter, and ProductOrders as its data source (see Listing 2) and demonstrates common Report Designer layout methods.
Create and format the basic 1997 Quarterly Orders by Product crosstab report by dragging the CategoryName column icon to the Matrix control's Rows cell, Quarter to the Columns cell, and ProductOrders to the Data cell. Right-click on the Data cell, choose properties to open the Text Box Properties dialog, click on the Format tab, type C0 as the format code for currency with no decimal values, and click on OK. (Report format codes are the same as those for DataGridView cells.) Select the Columns cell and click on the Align Right button. Add a grand total by right-clicking on the Rows cell and choosing Subtotal (see Figure 6's "Simple Matrix" design). Press F5 to display the order values for each category and quarter, as well as the total orders for each quarter.
Add a ProductName column by right-clicking on the Rows cell and choosing Add Row Group to open the Grouping and Sorting Properties dialog. Select =Fields!ProductName.Value as the expression and click on OK. Right-click on the ProductName cell and choose Subtotal to subtotal a row for each category. Replace the first Total caption with Grand Total and the second Total caption with "=Fields!CategoryName.Value & ' subtotal'" to identify the subtotal (see Figure 6's "Matrix by Product with Category Subtotals" design).
Finally, generate yearly totals for orders by product, category, quarter, and year by right-clicking on the Columns cell and choosing Subtotal to add a Total column at the right of the four Quarters columns. Rename the added column to 1997Y, and add bold formatting to column names, category names, and data column names. Then add a BackgroundColor to enhance the ProductName column (see Figure 6's "Matrix with Yearly Subtotals and Totals" design). Press F5 to generate the three matrix reports, and click on the Last Page button to display the final layout (see Figure 7).
Don't let the scarcity of documentation for the Report Designer and ReportViewer combination dissuade you from considering this useful new control for integrating tabular and crosstab reports, as well as charts and graphs, in your .NET 2.0 WinForms and WebForms projects. The ReportViewer control is lighter weight and more responsive than corresponding Crystal Reports and other third-party controls. Stay tuned for future VSM articles on using ReportViewer controls to embed reports in WebForms, display charts and graphs, and add subreports or drill-through reports.