How to bind complex object as datasource in PivotGrid
This KB document explains how to render the PivotGrid control with complex object as data source.
Solution:
You can render the PivotGrid control with complex object, which is in relational data source.
Step 1:
Define the dataSource. The following array “pivot_dataset” contains sample data source.
var pivot_dataset = [
{ Amount: 100, Country: "Canada", Date: "FY 2005", Product: { Name: "Bike", Number: 12 }, Quantity: 2, State: "Alberta" },
{ Amount: 200, Country: "Canada", Date: "FY 2006", Product: { Name: "Van", Number: 13 }, Quantity: 3, State: "British Columbia" },
{ Amount: 100, Country: "Canada", Date: "FY 2007", Product: { Name: "Van", Number: 14 }, Quantity: 1, State: "Quebec" },
{ Amount: 200, Country: "France", Date: "FY 2005", Product: { Name: "Bike", Number: 32 }, Quantity: 2, State: "Charente-Maritime" },
{ Amount: 250, Country: "France", Date: "FY 2006", Product: { Name: "Van", Number: 11 }, Quantity: 4, State: "Essonne" }
];
Step 2:
Map the complex object into separate field names by splitting the same in the “load” event. The following code snippet demonstrates this.
JavaScript
function onLoad(args) {
for (var i = 0; i < pivot_dataset.length; i++) {
if (typeof (pivot_dataset[i]["Product"]) == "object") {
pivot_dataset[i]["Product Name"] = pivot_dataset[i]["Product"]["Name"];
pivot_dataset[i]["Product Number"] = pivot_dataset[i]["Product"]["Number"];
}
}
args.model.dataSource.data = pivot_dataset;
}
Step 3:
Bind the report as your platform needs.
JavaScript
$("#PivotGrid1").ejPivotGrid({
dataSource: {
// Data source bound to the PivotGrid control.
data: pivot_dataset,
// Required fields in row, column, value, and filter areas of the PivotGrid control.
// Rows
columns: [
{
fieldName: "Product Name",
fieldCaption: "Product Name"
},
{
fieldName: "Product Number",
fieldCaption: "Product Number"
}
]
// Values
}, load: "onLoad"
});
ASP
<ej:PivotGrid ID="PivotGrid1" runat="server" ClientIDMode="Static"> <DataSource> <%-- Rows --%> <Columns> <ej:Field FieldName="Product Name" FieldCaption="Product Name"></ej:Field> <ej:Field FieldName="Product Number" FieldCaption="Product Number"></ej:Field> </Columns> <%-- Values --%> </DataSource> <ClientSideEvents Load="onLoad" /> </ej:PivotGrid>
MVC
@Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Columns(columns => { columns.FieldName("Product Name").FieldCaption("Product Name").Add(); columns.FieldName("Product Number").FieldCaption("Product Number").Add(); }))
