Bind a Report to a Join-Based Federated Data Source

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 one data table.

3. Add an Excel data source that provides the other data table.

   

Create Data Federation and Bind the Report to It

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

   

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, drag and drop the table from the SQL data source 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 relationship based on the key field.

   

6. Enable checkboxes for the data fields you want to include in the query result set.

   

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 query. This data source becomes available in the Report Explorer's Components node. The Field List reflects the data source structure.

The federated query's default name is the same as the main table's name. To rename this query, right-click the data source in the Field List or Report Explorer and select Manage Queries in the context menu.

Once you rename the query, update the report's Data Member 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 create the layout.

   

The resulting layout looks similar to the following image:

Switch to the Preview mode to see the report document.