Bind a Report to a Join-Based Federated Data Source (Runtime Sample)
- 5 minutes to read
This example demonstrates how to create a federated data source that joins data from multiple data sources into a single query.
Tip
Online Example: How to bind a report to a federated data source
Create a SqlDataSource
Create a SqlDataSource object that retrieves data from the Categories table of the sample Northwind database. See Bind a Report to an MDB Database (Runtime Sample) for more information.
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
// ...
static SqlDataSource CreateSqlDataSource() {
var connectionParameters = new SQLiteConnectionParameters("Data/nwind.db", null);
var sqlDataSource = new SqlDataSource(connectionParameters) { Name = "Sql_Categories" };
var categoriesQuery = SelectQueryFluentBuilder.AddTable("Categories").SelectAllColumnsFromTable().Build("Categories");
sqlDataSource.Queries.Add(categoriesQuery);
sqlDataSource.RebuildResultSchema();
return sqlDataSource;
}
Create an ExcelDataSource
Create an ExcelDataSource object that contains data about products. See Bind a Report to an Excel Workbook (Runtime Sample) for more information.
using DevExpress.DataAccess.Excel;
// ...
static ExcelDataSource CreateExcelDataSource() {
var excelDataSource = new ExcelDataSource() { Name = "Excel_Products" };
excelDataSource.FileName = Path.Combine(Path.GetDirectoryName(typeof(Form1).Assembly.Location), "Data/Products.xlsx");
excelDataSource.SourceOptions = new ExcelSourceOptions() {
ImportSettings = new ExcelWorksheetSettings("Sheet"),
};
excelDataSource.RebuildResultSchema();
return excelDataSource;
}
Create a FederationDataSource
Create a FederationDataSource object that joins data from the SQL and Excel data sources into a single query.
using DevExpress.DataAccess.DataFederation;
// ...
static FederationDataSource CreateFederationDataSource(SqlDataSource sql, ExcelDataSource excel) {
// Create a federated query's SQL and Excel sources.
Source sqlSource = new Source(sql.Name, sql, "Categories");
Source excelSource = new Source(excel.Name, excel, "");
// Create a federated query.
var selectNode = sqlSource.From()
// Select the "CategoryName" column from the SQL source.
.Select("CategoryName")
// Join the SQL source with the Excel source based on the "CategoryID" key field.
.Join(excelSource, "[Excel_Products.CategoryID] = [Sql_Categories.CategoryID]")
// Select columns from the Excel source.
.Select("CategoryID", "ProductName", "UnitPrice")
// Specify the query's name and build it.
.Build("CategoriesProducts");
// Create a federated data source and add the federated query to the collection.
var federationDataSource = new FederationDataSource();
federationDataSource.Queries.Add(selectNode);
// Build the data source schema to display it in the Field List.
federationDataSource.RebuildResultSchema();
return federationDataSource;
}
Bind a Report to the FederationDataSource
Create a new report and construct its layout. Use the report’s DataSource and DataMember properties to bind it to a federated data source.
using System.ComponentModel;
using System.Drawing;
using DevExpress.XtraReports.UI;
using System.IO;
// ...
public static XtraReport CreateReport() {
// Create a new report.
var report = new XtraReport();
// Create data sources.
var sqlDataSource = CreateSqlDataSource();
var excelDataSource = CreateExcelDataSource();
var federationDataSource = CreateFederationDataSource(sqlDataSource, excelDataSource);
// Add all data sources to the report to avoid serialization issues.
report.ComponentStorage.AddRange(new IComponent[] { sqlDataSource, excelDataSource, federationDataSource });
// Assign a federated data source to the report.
report.DataSource = federationDataSource;
report.DataMember = "CategoriesProducts";
// Add the Detail band and two labels bound to the federated data source's fields.
var detailBand = new DetailBand() { HeightF = 50 };
report.Bands.Add(detailBand);
var categoryLabel = new XRLabel() { WidthF = 150 };
var productLabel = new XRLabel() { WidthF = 300, LocationF = new PointF(200, 0) };
categoryLabel.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryName]"));
productLabel.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
detailBand.Controls.AddRange(new[] { categoryLabel, productLabel });
return report;
}
View the Result
You can now initialize the End-User Report Designer or Document Viewer and display the report returned by the CreateReport() function declared above. Refer to the following topics for instructions on how to do this on different platforms:
The following code snippet demonstrates how to open the resulting report in the WinForms Report Designer:
ReportDesignTool designTool = new ReportDesignTool(CreateReport());
designTool.ShowRibbonDesignerDialog();
