Search results

Calculated Field in ASP.NET MVC Pivot Grid control

21 Oct 2021 / 1 minute to read

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
Copied to clipboard
@{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()
Copied to clipboard
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
Copied to clipboard
@{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>
Copied to clipboard
public IActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}