How to: Bind a Dashboard to a Federated Data Source
- 4 minutes to read
This example demonstrates how to bind a dashboard to a federated data source created at runtime.
The key object in the federated data source is a federated query. This example uses two methods to create two identical federated queries.
Method 1: Data Federation API
The first approach uses Data Federation API. It has the following steps:
Create the Source object for each data source integrated in the federated data source.
Create a new SelectNode object to start building a federated query.
Create a SourceNode object for each data source.
Create and add expressions to the SelectNode.Expressions collections. Each expression defines a data column in the resulting dataset.
Combine rows from data sources with JOINS. Specify the SelectNode.Root and add join elements to the SelectNode.SubNodes collection.
Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.
Method 2: SelectNodeBuilder object
The second approach uses the DevExpress.DataAccess.DataFederation.SelectNodeBuilder object to create a federated data source in code. It has the following steps:
Create the Source object for each data source integrated in the federated data source.
Use the Source.From extension method to access the SelectNodeBuilder and create a SelectNode object.
Add the SelectNode object to the FederationDataSourceBase.Queries collection as a federated query.
Result
The DashboardFederationDataSource that contains two federated queries in its Queries collection is added to the Dashboard.DataSources collection. It is available for binding as any other dashboard data source.
The Query Builder that displays the created federated query is shown in the following picture:
Note
The complete sample project How to Bind a Dashboard to a Federated Data Source Created at Runtime is available in the DevExpress Examples repository.
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.DataFederation;
using DevExpress.DataAccess.Excel;
using DevExpress.DataAccess.Sql;
// ...
DashboardFederationDataSource federatedDS = CreateFederatedDataSource(sqliteDataSource, exceldataSource, objectDataSource);
dashboard.DataSources.Add(federatedDS);
private static DashboardFederationDataSource CreateFederatedDataSource(DashboardSqlDataSource sqliteDataSource, DashboardExcelDataSource exceldataSource, DashboardObjectDataSource objectDataSource)
{
DashboardFederationDataSource federationDS = new DashboardFederationDataSource();
Source sqlSource = new Source("sqlite", sqliteDataSource, "SQLite Orders");
Source excelSource = new Source("excel", exceldataSource, "");
Source objectSource = new Source("SalesPersonDS", objectDataSource, "");
#region Use-API-to-create-a-query
SelectNode mainQueryCreatedByApi = new SelectNode();
SourceNode excelSourceNode = new SourceNode(excelSource, "ExcelDS");
SourceNode objectSourceNode = new SourceNode(objectSource, "ObjectDS");
SourceNode sqliteSourceNode = new SourceNode(sqlSource, "SQLite Orders");
mainQueryCreatedByApi.Alias = "FDS-Created-by-API";
SourceNode root = new SourceNode(sqlSource, "SQLite Orders");
mainQueryCreatedByApi.Root = root;
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "SalesPerson", Node = objectSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "Weight", Node = objectSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "CategoryName", Node = excelSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ProductName", Node = excelSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "OrderDate", Node = sqliteSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCity", Node = sqliteSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "ShipCountry", Node = sqliteSourceNode });
mainQueryCreatedByApi.Expressions.Add(new SelectColumnExpression() { Name = "Extended Price", Node = excelSourceNode });
mainQueryCreatedByApi.SubNodes.Add(new JoinElement(excelSourceNode, JoinType.Inner, "[ExcelDS.OrderID] = [SQLite Orders.OrderID]"));
mainQueryCreatedByApi.SubNodes.Add(new JoinElement(objectSourceNode, JoinType.Inner, "[ObjectDS.SalesPerson] = [ExcelDS.Sales Person]"));
#endregion
#region Use-NodedBuilder-to-create-a-query
SelectNode mainQueryCreatedByNodeBuilder =
sqlSource.From()
.Select("OrderDate", "ShipCity", "ShipCountry")
.Join(excelSource, "[excel.OrderID] = [sqlite.OrderID]")
.Select("CategoryName", "ProductName", "Extended Price")
.Join(objectSource, "[SalesPersonDS.SalesPerson] = [excel.Sales Person]")
.Select("SalesPerson", "Weight")
.Build("FDS-Created-by-NodeBulder");
#endregion
federationDS.Queries.Add(mainQueryCreatedByNodeBuilder);
federationDS.Queries.Add(mainQueryCreatedByApi);
federationDS.CalculatedFields.Add("FDS-Created-by-NodeBulder", "[Weight] * [Extended Price] / 100", "Score");
federationDS.Fill(new DevExpress.Data.IParameter[0]);
return federationDS;
}