Walkthrough: Using a Database Data Source with the ReportViewer Web Server Control in Local Processing Mode

  • Article

This walkthrough shows how to create a report for a Microsoft Visual Studio 2008 ASP.NET Web site and how to add a ReportViewer control to a Web page so users can view the report.

Prerequisites

To use this walkthrough, you must have access to the AdventureWorks sample database. For more information, see Walkthrough: Installing the AdventureWorks Database.

You must also have access to a server running Microsoft Internet Information Services (IIS) version 5.0 or later with permissions to create an ASP.NET Web page.

Perform the following steps on your computer to use Visual Studio 2008 templates to create an ASP.NET Web page with a ReportViewer control configured to view a report that you create. For this example, you create the application in Microsoft Visual Basic.

Create a new Web site

  1. On the File menu, point to New, and select Web Site.

  2. In the New Web Site dialog box, select ASP.NET Web Site.

  3. In the Language list, choose Visual Basic, and click OK.

  4. In the Location box, choose HTTP if it is not already selected, then click Browse to navigate to a new folder.

  5. In the Choose Location dialog box, click Local IIS, and navigate to your Web site root directory.

  6. Click the Create New Web Application button to create a new folder and type in a name. Press ENTER.

  7. Click Open.

  8. Click OK.

    The Web site project opens showing the Default.aspx Web page.

  9. In Solution Explorer, check that Default.aspx is high-lighted.

  10. On the View menu, select Designer.

    You are now ready to modify your Web site default page.

Define a data connection and DataTable by adding a DataSet

  1. On the Website menu, select Add New Item.

  2. In the Add New Item dialog box, click DataSet. Click Add. When prompted if you should add the item to the App_Code folder, click Yes. This adds a new XSD file DataSet1.xsd to the project, opens the DataSet Designer, and invokes the TableAdapter Configuration Wizard.

  3. On the Choose Your Data Connection page, if you installed AdventureWorks using SQLExpress, you may see an existing data connection sqlexpress.AdventureWorks.dbo that you can select, and skip to step 6. Otherwise, click New Connection.

  4. In the Data Source dialog box, select Microsoft SQL Server (Sql Client).

  5. In the Server name dialog box, enter the name of the server where the AdventureWorks sample database is located. For example, the SQL Server Express default instance name is (local)\sqlexpress. After you enter the server name, select AdventureWorks from the database drop-down list, and click OK.

  6. Click Next.

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

  8. On the Choose the Command Type page, select Use SQL Statements. Confirm the other radio buttons are not selected.

  9. On the Enter a SQL Statement page, enter the following Transact-SQL query to retrieve data from the AdventureWorks database, and then click Finish. You can also click on the Query Builder button and use Query Builder to create the query and check it using the Execute Query button.

    SELECT d.Name as Dept, s.Name as Shift, e.EmployeeID as EmployeeID
FROM (HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory e
    ON d.DepartmentID = e.DepartmentID)
INNER JOIN HumanResources.Shift s
    ON e.ShiftID = s.ShiftID

    If the query does not return the expected data, you might be using an earlier version of AdventureWorks. For more information about installing the SQL Server 2005 version of AdventureWorks, see Walkthrough: Installing the AdventureWorks Database.

  10. On the Choose Methods to Generate page, accept the defaults Fill a DataTable with Method name: Fill and Return a DataTable with Method name: GetData. Click Next.

  11. On the Wizard Results page, click Finish.

    You have now completed configuring the ADO.NET DataTable as data source for your report.

  12. On the DataSet Designer page in Visual Studio, you should see the DataTable you added (the default name is DataTable1), listing the columns resulting from your query definition. From the Data menu, you can select Preview Data, and then click the Preview button, to check your query results.

Design the report

  1. From the Website menu, select Add New Item.

  2. From the Add New Item dialog, select the Report template, enter a name for the report file, and then click Add. This creates a report definition file (the default is Report.rdlc), launches the Report Designer, and displays the Website Data Sources pane.

  3. Open the Toolbox. Click on a textbox and then on the report design surface.

  4. Enter a report title in the textbox: # of Employees per Shift per Dept. Expand the textbox if necessary.

  5. From the Toolbox, drag a Matrix report item onto the report below the textbox. The dotted white background is your report page. To adjust the matrix location on the page, click the left corner to select it, and drag or use arrow keys to move the item as needed.

  6. From the Website Data Sources window, expand the DataTable1 node until you see the columns from your query. Drag Dept onto the Rows textbox in the first column, second row of the matrix.

  7. Drag Shift onto the Columns textbox in the second column, first row of the matrix. While the textbox is selected, in the Properties window, set the TextAlign property to Right. Click in the textbox to activate the Report Formatting toolbar, and click the Bold button.

  8. Select the matrix by clicking anywhere in the matrix. Right-click and select the matrix by name (the default is matrix1). An outline will appear around the matrix report item. Right-click the outline and select Properties. Check that the title of the dialog that opens is Matrix Properties.

  9. Click the Groups tab. In the Columns section, click Edit.

  10. In the Sorting and Grouping dialog, click the Sorting tab.

  11. Click in the first box under Expression to activate the textbox. From the drop-down list, choose =Fields!Dept.Value. This ensures the report data will be sorted by Department name. Click OK.

  12. Click OK to close the Matrix Properties dialog.

  13. From the Website Data Sources window, drag the EmployeeID field onto the Data textbox in the second column, second row of the matrix. Right-click this textbox and select Expression.

  14. Edit the default Sum aggregate function and change it to Count. Click OK.

  15. Click OK to close the Textbox Properties dialog.

  16. On the File menu, select Save All.

Add a ReportViewer control to the application

  1. In Solution Explorer, right-click Default.aspx, select View Designer.

  2. Open the Toolbox window. From the Data group, drag a ReportViewer control onto the page.

  3. If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the triangle in the upper right corner of the ReportViewer control on the Web page. In the Choose Report box, select the fully qualified file name for Report.rdlc.

    When you select a report, instances of data sources used in the report are created automatically. Code is generated to instantiate each DataTable (and its DataSet container) and an ObjectDataSource control corresponding to each data source used in the report. This data source control is configured automatically.

Note

To understand why ASP.NET uses an ObjectDataSource control and not a SqlDataSource control, even though your underlying data source may be a database or some other strongly typed data store, see Creating Data Sources for a Report.

Build and run the application

  1. Press CTRL+F5 to run the page without debugging or F5 to run with debugging.

    As part of the build process, the report is compiled and any errors found (such as a syntax error in an expression used in the report) are added to the Task List.

    The Web page appears in the browser. The ReportViewer control displays the report. You can use the toolbar to browse through the report, zoom, and export to Excel.

  2. Close the browser.

See Also

Reference

Microsoft.Reporting.WinForms.ReportViewer.Drillthrough
Microsoft.Reporting.WinForms.LocalReport.SubreportProcessing
Microsoft.Reporting.WebForms.ReportViewer.Drillthrough
Microsoft.Reporting.WebForms.LocalReport.SubreportProcessing

Concepts

Using the ReportViewer Tasks Smart Tags Panel

Other Resources

Samples and Walkthroughs