All docs
V20.2
20.2
20.1
19.2
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

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.

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 Access97ConnectionParameters("Data/nwind.mdb", "", "");
    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;
// ...
static ExcelDataSource CreateExcelDataSource() {
    var excelDataSource = new ExcelDataSource() { Name = "Excel_Suppliers" };
    excelDataSource.FileName = "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();

See Also