A newer version of this page is available. Switch to the current version.

Bind a Report to a 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.

Create a SqlDataSource

Create a SqlDataSource object that retrieves data from the Northwind database’s Categories 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_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 = "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 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;

// ...

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();

See Also