datatables summarise filtered data

Question

I'm using datatable jquery plugin in my site. All working well.

However I'm trying to enhance the table by using the plugin script column filter and then I want to summarise the data in the footer. I can get the filter to work correctly.

An example in datatables for summarising the data only works on either the page of data or the entire dataset.

I found this thread: http://datatables.net/forums/discussion/2053/fnfootercallback-sum-column-after-filter/p1 looking for a similar solution. The author suggests the following function:

._('td:nth-child(4)', {"filter": "applied"})

This apparently returns an object of the filtered data. However once I have this I do not know where to start adding the data

At the moment my datatable script (shortened for the sake of the post) looks like this:

table.dataTable({...


    "fnFooterCallback": function ( nRow, aaData, iStart, iEnd, aiDisplay ) {

        /*
                     * Calculate the total sales for the table (ie inc. outside
                     * the pagination)
                     */
        var iTotalSales = 0;
        for ( var i=0 ; i<aaData.length ; i++ )
        {
            iTotalSales += aaData[i][2]*1;
        }

        /* Calculate the total sales on this page */
        var iPageSales = 0;
        for ( var i=iStart ; i<iEnd ; i++ )
        {
            iPageSales += aaData[ aiDisplay[i] ][2]*1;
        }

        /* Modify the footer row to match what we want */
        var secondRow = $(nRow).next()[0];
        var nCells = secondRow.getElementsByTagName('td');
        nCells[0].innerHTML = accounting.formatMoney(iPageSales, "£ ", 2) +
                ' Page Total ('+ accounting.formatMoney(iTotalSales, "£ ", 2) +' Total Sales)';
    }

})
        .columnFilter({
            aoColumns: [ { type: "date-range" },
                null,
                { type: "text"  },
                { type: "text"  },
                { type: "select"  },
                { type: "select"  }
            ]

        })
        ._('td:nth-child(4)', {"filter": "applied"});

I currently have a summary as above which displays the total filtered on the page against the total of the table (all data not just filtered)

I'm a jquery novice - I'm not sure where to start manipulating the object created in the final call

Thank you

Did you solve this? I'm trying to do it now... – Pitto Aug 19 '13 at 14:41 — Pitto, Aug 19 '13 at 14:41

score 4 · Accepted Answer · answered Jan 16 '14 at 16:29

4

When you run ._('td:nth-child(4)', {"filter": "applied"}) you get an array of that column's values returned to you. So if your table looks like this after being filtered:

col 1 | col 2 | col 3 | col 4
foo   | blah  |   $18 |   154
bar   | blech |    $2 |   109

...then the following command

var col4 = $('#mytable').dataTable()._('td:nth-child(4)', {"filter": "applied"})

...would give you col4 = [154, 109]. From there, you simply traverse col4 to sum its values, and manually insert the result into your footer row in the appropriate place.

answered Jan 16 '14 at 16:29

DiMono

3,308
2
19
40

Thank you for a great explanation - I put this part to one side in my project - I can add it now. Thanks again – Ray Jan 18 '14 at 08:41
No problem, glad to help. I actually came here looking to solve the same problem, saw your question, and that led me to the solution. – DiMono Jan 21 '14 at 19:46

score 2 · Answer 2 · answered May 06 '14 at 18:38

In data tables 1.10, you can use the columns method in the api. Beware that it only exists if you call .DataTable instead of .dataTable. That tripped em up for an hour because I was migrating old datatables code.

The sample below creates a data table, then binds a function that executes when the footer is being recalculated. When the footer is recalculated, the function searches for a column in the table that contains a cost. It then sums up the values in this column for:

The data displayed in the column on the current page of the datatable.
All the data in the table in the column.
The data in the column that exists on rows that meet the filter criteria.

So see the variable searchTotalData :)

var dataTable = $('#csvtable').DataTable({
    'footerCallback': function(row, data, start, end, display){
        var api = this.api();
        var intval = function(i){
            //Excuse this ugliness, it comes from the datatables sample
            return typeof i === 'string' ? 
                i.replace(/[\$,]/g, '')*1 :
                typeof i === 'number' ? 
                    i : 0;
        };

        var costColumnIndex = $('th').filter(function(i){return $(this).text() == 'Cost';}).first().index();
        var totalData= api.column(costColumnIndex).data();
        var total = totalData.reduce(function(a ,b){ return intval(a) + intval(b); }, 0) .toFixed(2);
        var pageTotalData = api.column(costColumnIndex, {page: 'current'}).data();
        var pageTotal = pageTotalData.reduce(function(a,b){return intval(a) + intval(b);}, 0).toFixed(2);
        var searchTotalData = api.column(costColumnIndex, {'filter': 'applied'}).data();
        var searchTotal = searchTotalData.reduce(function(a,b){return intval(a) + intval(b);}, 0).toFixed(2);

        $('#cost').html('Approximate page total $' + pageTotal + ', search total $' + searchTotal + ', totally total $' + total);
    }   
});

datatables summarise filtered data

2 Answers2