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
Add a SQL data source that retrieves data from the
Invoices
view of the Northwind database.Add a JSON data source that retrieves customer data from the https://raw.githubusercontent.com/DevExpress-Examples/DataSources/master/JSON/customers.json location.
Create Data Federation
Invoke the designer menu and click Add Data Source.
Select Data Federation in the invoked Data Source Wizard and click Next.
On the next page, click the + (plus) button and select Join to add a Join query.
In the invoked Query Builder, drag and drop the table from the SQL data source onto the design surface.
Drag and drop the JSON data source onto the design surface.
Click a field in one table and drag it to the related field in another table to create a Join relationship.
Select the line that indicates a relationship and click the Edit button in the upper right corner of the design surface to invoke the Relation Properties editor.
The editor allows you to change the join type and operator.
Select the data fields that you want to include in the result set.
You can specify a different name for a field. Click the field's Alias cell and enter the field name.
Click the + (plus) button to add a field to the query. Click the field's f-button to switch to the Expression edit mode.
Click the ellipsis (...) in the field to invoke the Expression Editor and construct an expression for the field value:
[Invoices.UnitPrice] * [Invoices.Quantity]
Click OK to close the Expression Editor.
Specify Amount as the alias for the newly created calculated field.
Click OK to close the Query Builder.
Click Finish to complete the Data Source Wizard.
The Wizard creates a new FederationDataSource that includes a single query.
Manage the Federation Data Source
The federation data source is shown in the Field List.
When you create a federated query, its name is set to the name of the first table added to the query. You can rename the query in the Manage Queries dialog. To invoke the dialog, click the Manage Queries button.
The Manage Queries dialog appears.
Click the query name in the list to invoke the text editor and change the name.
The Manage Queries dialog allows you to add, modify, or delete queries.
To add a new query, click the Add query drop-down in the bottom left corner and select the query type: Join, Union, or Transform. The Query Builder is invoked to help you construct a new query.
To edit a query, select the query name in the list and click the Edit button that appears in the selected item. The Query Builder window is invoked to help you edit the query.
To delete a query, select the query name in the list and click the Delete button that appears in the selected item. The query is deleted without confirmation.
Note
Once you rename the query, update the report's Data Member property.