Search results

Calculated Field

Allows user to insert or add a new calculated field based on the available fields from the bound data source using basic arithmetic operators.

Calculated field can be included in pivot grid using the calculatedFieldsSettings property through code behind. The setting required for calculate field feature at code behind are:

  • name: It allows to indicate the given calculated field with unique name.
  • formula: It allows to set the formula base on the given data source.

Or else, calculated fields can be added at run time through the built-in dialog by just setting the allowCalculatedField property to true in Pivot Grid. You can see a button enabled in Field List UI to invoke the calculated field dialog.

The calculated field is applicable only for value fields.

razor
calculatedfield.cs
@{var amount = "\"" + "Sum(Amount)" + "\"";}
@{var sold = "\"" + "Sum(Sold)" + "\"";}
@{ var totalPrice = amount + "+" + sold;}

@Html.EJS().PivotView("PivotGrid").Height(300).DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).ExpandAll(false).EnableSorting(true)
 .FormatSettings(formatsettings =>
 {
     formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
 }).Rows(rows =>
 {
     rows.Name("Country").Add(); rows.Name("Products").Add();
 }).Columns(columns =>
 {
     columns.Name("Year").Caption("Production Year").Add(); columns.Name("Quarter").Add();
 }).Values(values =>
 {
     values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
 }).CalculatedFieldSettings(calculatedfieldsettings =>
 {
     calculatedfieldsettings.Name("Total").Formula(totalPrice).Add();
 })).AllowCalculatedField(true).ShowFieldList(true).Render()
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

Meanwhile, you can also display the calculated field dialog independently through other means. For example, you can invoke the dialog on an external button click which is shown in the below code sample.

razor
calculatedfield.cs
@{var amount = "\"" + "Sum(Amount)" + "\"";}
   @{var sold = "\"" + "Sum(Sold)" + "\"";}
   @{ var totalPrice = amount + "+" + sold;}

   @Html.EJS().Button("calculated-field-btn").Content("Calculated Field").IsPrimary(true).Render()
   @Html.EJS().PivotView("PivotGrid").Height("300").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).ExpandAll(false).EnableSorting(true)
    .FormatSettings(formatsettings => 
    { 
        formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add(); 
    }).Rows(rows => 
    { 
        rows.Name("Country").Add(); rows.Name("Products").Add(); 
    }).Columns(columns => 
    { 
        columns.Name("Year").Caption("Production Year").Add(); columns.Name("Quarter").Add(); 
    }).Values(values => 
    { 
        values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add(); 
    }).CalculatedFieldSettings(calculatedfieldsettings =>
    {
        calculatedfieldsettings.Name("Total").Formula(totalPrice).Add();
    })).AllowCalculatedField(true).Render()

    <script>
        document.getElementById("calculated-field-btn").addEventListener('click', function () {
            var pivotGridObj = document.getElementById("PivotGrid").ej2_instances[0];
            pivotGridObj.calculatedFieldModule.createCalculatedFieldDialog();
        });
    </script>
public IActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}