Bind a Report to a Database
This tutorial demonstrates how to bind a report to a hierarchical data source and specify a master-detail relationship between data source queries:
Click the report's smart tag. In the invoked actions list, expand the drop-down menu for the Data Source property and click Add New DataSource.
On the first page of the invoked Data Source Wizard, specify whether you want to use an existing data connection or create a new data connection from scratch. Select the first option to create a new connection and click Next.
On the next page, select Microsoft SQL Server and click Next to proceed.
This page allows you to specify connection string parameters specific to the selected data source provider.
Depending on the data provider selected, it may be necessary to specify additional connection options (such as the authentication type and database name) on this page.
To proceed to the next wizard page, click Next.
On the next page, you can choose which tables, views and/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, connect the required key fields (columns) using drag and drop.
Click OK to close the editor.
Note
When you are required to shape data at the level of a data source, you can create custom queries by expanding the Queries category and clicking the plus button.
This will invoke the Query Builder where you can create complex queries by joining multiple tables, filtering, sorting and grouping their data, as well as calculating various aggregate functions.
Although it is also possible to join different tables within a single query, creating hierarchical data sources is preferred in most cases to provide better performance (in general, master-detail reports are generated faster than similar-looking reports created by grouping "flat" data sources).
Click Finish to complete the Data Source Wizard. If the selected queries or stored procedures contain any parameters, you can go to the next wizard page and define their values.
The newly created SQL data source will be displayed in the Data Sources node of the Report Explorer. Additionally, the hierarchy of the data source will be reflected by the Field List. In both panels, you can right-click the data source to access its settings.
