Bind a Report to a Federated Master-Detail Data Source
- 3 minutes to read
This topic describes how to create a federated data source that retrieves data from multiple data sources into separate queries. The topic also shows how to specify a master-detail relationship between these queries.
Create a Report and Data Sources
Add an Excel data source that provides information about products.
Create Data Federation and Bind the Report to It
Click the report’s smart tag, expand the DataSource property’s drop-down menu, and click Add Report Data Source.
In the invoked Data Source Wizard, select Data Federation and click Next.
On the next page, enable check boxes for the SQL data source’s Categories table and the Excel data source. The selected items are included to data federation as separate queries.
Click Manage Relations to specify a master-detail relationship between these queries.
In the invoked editor, drag and drop the CategoryID key field from the master query (Categories) to the detail query (Products).
Click OK to close the editor. Click Finish to complete the Data Source Wizard.
The Wizard creates a new FederationDataSource that includes two queries with a master-detail relationship. This data source becomes available in the Report Explorer‘s Data Sources node. The Field List reflects the data source structure.
The Wizard specifies query names as follows:
- If the initial data source contains data at the root level (as the Excel data source), the federated query’s name equals to the data source name.
- If the initial data source contains one or more queries (as the SQL data source), the federated query’s name consists of the data source name and query name separated by the underscore.
You can rename queries in the Manage Queries dialog. To invoke it, right-click the data source in the Field List or Report Explorer and select Manage Queries in the context menu.
The master-detail relationship’s name changes accordingly.
Once you rename the query, update the report’s DataMember property.
Design the Report Layout
Click the report’s smart tag and select Design in Report Wizard.
In the invoked Report Wizard, select Table Report and click Next.
The resulting layout looks similar to the following image:
Switch to the Preview tab to see the report document.