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.

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

<ejs-pivotview id="PivotGrid" height="300" allowCalculatedField="true" showFieldList="true">
    <e-datasource data="@ViewBag.data" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-calculatedfieldsettings>
            <e-field name="Total" formula="@totalPrice"></e-field>
        </e-calculatedfieldsettings>
    </e-datasource>
</ejs-pivotview>
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.

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

<ejs-button id="calculated-field-btn" content="Calculated Field" isPrimary="true"></ejs-button>
<ejs-pivotview id="PivotGrid" height="300" allowCalculatedField="true">
    <e-datasource data="@ViewBag.data" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Production Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-calculatedfieldsettings>
            <e-field name="Total" formula="@totalPrice"></e-field>
        </e-calculatedfieldsettings>
    </e-datasource>
</ejs-pivotview>

<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();
}