Bind a Report to a Join-Based Federated Data Source

• Jul 04, 2022
• 3 minutes to read

This topic describes how to create a federated data source that joins data from multiple data sources into a single query.

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 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, click Add Query.

   

4. In the invoked Query Builder (adapted to federated data sources), drag and drop the Categories table onto the design surface.

   

5. Drag and drop the Excel data source onto the design surface. In the invoked Join Editor, select the Inner join type and create a join relationship based on the CategoryID key field.

   

6. Enable check boxes for the data fields you want to include in the query result set. The selected fields are listed in the grid.

   

   You can specify a different name for a field. Click the field’s Alias cell and type the alias.

   

   Use the grid’s empty line to add an unbound field to a query. Click the field’s ellipsis button to invoke the Expression Editor and construct an expression that specifies the field’s value.

   

7. Click OK to close the Query Builder. Click Finish to complete the Data Source Wizard.

The Wizard creates a new FederationDataSource that includes the single Categories query. This data source becomes available in the Report Explorer‘s Data Sources node. The Field List reflects the data source structure.

The federated query’s default name equals to the main table’s name (the Categories table in this tutorial). You can rename this query 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 Manage Queries dialog appears.

Double-click the table name in the left menu and use the editor to change this name.

Besides renaming table names, you can also use the Manage Queries dialog to add, modify, or delete queries.

To add a new query, click the Add button in the left menu.

Double-click a table or drag-and-drop it onto the right surface to add the table to the query.

Specify the query type. Then, use the menus on the right to configure data fields for the selected or existing tables.

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