Search results

Calculated Field

Allows end user to create a new calculated field in the pivot table, based on available fields from the bound data source or using simple formula with basic arithmetic operators. It can be added at runtime through the built-in dialog, invoked from Field List UI. To do so, set the AllowCalculatedField property in EjsPivotView class to true in the pivot table. End user can now see a “CALCULATED FIELD” button enabled in Field List UI automatically, which on clicking will invoke the calculated field dialog and perform necessary operation.

Calculated field can also be included in the pivot table through code behind using the PivotViewCalculatedFieldsSettings class. The required properties to create a new calculate field are:

  • Name: It allows to indicate the calculated field with a unique name.
  • Formula: It allows to set the formula.

The calculated field is applicable only for value fields. Also, calculated field created through code behind will be automatically listed in the UI dialog as well.

@using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowFieldList="true" AllowCalculatedField="true">
         <PivotViewDataSourceSettings DataSource="@data">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
                <PivotViewValue Name="Total" Caption="Total Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
                <PivotViewFormatSetting Name="Total" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
             <PivotViewCalculatedFieldSettings>
                <PivotViewCalculatedFieldSetting Name="Total" Formula="@totalPrice"></PivotViewCalculatedFieldSetting>
            </PivotViewCalculatedFieldSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public string totalPrice = "\"" + "Sum(Amount)" + "\"" + "+" + "\"" + "Sum(Sold)" + "\"";
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

Meanwhile, user can also view calculated field dialog in UI by invoking CreateCalculatedFieldDialog method on an external button click which is shown in the below code sample.

    @using Syncfusion.EJ2.Blazor.PivotView
    @using Syncfusion.EJ2.Blazor.Buttons
    <EjsButton OnClick="@calc" IsPrimary="true">Calculated Field</EjsButton>
    <EjsPivotView TValue="ProductDetails" @ref="pivot" AllowCalculatedField="true">
         <PivotViewDataSourceSettings DataSource="@data">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
                <PivotViewFormatSetting Name="Total" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public EjsPivotView<ProductDetails> pivot;
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
        public void calc(Microsoft.AspNetCore.Components.Web.MouseEventArgs args)
        {
            this.pivot.CreateCalculatedFieldDialog();
        }
    }

output

output

Renaming the existing calculated field

Existing calculated field can be renamed only through the UI at runtime. To do so, open the calculated field dialog, select the target field and click “Edit” icon. User can now see the existing name getting displayed in the text box at the top of the dialog. Now, change the name based on user requirement and click “OK”.

output

output

Editing the existing calculated field formula

Existing calculated field formula can be edited only through the UI at runtime. To do so, open the calculated field dialog, select the target field and click “Edit” icon. User can now see the existing formula getting displayed in a multiline text box at the bottom of the dialog. Now, change the formula based on user requirement and click “OK”.

output

output

Reusing the existing formula in a new calculate field

While creating a new calculated field, if user wants to the add the formula of an existing calculated field, it can be done easily. To do so, simply drag-and-drop the existing calculated field to the “Formula” section.

output

output

output