Calculated Column

[Torbian Linz]

Hi,

in my Grid I have 2 columns: price and amount. Now I want to add an additional column with the total, price * amount. Since the user is supposed to change the amount, the total has to be updated after edit.

How do I add a calculated column in a simple grid?

Kind Regards,

Torbian Linz

[Prasanna Kumar Viswanathan]

Hi Torbian,

Thanks for using Syncfusion Products.

Your requirement has been achieved by the queryCellInfo event in ejGrid. This event will be triggered each time when a request is made to access particular cell information, element and data. Using this event, we can be able to get the column headerText in arguments. Using the condition and text Jquery property we updated the value in another column.

Please find the below code snippet :

@(Html.EJ().Grid<Sample118577.OrdersView>("FlatGrid")

.Datasource((IEnumerable<object>)ViewBag.datasource)

.AllowScrolling()

.AllowSorting() /*Sorting Enabled*/

.AllowPaging() /*Paging Enabled*/

.EditSettings(edit => { edit.AllowAdding().AllowDeleting().AllowEditing(); })

.Columns(col =>

{

col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(75).Add();

col.Field("CustomerID").HeaderText("Customer ID").Width(80).Add();

col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(TextAlign.Right).Width(75).Add();

col.Field("Freight").HeaderText("Freight").TextAlign(TextAlign.Right).Width(75).Format("{0:C}").Add();

col.HeaderText("Total").TextAlign(TextAlign.Right).Width(75).Add();

})

.ClientSideEvents(eve => { eve.QueryCellInfo("calculate"); })

)

<script type="text/javascript">

function calculate(args) {

if (args.column.headerText === "Total")

{

$(args.cell).text(args.data.OrderID + args.data.EmployeeID);

}

}

</script>

For your convenience we have created a sample and sample can be downloaded from the below link :

Sample Link : http://www.syncfusion.com/downloads/support/forum/118577/Sample1185771851033778.zip

Please get back to us if you have any further assistance,

Regards,

Prasanna Kumar N.S.V

[Torbian Linz]

Okay, great! Thanks so far.

Now the column is calculated correctly, but ignores any Format I give. I set the Format in the creation of the Grid and tried it in the mentioned context.

How can I set the format correctle?

Kind Regards

[Prasanna Kumar Viswanathan]

Hi Torbian,

Thanks for the update.

Your requirement has been achieved by the format api in columns of ejGrid. We suggest you to use format api in columns. Please find the below code snippet:

.Columns(col =>

{

col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(75).Add();

col.Field("CustomerID").HeaderText("Customer ID").Width(80).Add();

col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(TextAlign.Right).Width(75).Add();

col.Field("Freight").HeaderText("Freight").TextAlign(TextAlign.Right).Width(75).Format("{0:C}").Add();

col.HeaderText("Total").TextAlign(TextAlign.Right).Format("{0:n2}").Width(75).Add();

})

For your kind information, Please refer the below for the various types of formats

Link : http://mvc.syncfusion.com/demos/web/grid/columnformatting

For your convenience we have created a sample and sample can be downloaded from the below link

Sample Link : http://www.syncfusion.com/downloads/support/forum/118577/Sample118577-1948439406.zip

Regards,

Prasanna Kumar N.S.V

[Torbian Linz]

Hi Prasanna,

simply put: Nope.

As mentioned in my answer before, that's exacly what I tried. Set the format of the column. It simply doesn't work. I even used your example without success.

Although I noticed a difference in the Versions used:

in my web.config I have

<add assembly="Syncfusion.EJ, Version=12.4450.0.24, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />

<add assembly="Syncfusion.EJ.Mvc,Version=12.4500.0.24, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />

whereas your versions are:

<add assembly="Syncfusion.EJ, Version=12.4400.0.34, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />

<add assembly="Syncfusion.EJ.Mvc, Version=12.4400.0.34, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />

That's the only thing I had to change when trying your example.

Kind regards

[Prasanna Kumar Viswanathan]

Hi Torbian,

Sorry for the inconvenience caused,

We have created a sample in 12.4.0.24 versions and use the format api in columns of ejGrid. we able to get the format in the total column.

For your convenience we have attached a sample using 12.4.0.24 version and the same can be downloaded from the below link

Sample Link : http://www.syncfusion.com/downloads/support/forum/118577/Sample118577608985917.zip

If you still facing the same issue, Please attach a screenshot of the code snippet or Is it possible to provide a sample for us? It will helpful to provide a better solution

Please get back to us if you have any further assistance on this,

Regards,

Prasanna Kumar N.S.V

[Torbian Linz]

Hi Prasanna,

again no luck. The Project is imported ok, but I still have no Format in the calculated column. I tried it with your Project and a fresh install of the most recent stable version of Syncfusion for MVC (12.4.0.24). No Updates are available in the dashboard.

I tried Changing the format to something like {0:c2} so that the change would be most prominent.

Kind Regards

[Prasanna Kumar Viswanathan]

Hi Torbian,

Thanks for the update.

We are able to reproduce the formatting problem while using currency format like {0:C2}. We have achieved your requirement by using the ejGrid public method formatting. As we are rendering the total column values by using text jQuery API, we cannot able to set the currency format by using format API in columns. So we suggest you to use formatting method in queryCellInfo event.

Please find the below code snippet :

.Columns(col =>

{

col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(75).Add();

col.Field("CustomerID").HeaderText("Customer ID").Width(80).Add();

col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(TextAlign.Right).Width(75).Add();

col.Field("Freight").HeaderText("Freight").TextAlign(TextAlign.Right).Width(75).Format("{0:C}").Add();

col.HeaderText("Total").TextAlign(TextAlign.Right).Width(75).Add();

})

.ClientSideEvents(eve => { eve.QueryCellInfo("calculate"); })

)

<script type="text/javascript">

function calculate(args) {

if (args.column.headerText === "Total")

{

var obj = $("#FlatGrid").data('ejGrid')

var value=obj.formatting('{0:c2}', args.data.OrderID + args.data.Freight)

$(args.cell).text(value);

}

}

</script>

For your convenience we have created a sample and sample can be downloaded from the below link

Sample Link : http://www.syncfusion.com/downloads/support/forum/118577/Sample118577-331051387.zip

Please get back to us if you have any further assistance on this,

Regards,

Prasanna Kumar N.S.V

[Torbian Linz]

Works like a charm. Thanks.

Unfortunately it ignores the locale setting of the grid. All static columns and the summary row are formatted using the given locale ("de-DE") but the calculated and manually formatted column doesn't use the locale.

Any hint on that?

Kind Regards

[Prasanna Kumar Viswanathan]

Hi Torbian,

Thanks for the update,

Your requirement have been achieved by using the ejGrid public method formatting. In formatting method we able to send the locale as a parameter. Using that we able to set the locale for the calculated column.

Please find the below code snippet :

function calculate(args) {

if (args.column.headerText === "Total")

{

var obj = $("#FlatGrid").data('ejGrid')

var value = obj.formatting('{0:c2}', args.data.OrderID + args.data.Freight, this.model.locale)

$(args.cell).text(value);

}

}

For your convenience we have created a sample and sample can be downloaded from the below link

Sample Link : http://www.syncfusion.com/downloads/support/forum/118577/Sample1185771284657682.zip

Please get back to us if you have any further assistance on this

Regards,

Prasanna Kumar N.S.V

[Torbian Linz]

Thanks, working great. Is there any documentation on the formatting function available? I found none.

Kind Regards

[Prasanna Kumar Viswanathan]

Hi Torbian,

Thanks for the update,

Query : “Is there any documentation on the formatting function available”

As we do not include the documentation for the formatting method in the online documentation link. So, we have created a UG documentation task for the above formatting method and it will be included in the online documentation link.

Please get back to us if you have further assistance on this,

Regards,

Prasanna Kumar N.S.V