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 an 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 AdventureWorks2008 sample database. For more information, see Walkthrough: Installing the AdventureWorks Database.

Perform the following steps on your computer to create an ASP.NET Web page with a ReportViewer control configured to view a local report. 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, in the Installed Templates pane, select Visual Basic, and then choose ASP.NET Web Site.

  3. In the Location box, specify a project directory and click OK.

    The Web site project opens.

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, select DataSet and 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 and opens the DataSet Designer.

  3. From the Toolbox window, drag a TableAdapter control to the design surface. This invokes the TableAdapter Configuration Wizard.

  4. On the Choose Your Data Connection page, click New Connection.

  5. If this is the first time you create a data source in Visual Studio, you will see the Choose Data Source page. In the Data Source box, select Microsoft SQL Server.

  6. In the Add Connection dialog box, perform the following steps:

    • In the Server name box, enter the server where the AdventureWorks2008 database is located.

      The default SQL Server Express instance is (local)\sqlexpress.

    • In the Log on to the server section, select the option that provides you access to the data. Use Windows Authentication is the default.

    • From the Select or enter a database name drop-down list, click AdventureWorks2008.

    • Click Next.

  7. If you specified Use SQL Server Authentication in the previous step, select the option whether to include the sensitive data in the string or set the information in your application code.

  8. On the Save the Connection String to the Application Configuration File page, type in the name for the connection string or accept the default AdventureWorks2008ConnectionString. Click Next.

  9. On the Choose a Command Type page, select Use SQL Statements, and then click Next.

  10. On the Enter a SQL Statement page, enter the following Transact-SQL query to retrieve data from the AdventureWorks2008 database, and then click Finish.

    SELECT d.Name as Dept, s.Name as Shift, 
    e.BusinessEntityID 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

    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. If the query does not return the expected data, you might be using an earlier version of AdventureWorks. For more information about installing the AdventureWorks2008 version of AdventureWorks, see Walkthrough: Installing the AdventureWorks Database.

  11. You have now completed configuring the ADO.NET DataTable as data source for your report. 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.

  12. On the Choose Methods to Generate page, click Finish.

  13. Save the file.

Design the report using the Report Wizard

  1. Make sure that the top-level Web site is selected in Solution Explorer.

  2. Right-click on the Web site and select Add New Item.

  3. In the Add New Item dialog box, select Report Wizard, enter a name for the report file, and then click Add.

    This launches the Report Wizard.

  4. In the Dataset Properties page, in the Data source box, select the DataSet you created.

    The Available datasets box is automatically updated with the DataTable you created.

  5. Click Next.

  6. In the Arrange Fields page do the following:

    1. Drag Dept from available fields to the Row groups box.

    2. Drag Shift from available fields to the Column groups box.

    3. Drag EmployeeID from available fields to the Values box.

    4. Click the arrow next to Sum(EmplyeeID) and select Count.

  7. Click Next twice, then click Finish to close the Report Wizard.

    This creates the .rdlc file and opens it in Report Designer. The tablix you designed is now displayed in the design surface.

  8. Save the .rdlc file.

Add a ReportViewer control to the application

  1. In Solution Explorer, right-click the Web form Default.aspx and select View Designer.

  2. Open the Toolbox window. From the AJAX Extensions group, drag a ScriptManager control onto the design surface.

  3. From the Toolbox window, in the Reporting group, drag a ReportViewer control onto the page below the ScriptManager control.

  4. If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the smart-tag glyph on the top right corner. In the Choose Report box, select report you created.

    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.

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