How can I add a subtotal and a grandtotal to my table using DataTables “jQuery Plug-in?”

malhayek

I am trying to use Datatables plug-in to display data for a report.

I like to add 2 footer rows to my table, the first one for a subtotal "totals from the current page" and another row for Granttotal "Totals from all the pages"

I followed this example in the docs (i.e. https://datatables.net/examples/advanced_init/footer_callback.html) to attempt to add the rows, but for some reason the footer is not showing up.

I populate the datatable on the fly using Ajax Request after the DataTable is initilized. Below is my current code.

Here is my Html markup

Here is my JavaScript code

$(function(e) {

$(window).load(function (e) {

    $('#reportTable').DataTable({
        pageLength: 50,
        lengthMenu: [10, 25, 50, 75, 100, 250, 500, 1000],
        dom: 'Bfrtip',
            buttons: [
                'copy', 'csv', 'excel', 'pdf', 'print'
            ],
        order: [   [ 2, 'desc' ]  ],
        columns: [
            { data: 'chain_name', title: 'Chain Name', width: '20%'},
            { data: 'store_id' , title: 'Store Number' },
            { data: 'completed', title: 'Total Completed' },
            { data: 'initial_quota', title: 'Target To Complete' },
            { data: 'total_callable', title: 'Total In Queue' },
            { data: 'current_status', title: 'Current Status' },
            { data: 'wgtstamp', title: 'Weight' }
        ]
        ,
        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 all pages
            var completedSubTotal = api
                .column(2)
                .data()
                .reduce(function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

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

            // Update footer with a subtotal
            $(api.column(2).footer()).html(completedSubTotal);

            // Update footer with a grand total
            $(api.column(2).footer()).html(completedGrandTotal);
        }


    });
});


$('input:search').on('keyup', function () {
    $('#reportTable').DataTable().search(this.value).draw();
});

$('#CampaignMenu').change(function(e) {

    $('#reportTable').DataTable().clear().draw();
    var campId = $(this).val();

    if (campId != '0') {

        $.ajax({
            type: 'POST',
            url: '@Url.RouteUrl("AccountQuotaDashboard.GetReportData")',
            data: { 'campaign_id': campId },
            dataType: 'json',
            success: function (json) {

                if (json && !$.isEmptyObject(json)) {

                    var table = $('#reportTable').dataTable().fnAddData(json);

                }

            }
        });

    }

});

});

How can I correctly add 2 footer row to my data table?

allan

Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.

Information on how to create a test page], if you can't provide a link to your own page can be found here.

Thanks,
Allan

malhayek

allan,
sorry it took me a while to create a page so you can see it. Please have a look at the following URL which should give you a closer look on what is not working
http://live.datatables.net/yubuyewi/2/

As you can see there is not footer rows. and I am expecting to see 2 rows added to the footer the first for the sub total and the second for the grand total

allan

There are no footer cells in your HTML table. At the moment, DataTables doesn't have an option to create elements in the table footer, so you would either need to put them into the HTML or add them into the DOM prior to the table's initialisation.

I realise that this lack of this feature can be a bit annoying - it is something that will be addressed in the next major release of DataTables.

Allan

malhayek

allan,
thank you for the help. Indeed adding html tags into the tfoot I was able to see one footer. But how can I see 2 row? a row for the sub total and another for the grant total?

I updated the fiddler http://live.datatables.net/yubuyewi/3/

Also, how can I calculate the average of of column(6) ?

malhayek

Allan,
I have also tried to use your api to sum the column and average the column like this

            footerCallback: function () {

                var api = this.api();

                var table = api.table();

                api.column('.pageTotal', {page:'current'}).every(function () {

                    $(this.footer(0)).html(this.data().sum());
                });

                api.column('.grandTotal').every(function () {

                    $(this.footer(1)).html(this.data().sum());
                });
         });

Then my table looks like this

<table id="reportTable" class="table table-striped " cellspacing="0" style="width: 100%;">
    <thead></thead>
    <tbody></tbody>
    <tfoot>
        <tr>
            <td></td>
            <td class="pageTotal"></td>
            <td class="pageTotal"></td>
            <td class="pageTotal"></td>
            <td class="pageTotal"></td>
            <td></td>
            <td class="pageTotal"></td>
            <td></td>
            <td class="pageTotal"></td>
        </tr>
        <tr>
            <td></td>
            <td class="grandTotal"></td>
            <td class="grandTotal"></td>
            <td class="grandTotal"></td>
            <td class="grandTotal"></td>
            <td></td>
            <td class="grandTotal"></td>
            <td></td>
            <td class="grandTotal"></td>
        </tr>
    </tfoot>
</table>

But this isn't giving me any values in the footers.