Data data is summarized by filtered data.

Question

I am using a datatable jquery plugin on my site. Everything works well.

However, I am trying to improve the table using a script column filter, and then I want to summarize the data in the footer. I can make the filter work correctly.

The example in datatables for summing data works only on the data page or the entire data set.

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

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

This obviously returns a filtered data object. However, as soon as I have it, I don’t know where to start adding data

At the moment, my datatable script (short for message) is as follows:

 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 shown above, that displays the total number of filters per page relative to the overall table (all data is not just filtered)

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

thanks

Answer 1

When you run ._('td:nth-child(4)', {"filter": "applied"}) you get an array of the column values ​​returned to you. Therefore, if your table looks like this after filtering:

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

... then run the following command

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

... will give you col4 = [154, 109] . From there, you simply move col4 to summarize its values, and manually paste the result into the footer line in the appropriate place.

Answer 2

In data tables 1.10, you can use the column method in api. Remember that it exists only if you call .DataTable instead of .dataTable. This worked for an hour because I was porting the old datatables code.

The example below creates a data table, and then links the function that is performed when recounting the footer. When the footer is recalculated, the function searches for the column in the table that contains the value. He then summarizes the values ​​in this column for:

• The data displayed in the column of the current data page.
• All data in a table in a column.
• The data in the column that exists in the rows that match the filter criteria.

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); } });