Bind a Report to a Union-Based Federated Data Source (Runtime Sample)
- 6 minutes to read
You can create a federated data source for your report to display data combined from several sources. This topic demonstrates how to use the Union operation to combine data into a single query.
Tip
Online Example: Bind a Report to Union-Based Federated Data Source
Create a SqlDataSource
Create a SqlDataSource object that retrieves data from the Northwind database’s Customers table. 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_Customers" };
var categoriesQuery = SelectQueryFluentBuilder.AddTable("Customers").SelectAllColumnsFromTable().Build("Customers");
sqlDataSource.Queries.Add(categoriesQuery);
sqlDataSource.RebuildResultSchema();
return sqlDataSource;
}
Create an ExcelDataSource
Create an ExcelDataSource object that contains data about suppliers. See Bind a Report to an Excel Workbook (Runtime Sample) for more information.
using DevExpress.DataAccess.Excel;
using System.IO;
// ...
static ExcelDataSource CreateExcelDataSource() {
var excelDataSource = new ExcelDataSource() { Name = "Excel_Suppliers" };
excelDataSource.FileName = Path.Combine(Path.GetDirectoryName(typeof(Form1).Assembly.Location), "Data/Suppliers.xlsx");
excelDataSource.SourceOptions = new ExcelSourceOptions() {
ImportSettings = new ExcelWorksheetSettings("Sheet"),
};
excelDataSource.RebuildResultSchema();
return excelDataSource;
}
Create a FederationDataSource
Create a FederationDataSource object that uses the Union operation to combine 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, "Customers");
Source excelSource = new Source(excel.Name, excel, "");
// Create a federated Union query.
var contactsNode = sqlSource.From()
// Select the "ContactName", "City" and "Phone" columns from the SQL source.
.Select("ContactName", "City", "Phone")
.Build()
// Union the SQL source with the Excel source.
.Union(excelSource.From()
// Select the "ContactName", "City" and "Phone" columns from the Excel source.
.Select("ContactName", "City", "Phone").Build(),
UnionType.Union)
// Specify the query's name and build it.
.Build("Contacts");
// Create a federated data source and add the federated query to the collection.
var federationDataSource = new FederationDataSource();
federationDataSource.Queries.Add(contactsNode);
// 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 the federated data source.
using System.ComponentModel;
using System.Drawing;
using DevExpress.XtraReports.UI;
// ...
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 = "Contacts";
// Add the Detail band and labels bound to the federated data source's fields.
var detailBand = new DetailBand() { HeightF = 50 };
report.Bands.Add(detailBand);
var contactNameLabel = new XRLabel() { WidthF = 150 };
var cityLabel = new XRLabel() { WidthF = 150, LocationF = new PointF(200, 0) };
var phoneLabel = new XRLabel() { WidthF = 200, LocationF = new PointF(400, 0) };
contactNameLabel.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ContactName]"));
cityLabel.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[City]"));
phoneLabel.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[Phone]"));
detailBand.Controls.AddRange(new[] { contactNameLabel, cityLabel, phoneLabel });
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();
