Reporting - Data Federation (v19.1)

Reporting Team Blog
22 May 2019

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.

Report Wizard Data Federation

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

Data Sources Tree

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

Manage Relations

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.

Query Builder

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.

Field List

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:

13 comment(s)
Christian Peters
Christian Peters

FireBird would be nice

22 May, 2019
Cedrus Bank SAL
Cedrus Bank SAL

Can the 'FederationDataSource' be used as an independent component in applications other than the XtraReports ?

22 May, 2019
Alexander Krakhotko (Xafari team)
Alexander Krakhotko (Xafari team)

great news!

add scenario

1) lage table FederationDataSource (100M+ rows on join tables)

2) filter for FederationDataSource

3) pagination data loading from FederationDataSource

22 May, 2019
Santiago Moscoso
Santiago Moscoso

Can it be used in the End-User Designer for the Report and Dashboard Server?

22 May, 2019
James Lincoln
James Lincoln

Seems like federation provides a mechanism to build sub-queries where the sources of queries are no longer limited to tables, but rather can be built on top of other queries.    I am assuming this would even work in cases where all queries are driven from the same data or database.   Do you agree ?

Do sources of federation also include select stored procedures ?    In any case this is a great addition !

22 May, 2019
Jose Isga
Jose Isga

Great Job, Excellent!!!

1. We need the Data Federation Datasource supported in the Web Report Designer.

2. We need the Data Federation Datasource supported in the Web Dashboards Designer.

3. Can we use the new Data Federation Datasource in others components like Grids?

Thank you.

22 May, 2019
Thomas Reichenberger
Thomas Reichenberger

We also need the Data Federation Datasource supported in the Web Report Designer!

22 May, 2019
Gabriel Phaiphai
Gabriel Phaiphai

I agree with Jose Isga,  

Maybe do a survey on those a well to gauge the feature's demand

22 May, 2019
Yaroslav (DevExpress Support)
Yaroslav (DevExpress Support)

Hi everyone,

Thanks for all the feedback on this. I confirm that FederationDataSource can be used separately and it does support stored procedures. If you have a particular technical matter to discuss, create a support ticket at devexpress.com/ask. Improving data federation is going to be one of our main goals for the next releases, so your ideas and suggestions would be greatly appreciated.

As for the Web End-User Report Designer, why it is not possible to prepare a ready-to-use datasource in advance? Do you expect your end-users to be proficient enough be refine data and create advanced queries from multiple datasources? Please keep the votes and use-cases coming in. All your feedback will be shared with the feature team.

@Santiago: Currently, the Report and Dashboard Server does not support this feature.

22 May, 2019
William Parr
William Parr

Is there a plan to have Data Federation Datasources to be used as the Datasource in an Data extract datasource?  Or be able to use Data extract datasources in Data Federation Datasource?

14 June, 2019
Andrey (DevExpress)
Andrey (DevExpress)

@William Parr

We consider providing support for Data Federation as a source for Extract Data Source and will implement this feature in one of future major releases.

As for the use of Extract as a source for the Federation, we have not planned this feature so far because of lack of use cases.

If you have a scenario where such a feature can bring a benefit, please share it with us, and we will discuss it.

17 June, 2019
Raoulw
Raoulw

Hi Andrey,


Use case:


I have an ERP system that holds Orders its a direct access database. I need to use a data extract because of the size of the tables.


I have a proposals system which is an XAP/XPO application. 


I need to display the summary of the Proposal.Value + Order.Value to do forecasting. So I need a data federation of an  XPO and legacy database.


Thanks,

22 August, 2019
Andrey (DevExpress)
Andrey (DevExpress)

@Raoulw

I'm happy to say that in early preview v19.2 you can both use Data Federation as a source for Extract and use Extract as a source for Data Federation.

You can find more details in the DevExpress Dashboard - Early Access Preview (v19.2)  blog post.

23 August, 2019

Please login or register to post comments.