Reporting - Data Federation (v19.1)

Sometimes, all the data you require for a report is not available in a single location. Perhaps the main application uses a SQL database, but there are secondary systems with their own data storage. Or perhaps users accumulate information in external files, for instance Excel spreadsheets. In order to create a report, you need to combine data from multiple sources. Our new Federation Data Source allows you to do this.

This new data source can combine several of the built-in reporting data source types, including the SqlDataSource, the ExcelDataSource, the JsonDataSource, the EFDataSource, the XpoDataSource and the ObjectDataSource.

You can define joins and relationships between queries and collections, across underlying data sources. Here’s how to do this using the wizard.

Data Federation Wizard

The Report Wizard now offers a new entry called Data Federation. Please note that this functionality is not supported in the Web Report Designer at this time – please let us know in the post comments if you require it. Also please be aware that the item is only visible if your report already contains other data sources.

In the data sources tree, you can check items to add queries or collections to the list FederationDataSource.Sources.

Click Manage Relations to define relationships between individual collections from different data sources.

The Add Query button invokes the Query Builder dialog. Here you can create queries that join data from multiple sources using either LEFT OUTER JOIN or INNER JOIN logic. You can also specify column aliases.

All other wizard pages are the same as usual. Once you finish setting up your report, the Field List displays the new data source and its combined structure. When you include data fields in the report design, the configured relations and querying logic are applied automatically.

This Field List image shows the Product Order information from the Excel Data Source nested underneath the Products table from the SQL Data Source, by means of the defined master/detail relationship. The item OrdersWithProductName represents the custom query shown in the previous image.

Data Federation From Code

The following sample code demonstrates how to join two different data sources using helper methods in the class SelectNodeBuilder.

FederationDataSource CreateFederationDataSource(
  SqlDataSource sql, ExcelDataSource excel)   {

  var federation = new FederationDataSource();

  // Sources are building blocks of a federation query
  var sqlSource = new Source("Sql", sql, "Products_Query");
  var excelSource = new Source("Excel", excel, "");

  // Create a federation query starting from the SQL source
  var query = sqlSource.From()
    // Select two columns, assigning an alias for ProductID
    .Select("ProductName", "ProductID".As("Sql_ProductID"))
    // Join the Excel source using an explicit condition
    .Join(excelSource, "[Sql.ProductID] = [Excel.Product ID]")
    // Select columns from the Excel source
    .Select("Product ID".As("Excel_ProductID"), "Unit Price", "Quantity", "Discount")
    // Build the federation query and assign a name
    .Build("query");

  federation.Queries.Add(query);
  federation.RebuildResultSchema();
  return federation;
}

Note that you need to add the resulting federation query to the FederationDataSource.Queries collection and then call RebuildResultSchema.

In addition, all data source components must be included in the XtraReport.ComponentStorage collection to prevent serialization issues when opening the report in the Report Designer.

var sqlDataSource = CreateSqlDataSource();
var excelDataSource = CreateExcelDataSource();

var federationDataSource =
  CreateFederationDataSource(sqlDataSource, excelDataSource);

var report = new XtraReport();
report.ComponentStorage.AddRange( new IComponent[] {
  sqlDataSource,
  excelDataSource,
  federationDataSource
});
report.DataSource = federationDataSource;

var reportDesignTool = new ReportDesignTool(report);
reportDesignTool.ShowDesignerDialog();

To define a relationship between different data sources, use the FederationDataSource.Relations collection. However, relations are built between defined queries, so these need to be added first.

FederationDataSource CreateFederationDataSourceRelation(
  SqlDataSource sql, ExcelDataSource excel)  {

  var federation = new FederationDataSource();

  var products = new Source("Sql", sql, "Products_Query");
  var productDetails = new Source("Excel", excel, "");

  // Query columns from the Products source
  var queryProducts = products.From()
    .Select("ProductName", "ProductID", "UnitsInStock", "UnitsOnOrder")
    .Build("Products_Query");
  federation.Queries.Add(queryProducts);

  // Query columns from the Product Details source
  var queryProductDetails = productDetails.From()
    .Select("Product ID".As("Excel_ProductID"), "Unit Price", "Quantity", "Discount")
    .Build("Product_Details_Query");
  federation.Queries.Add(queryProductDetails);

  // Now define the relation on the basis of the two existing queries
  // named by the first two parameters to the constructor of
  // FederationMasterDetailInfo
  federation.Relations.Add(
    new FederationMasterDetailInfo(
        "Products_Query",
        "Product_Details_Query",
        new FederationRelationColumnInfo("ProductID", "Excel_ProductID")));
  federation.RebuildResultSchema();
  return federation;
}

If you would like to see more details and examples, please refer to this sample walk-through and others in the same section of the documentation.

Status And Future Plans - Your Feedback Counts

For the future, we are considering support for unions across queries and data sources. We would really like to hear about your ideas to help us enhance this new data source component further. Please don’t hesitate to share your scenarios, either by leaving a comment below or by responding to this short survey:

Free DevExpress Products - Get Your Copy Today