Hello,
I am using the MVC wrapper for the iggrid in my razor view and I am wondering if it is possible to create a custom column summary based on other column summaries. For example, say I have 3 columns: Price, Cost, and an unbound column called Margin. The Margin is just the Price minus the Cost all divided by the Price. I can get the individual row margins to calculate correctly simply with a formula in the unbound column but I can't figure out how to get the total margin for the entire dataset. All the examples of custom summaries I have seen only use the data from the column the custom summary is for. I need to get the totals from the Price and Cost columns in order to calculate my total Margin which I can't figure out if I can do.
thanks,
Andrew
Here is my relevant code:
<script type="text/javascript">
function CalcMargin(data) {
var i, l = length, margin= 0;
for (i = 0; i < l; i++) {
--Don't really know how to access other columns--
}
return margin;}
</script>
//summaries
features.Summaries().Type(OpType.Local).ColumnSettings(settings =>
{settings.ColumnSetting().ColumnKey("Price").SummaryOperands(builder =>
{builder.SummaryOperand().Active(true).Type(SummaryFunction.Sum).RowDisplayLabel("Total");});
settings.ColumnSetting().ColumnKey("Cost").SummaryOperands(builder =>
settings.ColumnSetting().ColumnKey("Margin").SummaryOperands(builder =>
{builder.SummaryOperand().Active(true).Type(SummaryFunction.Custom).RowDisplayLabel("Total").SummaryCalculator("$.proxy(CalcMargin, this)"); });
});
Hello Andrew,
Thank you for your patience!
A possible approach regarding your scenario is calculating margin in the controller and passing the result to the view. I have created and attached a sample implementing this solution.
To achieve the desired behavior I have added a margin property to the model and a method implementing its calculation:
public class Product
{
public int ProductId { get; set; }
public string Name { get; set; }
public double Cost { get; set; }
public double Price { get; set; }
public double Margin { get; set; }
public Product(string name)
this.Name = name;
public Product() {}
public void SetMargin()
this.Margin = (this.Price + this.Cost) / this.Price;
Then I have used a bound column for the margin in the view grid:
@(Html.Infragistics().Grid(Model).ID("grid1").Width("99%")
.AutoGenerateColumns(false)
.Columns(column =>
column.For(x => x.Name).Width("200px").HeaderText("Name");
column.For(x => x.ProductId).Width("260px").HeaderText("Id");
column.For( x=> x.Cost).Width("280px").HeaderText("Cost").DataType("number");
column.For(x => x.Price).Width("350px").DataType("number").HeaderText("Price");
column.For(x => x.Margin).Width("300px").HeaderText("Margin");
})
The whole working sample is attached for your reference.
There is also another possible approach for this by using unbound columns, however there currently seems to be an issue with the summary calculations for unbound numeric columns when the grid is defined through the MVC wrapper. I have investigated this issue, and I have asked our engineering stuff to examine this further. Its Development ID number is 150317. The next step will be for a developer to review my investigation and confirm my findings or to offer a fix, or other resolution.
You can also continue to send updates to this case at any time.
You can view the status of the development issue connected to this case by selecting the "Development Issues" tab when viewing this case on the web site.
It is possible however to have a calculated unbound numeric column at the moment by defining the grid in javascript. An html sample illustrating that is attached for your consideration.
If you have any further questions don’t hesitate to contact me again.
Attached is the sample presenting the MVC wrapper implementation.
I am glad you have been able to resolve this issue! Please feel free to contact us if any questions regarding our toolset arise in future.
Ahh, I see. That was the missing link that I couldn't put together. I was able to get it to work following the process you outlined above. Thanks for your help!
regards,
Rosco
Thank you for your reply!
I have created a sample regarding your query. In the current approach global helper variables are used in order to keep the values needed for the margin calculation. Custom functions calculating the cost and price sums are defined and used instead of the default built-in sum function. Those custom functions pass values to the helper variables and then the helper variables are used in the function which calculates the margin:
function PredefineAndSavePrice (data) {
var i, l = data.length, sum = 0;
elem = data[i];
sum += elem;
helper1 = sum;
return sum;
};
function PredefineAndSaveQuantity (data) {
helper2 = sum;
function CalculateMargin(data){
return (helper1 - helper2)/helper1;
Attached is the whole working sample for your reference.
Please let me know in this helps. If you have any further questions feel free to contact me.
Hello Dimka,
Thank you for your response, however, your solution isn't what I am after. I am already able to set an unbound column in my iggrid with a function that calculates the margin. That works great. The problem is that when I apply summaries to this column, I only get the standard COUNT, MIN, MAX, SUM, and AVG summary options. I need a custom Summary option called MARGIN. This custom summary option would be a calculation of the SUM summary option from the Price column minus the SUM summary option of the Cost column all divided by the SUM summary option from the Price column.
So for the example you have provided, it would be (155 - 559) / 155 = -2.6 (ouch, you're losing a lot of money on these items :) )
I am not able to figure out how to access the summary data from other columns for the Margin column's custom summary option. I hope this is more clear.
thanks again!