Bind a Report to a Federated Master-Detail Data Source

• Jul 04, 2022
• 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

1. Create a new blank report.

2. Add a SQL data source that provides information about categories. For instance, bind the report to the Categories table of the sample Northwind database.

3. Add an Excel data source that provides information about products.

   

Create Data Federation and Bind the Report to It

1. Click the report’s smart tag, expand the DataSource property’s drop-down menu, and click Add Report Data Source.

   

2. In the invoked Data Source Wizard, select Data Federation and click Next.

   

3. 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.

   

4. In the invoked editor, drag and drop the CategoryID key field from the master query (Categories) to the detail query (Products).

   

5. 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

1. Click the report’s smart tag and select Design in Report Wizard.

   

2. In the invoked Report Wizard, select Table Report and click Next.

   

3. Select data members for the report and its detail reports. Select data fields to display in the report and click Finish. You can also go to the next page to continue layout creation.

   

The resulting layout looks similar to the following image:

Switch to the Preview tab to see the report document.