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:
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.
On the first page of the invoked Data Source Wizard, select Database and click Next to proceed.
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.
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.
Click Next to proceed to the next wizard page.
Click Next on the following page to save the newly created connection string to the configuration file.
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.
In the invoked editor, drag and drop key fields (columns) to connect them.
Click OK to close the editor.
To shape data at the data source level, create a custom query. Expand Queries and click the 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.
The newly created SQL Data Source is listed under the Report Explorer‘s Data Sources 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.