Totals and aggregate functions.

[WsevoL0D]

Hi. I this particular case have a little difficulties implementing sum for numeric column and need some help.

Is there any way to use jsgrid to get sums of numeric columns and display them in the footer? I haven't found anything regarding aggregate funcs in examples aside from itemCount, which seem to be used only with pager(is it possible to use it without pager?)

As I see it now the easiest way to get sums is just count/sum relevant cells.And use onDataLoaded() for this operation. Do you think it is the best option?

[WsevoL0D]

This is how, I think, it can be implemented with jsGrid onDataLoaded:

onDataLoaded: function(args) {
                var rows = args.grid.data;              
                total_price = 0;            
                for (row in rows) 
                {   
                    curRow = rows[row];                 
                    total_price += curRow.price * (1 - curRow.discount/100) * curRow.qty;               
                };
                var $spanTotals = $('#gridTotals');             
                $spanTotals.html($("<div>", {id:'totalPrice', style:'text-align:right;'}).html('Total price: ' + total_price));
            },

The problem with this approach: #gridTotals section is bounded to column rigidly, and it's not looking good if I have dynamic view (different number of columns can be displayed before and after price column).

It would be nice to have some footer template for columns maybe...

Upd: Also the downside is need to call this function onItemUpdated, deleted, created.

[tabalinas]

For now maybe a little bit better would be use onRefreshed callback, which is called each time gird rows are rendered. And in this handler you could append a row to the grid body (tbody) with the total. Anyway, I agree that it would be nice to have a built-in feature for that.

[WsevoL0D]

Finally I've made pretty neat totals following your suggestion and attaching it to tbody's last row. I've added css class to columns I want to find sum for and calculated column indexes, so my totals stay in place event if the number of columns have been changed.

However, I still have to redraw totals onItemInserted and onItemUpdated, though I'm calling this function from onRefreshed as well.

[soongsta]

Hi @SevaJmurov, would you mind sharing the code?

[WsevoL0D]

Hi, @soongsta, no problem.

The function below should be called from onRefreshed callbalck and for some reason I have to call it from onItemInserted and onItemUpdated, though as I understand this events should trigger grid refreshing. Not very beautiful code, but it does its job:

var redrawTotals = function(grid) {
    var rows = grid.grid.data;
    var $gridBody = $("#jsGrid .jsgrid-grid-body tbody");
    //get aggregate column indexes
    var $pointsColIndex = $gridBody.find("td.row_points").index();
    var $priceColIndex = $gridBody.find("td.row_price").index();
    //get column count
    var $lastColIndex = $gridBody.find("tr:last td:last").index();
    //remove 'old' totals
    if ($("#jsGrid .jsgrid-grid-body tbody #totals")){
        $("#jsGrid .jsgrid-grid-body tbody #totals").remove();
    }
    //drawing totals row
    var $totalsRow = $('<tr>',{id:'totals'});
    //append spacer cell before aggregates
    $totalsRow.append($('<td>',{colspan:$pointsColIndex-1,style:'border-width:0px;background-color:#f9f9f9;'}));
    //label "totals"
    $totalsRow.append($('<td>',{style:"font-weight:bold;text-align:right;background-color:#F3F8FE;"}).html('Totals:'));   
    //count totals  
    total_points = 0;
    total_price = 0;            
    for (row in rows) 
    {   
        curRow = rows[row];
        //I have to recalculate computed columns again, because I can't reference them(!?)  
        total_price += curRow.price * (1 - curRow.discount/100) * curRow.qty;
        total_points += curRow.points * curRow.qty;
    };
    //append total points cell
    $totalsRow.append($('<td>',{id:"total_points",style:"font-weight:bold;text-align:right;background-color:#F3F8FE;"}).html(numberWithSpaces(total_points)));
    //append total price cell
    $totalsRow.append($('<td>',{id:"total_price",style:"font-weight:bold;text-align:right;background-color:#F3F8FE;"}).html(numberWithSpaces(total_price)));
    //if price column is not last in table append spacer cell after aggregates
    if ($lastColIndex > $priceColIndex){
        $totalsRow.append($('<td>',{colspan:$lastColIndex - $priceColIndex,style:'border-width:0px;background-color:#f9f9f9;'}));   
    }
    //add totals row after table's last row  
    $gridBody.find("tr:last").after($totalsRow.prop('outerHTML'));
 }

also I've added css: "row_price" and css:"row_points" to corresponding columns to 'inform' redraw function of aggregate columns location. numberWithspaces is just for beautiful numbers.

[moraneden]

grouping will be really appreciated !

[Raifen]

@SevaJmurov What contains a variable "grid" that enters to your function? When I inserted "$("#myGrid") I get an error "TypeError: grid is undefined"

thank you for response

[tabalinas]

redrawTotals is a function provided to onRefreshed callback. This callback accepts an argument that has a field grid which is an instance of the grid http://js-grid.com/docs/#onrefreshed. Thus you could see like the following:

$("#grid").jsGrid({
  onRefreshed: refreshTotal(args) {
    var items = args.grid.option("data");
  }
});

[mapaul3511]

Hi @SevaJmurov

Could you please give a more complete sample of the usage of you redrawTotals function? I really need it on my job, thank you so much.

[priyarathi]

Hi, will you please provide sample where you are attaching summary row in footer. Does it work while inserting too?

Thanks!

[tabalinas]

@priyarathi, look at this fiddle: https://jsfiddle.net/tabalinas/6qru8gne/

In this example we show total sum:

  onRefreshed: function(args) {
      var items = args.grid.option("data");
      var total = { Name: "Total", "Sum": 0, IsTotal: true };

      items.forEach(function(item) {
          total.Sum += item.Sum;
      });

      var $totalRow = $("<tr>").addClass("total-row");
      args.grid._renderCells($totalRow, total);

      args.grid._content.append($totalRow);
  },

This code is using _renderCells for simplicity and, but for custom total row it would make sense to create all necessary <td>s with code. The drawback of using _renderCells, is necessity to tweak itemTemplate of control field, not to show control buttons for total row:

  itemTemplate: function(_, item) {
        if(item.IsTotal)
            return "";
        return jsGrid.fields.control.prototype.itemTemplate.apply(this, arguments);  
  }

[shafiq1675]

Here is my customize Plz give your kind feedback. basically where is need to update.

onRefreshed: function (args) { var items = args.grid.option("data"); var total = { "NoOfCoveredUpazila": 0, "FundforForum": 0, "TotalPOContribution": 0, "RemainingPOContribution": 0, };

            items.forEach(function (item) {
                total.NoOfCoveredUpazila += item.NoOfCoveredUpazila;
                total.FundforForum += item.FundforForum;
                total.TotalPOContribution += item.TotalPOContribution;
                total.RemainingPOContribution += item.RemainingPOContribution;
            });                

            args.grid._content.append("<tr><td colspan=\"2\" class=\"jsgrid-cell jsgrid-align-right\" style=\"width: 80px;\"><strong>Total</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.NoOfCoveredUpazila + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.FundforForum + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.TotalPOContribution + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.RemainingPOContribution + "</strong></td></tr>");

        },

[sankarkumar23]

Here is my customize Plz give your kind feedback. basically where is need to update.

onRefreshed: function (args) { var items = args.grid.option("data"); var total = { "NoOfCoveredUpazila": 0, "FundforForum": 0, "TotalPOContribution": 0, "RemainingPOContribution": 0, };

            items.forEach(function (item) {
                total.NoOfCoveredUpazila += item.NoOfCoveredUpazila;
                total.FundforForum += item.FundforForum;
                total.TotalPOContribution += item.TotalPOContribution;
                total.RemainingPOContribution += item.RemainingPOContribution;
            });                

            args.grid._content.append("<tr><td colspan=\"2\" class=\"jsgrid-cell jsgrid-align-right\" style=\"width: 80px;\"><strong>Total</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.NoOfCoveredUpazila + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.FundforForum + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.TotalPOContribution + "</strong></td>"
                + "<td class=\"jsgrid-cell jsgrid-align-right\"><strong>" + total.RemainingPOContribution + "</strong></td></tr>");

        },

Awesome.. its working great.