How to make calculated column

[Zhen Liu]

I have a grid that needs to have a total price column which is calculated from quantity and unit price columns user typed in. How can I do this?

I also need a grand total on the bottom of the grid.

[Mani Sankar Durai]

Hi Zhen, 

Thanks for contacting Syncfusion support. 

We have analyzed your query and we have prepared a sample based on your requirement that can be downloaded from the below link. 

Link: http://www.syncfusion.com/downloads/support/forum/130960/ze/Sample2142466792 

In this sample we have shown the total value for the last column based on sum of two column value using column template feature in grid. Also we have shown the summary for the template column using custom summary feature in grid.  

Refer the code example 

<ej-grid id="FlatGrid" datasource="ViewBag.data" allow-paging="true" show-summary="true" action-complete="complete"> 

    <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true" edit-mode="@(EditMode.Normal)"></e-edit-settings> 

    <e-toolbar-settings show-toolbar="true" toolbar-items="@(new List<string>() {"add","edit","delete","update","cancel" })"></e-toolbar-settings>   

    <e-columns> 

... 

         <e-column field="EmployeeID" header-text="Employee ID" text-align="Right"  width="75"></e-column> 

        <e-column field="Freight" header-text="Freight" format="{0:C}" width="75"></e-column> 

        <e-column field="Sum" header-text="Sum" template="<span>{{:EmployeeID + Freight }}</span>" allow-editing="false" text-align="Right" width="75"></e-column> 

    </e-columns> 

    <e-summary-rows> 

        <e-summary-row title="Total sum"> 

            <e-summary-columns> 

                <e-summary-column summary-type="Custom" display-column="Sum" custom-summary-value= "currency" /> 

            </e-summary-columns> 

        </e-summary-row> 

        

    </e-summary-rows> 

</ej-grid> 

 

<script> 

    var frightval, empval; 

    var totalval, sumval = []; 

    function currency() { 

        var gridObj = $("#FlatGrid").ejGrid("instance"); 

        sumval = []; 

        for (i = 0; i < gridObj.model.dataSource.length; i++) { 

            frightval = gridObj.model.dataSource[i].Freight; 

            empval = gridObj.model.dataSource[i].EmployeeID; 

            totalval = frightval + empval; 

            sumval.push(totalval); 

        } 

        var result = ej.sum(sumval); 

        return (result);                //it will return the total summary value for the template column. 

    } 

   function complete (args) { 

        if (args.requestType == "beginedit") {         // when editing the value in two columns we have changed the value at run time for the total value column(i.e template column) 

            row = args.row; 

            $(args.row.find("input")).bind('keypress keyup', function (e) { 

                if(e.keyCode >= 48 && e.keyCode <= 57 || e.keyCode == 8)  {    //you can prevent as per the user need where key code 37 is left arrow and 39 is right arrow.  

                    ... 

                    var value = columnA + columnB; 

                    row.find(".e-templatecell").text(value); 

                } 

            }); 

        } 

    } 

</script> 

 

Refer the documentation link. 

Link: 

https://help.syncfusion.com/aspnet-core/grid/summary#custom-summary 

API link: https://help.syncfusion.com/api/js/ejgrid#events:actioncomplete 

https://help.syncfusion.com/api/js/ejgrid#members:columns-template 

Please let us know if you need further assistance. 

Regards, 

Manisankar durai. 

[Zhen Liu]

Thanks Mani, the calculation on each row is working, although it doesn't apply the currency format properly. However the summary is always zero in my code. I guess it is because that I am using e-datamanager with the grid. Is there any way that I can get the summary to work with e-datamanager?

            <ej-grid id="FlatGrid"  show-summary="true" action-complete="complete" begin-edit="beginedit">

                <e-datamanager json="(IEnumerable<object>)ViewBag.datasource" update-url="/Adjustments/LineUpdate?adjustmentId=@Model.Id" insert-url="/Adjustments/LineInsert?adjustmentId=@Model.Id" remove-url="/Adjustments/LineDelete?adjustmentId=@Model.Id" adaptor="remoteSaveAdaptor" />

                <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true" edit-mode="@(EditMode.Normal)"></e-edit-settings>

                <e-toolbar-settings show-toolbar="true" toolbar-items="@(new List<string>() {"add","edit","delete","update","cancel" })"></e-toolbar-settings>

                <e-columns>

                    <e-column field="Id" is-primary-key="true" visible="false"></e-column>

                    <e-column field="AdjustmentId" visible="false"></e-column>

                    <e-column field="CurrencyId" visible="false"></e-column>

                    <e-column field="ProductCode" datasource="(IEnumerable<object>)ViewBag.productcodes" edit-type="@(EditingType.Dropdown)" header-text="Product" width="80"></e-column>

                    <e-column field="Piece" header-text="Piece" width="10"></e-column>

                    <e-column field="Volume" header-text="Volume" width="10"></e-column>

                    <e-column field="Weight" header-text="Weight" width="10"></e-column>

                    <e-column field="UnitCost" header-text="Price" format="{0:C2}" width="10"></e-column>

                    <e-column field="Total" header-text="Total Price" template="<span>{{:Volume * UnitCost }}</span>" format="{0:C2}" allow-editing="false" text-align="Right" width="10"></e-column> 

            </e-columns>

                <e-summary-rows>

                    <e-summary-row title="Total">

                        <e-summary-columns>

                            <e-summary-column summary-type="Custom" display-column="Total" format="{0:C2}" custom-summary-value="currency" />

                      </e-summary-columns>

                    </e-summary-row>

                </e-summary-rows> 

            </ej-grid>

[Mani Sankar Durai]

Hi Zhen, 

We have checked the provided code and we can set the summary value when using remoteSaveAdaptor by the following way. 

Refer the code example 

  ... 

    function currency() { 

        var gridObj = $("#FlatGrid").ejGrid("instance"); 

        sumval = []; 

        var data = this.model.dataSource.dataSource.json;  //helps to retrieve whole data when using remoteSaveAdaptor in grid. 

        for (i = 0; i < data.length; i++) { 

            frightval = data[i].Freight; 

            empval = data[i].EmployeeID; 

            totalval = frightval + empval; 

            sumval.push(totalval); 

        } 

        var result = ej.sum(sumval); 

        return (result); 

    } 

... 

To retrieve the whole data we can get it from JSON property of adaptor only when using remoteSaveAdpaptor in grid. 

We have also prepared a sample that can be downloaded from the below link. 

Link: http://www.syncfusion.com/downloads/support/forum/130960/ze/ASP.NETCoreGrid-2083036736 

Please let us know if you need further assistance. 

Regards, 

Manisanka Durai. 

[Zhen Liu]

This works, thank you very much!

[Mani Sankar Durai]

HI Zhen, 

We are happy to hear that your problem has been solved. 

Please let us know if you need further assistance. 

Regards, 

Manisankar Durai.