how to sum of some rows in dataTable using footercallback

Question

I am using data Table. I want to sum of some columns and i want to show in the bottom of the report. I search many things. Then i found the new footer callback function in data Table. I used that. But still my output is not yet ready..

My code as follows,

function Databind(Pdestroy) {debugger;
    var destroy = false;
    if (Pdestroy == "1")
        destroy = true;

    var oTable = $('.datatable').dataTable({
        "bJQueryUI": true,
        'bServerSide': true,
        "bDestroy": destroy,
        "iDisplayLength": 10,
        "sPaginationType": "full_numbers",
        'sAjaxSource': '<%= Url.Action("listcount", "Home") %>',
        "bFilter": true,
        "aoColumnDefs": [{ 'bSortable': false, 'aTargets': [0, 1, 2, 7, 8, 9, 10, 11]}],

        "fnRowCallback": function (nRow, aData, iDisplayIndex) {
            var oSettings = oTable.fnSettings();
            $("td:first", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
            return nRow;
        },
        "footerCallback": function (row, aData, start, end, iDisplayIndex) {
         var api = this.api(),
         data;

            // Remove the formatting to get integer data for summation
            var intVal = function (i) {
                return typeof i === 'string' ? i.replace(/[\$,]/g, '') * 1 : typeof i === 'number' ? i : 0;
            };

            // Total over all pages
            total = api.column(4)
            .data()
            .reduce(function (a, b) {
                return intVal(a) + intVal(b);
            });

            // Total over this page
            pageTotal = api.column(4, {
                page: 'current'
            })
            .data()
            .reduce(function (a, b) {
                return intVal(a) + intVal(b);
            }, 0);

            // Update footer
            $(api.column(5).footer()).html(
            '$' + pageTotal + ' ( $' + total + ' total)');
        }
});
}

Here Row is showing undefined.And also there is no error showing. But the output is not showing. I have attached the screenshot of output..

To get the values what should i do futher process?

Answer 1

Use the sum plugin instead of reinventing the wheel :)

jQuery.fn.dataTable.Api.register( 'sum()', function ( ) {
    return this.flatten().reduce( function ( a, b ) {
        if ( typeof a === 'string' ) {
            a = a.replace(/[^\d.-]/g, '') * 1;
        }
        if ( typeof b === 'string' ) {
            b = b.replace(/[^\d.-]/g, '') * 1;
        }
        return a + b;
    }, 0 );
} );

since you just want to update a certain footer column each time the table is redrawn, you only need a simple drawCallback :

drawCallback: function() {
   var api = this.api();

   // Total over all pages
   var total = api.column(4).data().sum();

   // Total over this page
   var pageTotal = api.column(4, {page:'current'}).data().sum();

   $(api.column(5).footer()).html('$' + pageTotal + ' ( $' + total + ' total)');
}

Answer 2

@davidkonrad answer is better than mine. I suggest to use his answer instead of mine for just sum purpose.

This code can be used for custom operations for example adding css etc.

1. You must have tfoot in your table.
2. You can use footer callback function fnFooterCallback in version 1.10.

How to use

"fnFooterCallback": function(nRow, aaData, iStart, iEnd, aiDisplay) {
  //when working with pagination if you want to sum all records present in the current visible page only then use below  if block
  var iDisplayLength = parseInt(iEnd) - parseInt(iStart);
  if (iStart != 0) {
    iStart = iStart - iDisplayLength;
    iEnd = aaData.length;
  }
  //columns start from 0, i took 1st column so the line --> aaData[aiDisplay[i]][1]
  var iTarget = 0;
  for (var i = iStart; i < iEnd; i++) {
    iTarget += aaData[aiDisplay[i]][1] * 1; // because you get string in aaData[aiDisplay[i]][1] so multiplying with 1 gives number 
  }
  // Modifying the footer row
  var nCells = nRow.getElementsByTagName('th');
  nCells[1].innerHTML = parseInt(iTarget);
}