DataTables.net table column sum in footer

Question

I'm having issues with a tiny detail in inserting the sum value of each column, with class "sum", into its footer.

The code (more or less taken straight from DataTables.net) goes:

var table = $('#example').DataTable();
        table.columns('.sum').each(function (el) {
            var sum = table
                .column(el)
                .data()
                .reduce(function (a, b) {
                    return parseInt(a, 10) + parseInt(b, 10);
                });
            $(el).html('Sum: ' + sum);
        });

"sum" has the correct value but is somehow not inserted into the footer! I.e. its -element shows up empty.

Note that the snippet below works fine, but I want to sum each column with class sum.

var table = $('#example').DataTable();
var column = table.column(4);

$(column.footer()).html(
    column.data().reduce(function (a, b) {
        return parseInt(a, 10) + parseInt(b, 10);
    })
);

////////////////////////////////////////////////////////////////////////////////////

EDIT - Workaround:

I moved the code to where the DataTable is initialized and went with footerCallback instead. See below code:

"footerCallback": function (row, data, start, end, display) {
                    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 this page
                    pageTotal = api
                        .column('.sum', { page: 'current' })
                        .data()
                        .reduce(function (a, b) {
                            return intVal(a) + intVal(b);
                        }, 0);

                    // Update footer
                    $(api.column('.sum').footer()).html(pageTotal);
                }

Somehow now the value is inserted into the right tfoot element. Still no idea why it wouldn't work in the first place (but as mentioned in comment order of including JS-files could have had to do with some of it).

Now I just have to figure out how to loop multiple columns with class="sum"...

Solution 1

Your table manipulation code needs to be executed only when DataTable is initialized, (see initComplete property).

Also make sure you have <tfoot></tfoot> block defined in your <table> otherwise there would be no footer.

Otherwise you were very close to the solution, please see below the corrected code:

var table = $('#ticketTable').DataTable({
    'initComplete': function (settings, json){
        this.api().columns('.sum').every(function(){
            var column = this;

            var sum = column
                .data()
                .reduce(function (a, b) { 
                   a = parseInt(a, 10);
                   if(isNaN(a)){ a = 0; }                   

                   b = parseInt(b, 10);
                   if(isNaN(b)){ b = 0; }

                   return a + b;
                });

            $(column.footer()).html('Sum: ' + sum);
        });
    }
});

See this JSFiddle for an example.

UPDATE

There is also footerCallback property that lets you defined footer display callback function which will be called on every "draw" event.

The difference between initComplete and footerCallback is that initComplete is called once and footerCallback on every "draw" event.

If you're displaying the sum for the whole table, initComplete should suffice. Otherwise if you require to show in the footer data relevant for current page only (as in Footer callback example), use footerCallback instead.

Solution 2

Mix. Empty replaced to 0

"initComplete": function (settings, json) {
        this.api().columns('.sum').every(function () {
            var column = this;

            var intVal = function (i) {
                return typeof i === 'string' ?
                i.replace(/[\$,]/g, '') * 1 :
                    typeof i === 'number' ?
                        i : 0;
            };

            var sum = column
                .data()
                .reduce(function (a, b) {
                    return intVal(a) + intVal(b);
                });

            $(column.footer()).html('Sum: ' + sum);
        });
    }

Comments

[skepnaden]

'initComplete' ;) I was so close. Thanks for the answer!

[Viktor Bogutskii]

Need to update. This solution does not work with blank columns.

[Gyrocode.com]

@Siteogra, see this jsFiddle for an example.

[Freddy Sidauruk]

@Gyrocode.com doesn't work for me, is it support all version datatables !

[Gyrocode.com]

@FreddySidauruk, no, only 1.10 and newer.

[Freddy Sidauruk]

@Gyrocode.com i use datatables server side stole tutorial from here ahmed-samy.com/… already googling nothing works

[Gyrocode.com]

@FreddySidauruk, the tutorial uses 1.9 but newer 1.10 has backward support for 1.9 option names so you can use the newest version with no problem. If you have a question I recommend starting a new one on StackOverflow instead of using comments to existing question.

[Freddy Sidauruk]

@Gyrocode.com i did change all resource with cdn but doesn't work, am i wrong to comment to question which already solved ?

[Angel]

What about use the sum footer with reording columns?? When you use $( api.column( 0 ).footer() ).html('= '+sum); you are written on a determinate index column. But if you make a reorder column, the column index will not match with te aim column....

[Gyrocode.com]

@Angel, column index doesn't change even after reordering.

[Angel]

@Gyrocode.com, not in my case. When I apply the change footer in the footerCallback, my index column changes -- suma = api.column( 0, { filter: 'applied'} ).data().reduce( function (a, b) { return intVal(a) + intVal(b); }, 0 ); $( api.column( 0 ).footer() ).html('= '+suma); -- The index 0 is not the original 0 index :( It could be a configuration error in the configuration?

[Angel]

@Gyrocode.com, have you been able to discover the index error? See the problem is the filter applied when changes!