Bind a Report to a Database

  • 3 minutes to read

This tutorial demonstrates how to bind a report to a hierarchical data source at design time in Visual Studio and specify a master-detail relationship between SQL Data Source queries.

Do the following to accomplish this task:

  1. Click the report’s smart tag. In the invoked actions list, expand the Data Source property’s drop-down list and click Add Report Data Source.

    Add Report Data Source

  2. On the first page of the invoked Data Source Wizard, select Database and click Next to proceed.

    Select Database

  3. Choose whether to create a new data connection or use an existing connection from the Visual Studio Server Explorer. Select the first option to create a new connection and click Next.

    Create New Connection

  4. On the next page, specify the connection string or select one of the supported data providers.

    Some data providers require that you specify additional connection options (such as an authentication type or a database name).

    In this example, the report is bound to a sample Northwind database hosted on a Microsoft SQL Server.

    MS SQL Server COnnection Properties

    Click Next to proceed to the next wizard page.

  5. Click Next on the following page to save the newly created connection string to the configuration file.

    Save Connection

  6. On the next page, choose tables, views, or stored procedures to add to the report.

    To create a master-detail report, select two or more tables and click Manage Relations.

    Edit Relations

    In the invoked editor, drag and drop key fields (columns) to connect them.

    Relations Editor

    Click OK to close the editor.

    NOTE

    To shape data at the data source level, create a custom query. Expand Queries and click the Plus button.

    This invokes the Query Builder - where you can join multiple tables, filter, sort, and group data, and calculate aggregate functions to create complex queries.

    Although you can join different tables within a single query, we recommend that you create hierarchical data sources for better performance. (Master-detail reports are generated faster than similar-looking reports where “flat” data sources are grouped.)

    If the Wizard fails to retrieve the data source schema, it prompts to execute the query.

    Execute Query

    Click Finish to complete the Data Source Wizard. To specify parameters for a query or stored procedure, go to the next wizard page instead and specify parameter values.

The newly created SQL Data Source is listed under the Report Explorer‘s Components node. The data source hierarchy is shown in the Field List. In both panels, you can right-click the data source to access its settings.

SQL Data Source

See Also