Walkthrough: Creating a ReportViewer Report

Article
07/24/2009

This walkthrough shows how to create a simple table report in a Microsoft Visual Studio 2008 Windows application project based on the AdventureWorks sample database. You will add a report template to your project, set up connection information for the AdventureWorks database, define a query, add a table data region, and add a ReportViewer Windows Forms control to a Windows Form so the report can be viewed by users of the application.

Prerequisites

To use this walkthrough, you must have access to the AdventureWorks sample database for SQL Server 2005. If you use an earlier version of AdventureWorks, the query will fail. For more information about how to get the SQL Server 2005 version of AdventureWorks, see Walkthrough: Installing the AdventureWorks Database.

This walkthrough assumes that you are familiar with Transaction-SQL queries and ADO.NET DataSet and DataTable objects.

To create a new Windows-based application project

Open Visual Studio. On the File menu, point to New, and then select Project.
In the Project Types pane, choose Visual Basic.
In the Templates pane, choose Windows Application to create a Microsoft Windows-based application.
In the Name box, type SimpleReport.
In the Location box, enter the directory in which you want to save your project, or click Browse to navigate to it.

The Windows Forms Designer opens, showing Form1 of the project you created.
Click on the form. From the View menu, choose Properties Window. Expand the Size property to show Width and Height. Set Width to 500 pixels.

To define a data source connection and a data table

In Solution Explorer, right-click on the project named SimpleReport (not the solution), point to Add, and select New Item. If the Solution Explorer window is not visible, in the View menu, click Solution Explorer.
In the Add New Item dialog, click DataSet. Type a name for the dataset and click Add. The default name is DataSet1.xsd.

This adds a new XSD file to the project and opens the DataSet Designer.
From the View menu, click Designer. Open the Toolbox and drag a TableAdapter control onto the DataSet design surface.

This starts the TableAdapter Configuration Wizard.
On the Choose Your Data Connection page, click New Connection.
On the Add Connection page, perform the following steps:
- In the Data Source box, select Microsoft SQL Server.
- In the Server name box, enter the server where the AdventureWorks database is located.
  
  The default SQL Server Express instance is (local)\sqlexpress.
- From the drop-down list, click AdventureWorks.
- Click OK to continue to return to the wizard, then click Next.
On the Save the Connection String to the Application Configuration File page, type in the name for the connection string or accept the default AdventureWorksConnectionString. Click Next.
On the Choose the Command Type page, select Use SQL Statements, and click Next.
On the Enter a SQL Statement page, enter the following Transact-SQL query to retrieve sales data from the AdventureWorks database, and then click Finish:
```
SELECT  S.OrderDate, S.SalesOrderNumber, S.TotalDue AS TotalSales, 
        C.FirstName, C.LastName
FROM    HumanResources.Employee E INNER JOIN
        Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
        Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
```
You can also click on the Query Builder button and use Query Builder to create a query and validate it using the Execute Query button.

The Dataset Designer now shows the DataTable definition for DataTable1 with fields named from the columns and column aliases of the query (OrderDate, SalesOrderNumber, TotalSales, FirstName, and LastName). You will use these fields from the Data Sources window when binding data to your report data regions.

Note

If you need to change the fields in your data table, right-click on the DataTable1 header or the DataTable1TableAdapter header on the DataSet Designer page. Choose Configure, which restarts the TableAdapter Configuration Wizard.

To add a new report definition file

From the Project menu, point to AddNew Item.
In the Add New Item dialog box, click Report.
In Name, type Sales Orders.rdlc and then click Add to open a graphical design surface.

The graphical design surface is part of the Report Designer component of Visual Studio 2008.

To add a table to the report layout

With Sales Orders.rdlc in graphical design mode, from the View menu, select the Toolbox.

The Toolbox opens.
From the ToolboxData section, click Table, then click the report design surface.

Report Designer displays a table with three columns, spanning the width of the report.
Click the table so that column and row handles appear above and next to the table.
On the first column, right-click on the handle and then click Insert Column to the Left.
From the Properties window for table1, expand the Size node. By default, the Properties window is docked below Solution Explorer. You can also open this window from the View menu by selecting Properties Window.
Set Width property of the Size node to 4.8 in. This sets the table width and sets the column widths to equal spacing for viewing in the form.
In Report Designer, click the design surface.
From the Properties window, expand the Size node and set the Width to 5 in.
From the Data Sources window, click the DataTable1 node to expand it and show the data fields. Then perform the following steps:
- Drag the LastName field from the Data Sources window to the first column, middle (detail) row in the table.
- When you drop the field into the middle cell, two things happen.
  
  First, the detail cell will contain the following text: =Fields!LastName.Value. This text is a field expression that specifies data values for the LastName field. Fields that you add to a Detail row are always specified as expressions.
  
  Second, a column header value is automatically placed in the first row, just above the field expression. By default, the column is generated from the field name. For field names that use Pascal casing, column names will be each capitalized word separated by a space. For example, LastName becomes Last Name.
- Drag the OrderDate field from the Data Sources window to the second column, middle (detail) row in the table.
- Drag the SalesOrderNumber field from the Data Sources window to the third column, middle (detail) row in the table.
- Drag the TotalSales field from the Data Sources window to the last column, middle (detail) row in the table.
The following diagram shows a table data region that has been populated with these fields: LastName, OrderDate, SalesOrderNumber, and TotalSales.

To add the ReportViewer control to your form

Click Form1.vb in Solution Explorer.
From the View menu, choose Designer.
From the Data section of the Toolbox, drag the ReportViewer control to the form.
Click in the form. From the Properties window, expand Size. Set the width and height properties as needed.
Open the smart tags panel of the ReportViewer control by clicking the triangle on the top right corner of the control. Click the Choose Report drop-down list and select Sales Orders.rdlc.
From the smart tags panel, click Dock in parent container.

Throughout the rest of this walkthrough, you can build your application and view the report in the form at any time. If you want to see how the incremental changes of your report design affects the end report, build and view the report as the last step in each of the following procedures.
(Optional) Press F5 to build your application and view the report in the form.

The following diagram shows the current report definition rendered in your form. You will see a header row, one detail row for every row in the data table, and a footer row on the last page of the report, all in default format.

To format a date field

Right-click the cell with the OrderDate field expression and then click Properties. The Textbox Properties dialog box appears.
Select the Format tab and click the browse (…) button to open the Choose Format dialog box.

In the Format box, select Standard, select Date, and then select a date format.
Click OK to close the Choose Format dialog box, and then click OK again to close the Textbox Properties dialog box.
(Optional) Press F5 to build your application and view the report. In this case, you see the date column with the formatting you just applied.

To format a currency field

Right-click the cell with the TotalSales field expression and then click Properties.
Select the Format tab and click the browse (…) button to open the Choose Format dialog box.
In the Format box, select Standard, select Currency, and then select a currency format.
Click OK, and then click OK again to close the Textbox Properties dialog box.
(Optional) Press F5 to build your application and view the report. In this case, you see the Total Sales column values displayed with a currency format.

To format table headers

Click the table so that column and row handles appear above and next to the table.

Note

Handles are gray boxes that appear above and next to the table. You use handles to perform various actions on columns, rows, and the table itself. The handles that run across the top of the table are column handles. The handles that run down the side of the table are row handles. The handle where the column and row handles meet is the corner handle. To view an example of table handles, see Adding Table Data Regions (Visual Studio Report Designer).
Select the row handles for the first row, which contain the column header labels, and then click Bold.
Click the table header row and then click Background Color. Click the Web tab and select MistyRose. Click OK.
(Optional) Click F5 to build your application and view the report. The following diagram shows the formatted report.

To define a group for a tabular report

Click the table so that column and row handles appear above and next to the table.
Right-click on the handle of any row and then click Insert Group.
On the General tab, in the Group on box, select =Fields!LastName.Value in the first row and =Fields!FirstName.Value in the second row. This groups the data by sales person name. Notice that you can group data by fields that are not used in the table data region but are available from DataTable1 fields.
Click OK.

A group header and a group footer are added to the table

To summarize data by group

Click on the group header row and then click Background Color. Choose the Web tab and select LightCyan. Click OK.
Replace the TotalSales repeated in every row with an expression in the group header that is a combined total for the group.
1. Right-click the group header for Total Sales and choose Properties. The Textbox Properties dialog opens.
2. Copy the text below and paste it into the Value drop-down list.
```
=Sum(Fields!TotalSales.Value)
```
3. Apply currency formatting to this textbox.
Replace the name repeated in every row with an expression in the group header that includes two lines. The first line is the first and last name. The second line is the number of sales that were non-null.
1. Select the expression from the Last Name detail row and delete it.
2. Right-click in the group header row for Last Name and choose Expression. Copy the following text and paste it in the Edit Expression dialog area.
```
Fields!FirstName.Value + " " + Fields!LastName.Value + ": " + vbCrLf + Count(Fields!SalesOrderNumber.Value).ToString()
```
The following diagram shows the expression editor after this step.
(Optional) Press F5 to build your application and view the report. The diagram below shows the table rows grouped by each salesperson's name. Each group header summarizes the detail rows for the group, showing the salesperson's name, number of sales, and total amount sold.

To sort groups in a tabular report

Select the table data region and open Table Properties.
Click the Groups tab. The only group defined, table1_Group1, is automatically selected. Click Edit. The Grouping and Sorting dialog opens.

Verify that the open dialog box is Grouping and Sorting Properties, not Table Properties. The Table Properties Sorting tab controls the way the detail rows are sorted, not the way the groups are sorted. You will set detail sorting in the next procedure.
Click the Sorting tab. In the Sort on box, select <Expression...> from the drop-down list. Type the expression you want to sort on into the Edit Expression box. For example:

=Count(Fields!SalesOrderNumber.Value)
Click OK.
(Optional) Press F5 to build your application and view the report. Your report is now sorted by the number of sales made by each salesperson.

To sort the detail rows within a group in a tabular report

Click the table so that column and row handles appear above and next to the table.
Right-click the corner handle and then click Properties. The Table Properties dialog box opens.

Note

The corner handle is the handle where the column and row handles meet.
On the Sorting tab, in the Sort on box, select =Fields!TotalSold.Value. In the Direction box, select Descending. This will sort the detail data by amount sold starting with the highest values.
Click OK.

The Table Properties dialog box should look similar to the following diagram.
(Optional) Press F5 to build your application and view the report. The following diagram shows page 2 of the resulting report, to show groups with more than one detail row.

To add summary data to the footer in a tabular report

Select the Sales Order and Total Sales cells in the table footer row. Right-click in the selected cells and choose Merge Cells.

This provides more space to format the sum of all sales for the table.
Right-click in the single, merged cell and choose Properties.
In the Value drop-down list, type the following expression:

=Sum(Fields!TotalSales.Value)
Apply currency formatting to the merged cell.
Click in the text box cell next to the merged cells, and create a label. For example, type Total Sold:. Note that this is not an expression, simply text.
(Optional) Set the background color on the footer row to match the header row.

The following diagram shows the table definition.
(Optional) Press F5 to build your application and view the report. On the rendered report, click the LastPage button in the report toolbar to navigate to the last page of the report. Scroll to the bottom and you will see the total sales value.

Share via