Totals Row

erixoltan

We have some standard database queries with a home-grown table control, and we are now giving our report designers the option to use a datatables table, which they are very excited about.

We have a request to be able to add at totals row at the bottom, either in the footer or as the first or last row in the table. I don't see this as a feature of datatables, but I can't imagine it would be that hard to add.

Is there a way to do this, or have others had success providing this sort of capability? Are there any established best practices? I can't imagine that this would be an unusual request.

Thanks,
Erik

kthorngren

See this example of how to place totals in the footer.

Kevin

erixoltan

Thanks, that looks awesome. Glad I asked!

erixoltan

I've mostly gotten this working, but I'm down to a single issue.

        this.data_table = $(inner_id).dataTable({
            data: data,
            columns: columns,
            footerCallback: function() {
                let sumdef, sumcol;
                let api = this.api();
                let int_val = function(i) {
                    if (typeof i === 'string') {
                        return i.replace(/[\$,]/g,'') * 1;
                    }
                    if (typeof i === 'number') {
                        return i;
                    }
                    return 0;
                };
                let reduction = function(a,b) {
                    return int_val(a) + int_val(b);
                };
                for (sumdef of sums) {
                    sumcol = sumdef.col;
                    let sumtype = sumdef.type;
                    let coltot = api.column(sumcol).data().reduce(reduction, 0);
                    let pagetot = api.column(sumcol, {page: 'current'}).data().reduce(reduction, 0);
                    let colcode = `${F.convert_value(pagetot, sumtype)}<br>${F.convert_value(coltot, sumtype)}`;
                    console.log(colcode);
                    $(api.column(sumcol).footer()).html(colcode);
                }
            },
        });

On line 25, the console.log statement shows that the correct value is going into the colcode variable. On line 26, nothing happens the the footer() method returns null. I see that the table tag has been assigned class="data_tables_wrapper no-footer", but I don't know how to give it a footer. I've searched the documentation but am coming up blank. Has anyone else faced this issue? The data table looks great except there is no totals row at the bottom.

tangerine

Does your HTML table have a tfoot tag?

kthorngren

Datatables doesn't add a footer. You need to add it in HTML or use Javascript or jQuery to programmatically add the footer before Datatables is initialized.

Kevin

erixoltan

The HTML code for the <table> tag is dynamically generated based on the column definitions. Then a Data Table is added to it when the data callback comes back from the server. Here is the starting code for the table.

<div id='card_17' class='alx_card col_6 height_6'>YTD Top 50 Products &nbsp; 
   <table id='data_table_17' class='display'>
      <thead></thead>
      <tbody><tr><th>Updating...</th></tr></tbody>
      <tfoot>
         <tr><th>Totals</th><th></th><th></th><th></th><th></th><th></th></tr>
      </tfoot>
   </table>
</div>

The Data Table itself looks great, but there is no footer row with my totals.

allan

I suspect we'll need a link to a test case showing the issue please. It sounds like DataTables might not be registering the footers in your table for some reason. Perhaps try generating empty th elements for the header as well.

Allan

erixoltan

Thanks, allan. As so often happens, when I was composing the test case to explain the problem in more detail, I figured it out and got the whole thing to work!

It turns out that after setting everything up correctly according to the advice I had received here, I was not disabling a line of code that we use in other cases, that removes the innerHTML before repopulating our other tables and charts. As a result, it was blowing away the <tfoot> tag which was preventing my footer work from doing everything. When I disabled that line of code for data tables, the footer suddenly worked!

I'm marking this as answered, and thanks to everyone who helped me.