javascript - RowGrouping and Subtotal on Datatable

Question

I am using RowGrouping on Datatable. For each Group I need to do Subtotal on some of the columns (Encum, Paid, Balance) and Total on all of the rows for each Column (Encum, Paid, Balance). I am quite new to this Datatable and not able to achieve the result that I need. Here is the code I'm using. I need to display $value of 3 of those columns and also the subtotal at the header. Please help me out here, code in jsfiddle: jsfiddle.net/6r2pjbg8

var table = S$('#EncumbranceSummaryTable').DataTable({
        "columnDefs": [
            { "visible": false, "targets":0 }
        ],   
        "stateSave": false,
        "stateDuration": 60*60*24*365,
        "data" : POnbrsDetails,           
        "columns":[
             {"sTitle": "PO #", "data": "PO_Nbr" },
             {"sTitle": "FY", "data": "FY" },
             {"sTitle": "LN", "data": "LN" },
             {"sTitle": "F/O/A","data":"FOA"},
             {"sTitle": "Project ID", "data": "Proj_ID" },
             {"sTitle": "Srce Type", "data": "Srce_Type" },
             {"sTitle": "Encumbrance","data":"Encum_Amt"},
              {"sTitle": "Paid","data":"Paid"},
             {"sTitle": "Balance", "data": "Balance" }      
             
        ],
        "drawCallback": function ( settings ) {                
            var api = this.api();
            var rows = api.rows( {page:'current'} ).nodes();
            var last=null;

            var colonne = api.row(0).data().length;
            var totale = new Array();
            totale['Totale']= new Array();
            var groupid = -1;
            var subtotale = new Array();


            api.column(0, {page:'current'} ).data().each( function ( group, i ) {  
                debugger;
                if ( last !== group ) {
                    S$(rows).eq( i ).before(
                        '<tr class="group"><td colspan="5">'+group+'</td></tr>'
                    );

                    last = group;
                }                
                //sub total
                val = api.row(api.row($(rows).eq( i )).index()).data();      //current order index
                $.each(val,function(index2,val2){
                    debugger;
                    if (typeof subtotale[groupid] =='undefined'){                           
                        subtotale[groupid] = new Array();
                    }
                    if (typeof subtotale[groupid][index2] =='undefined'){                           
                        subtotale[groupid][index2] = 0;
                    }
                    if (typeof totale['Totale'][index2] =='undefined'){                           
                        totale['Totale'][index2] = 0;
                    }
                    
                  //  valore = Number(val2.replace('€',"").replace('.',"").replace(',',"."));
                    subtotale[groupid][index2] += 1;
                    totale['Totale'][index2] += 2;
                });
            } );

            $('tbody').find('.group').each(function (i,v) {
             
                var rowCount = $(this).nextUntil('.group').length;
                //$(this).find('td:first').append($('<span />', { 'class': 'rowCount-grid' }).append($('<b />', { 'text': ' ('+rowCount+')' })));
                var subtd = '';
                for (var a=0;a<3;a++)
                { 
                    subtd += '<td>'+ subtotale[i][a] +'</td>';
                }
              
                $(this).append(subtd);
            });

        }
    });

Answer 1

Does this work for you?

var POdata = [{
    "PO_Nbr": "3000202173",
    "FY": 2015,
    "LN": "1.1",
    "FOA": "2500/T7935002/T790225",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "6251",
    "Encum_Amt": 66033.62,
    "Paid": 36508.35,
    "Balance": 29525.27
}, {
    "PO_Nbr": "3000202173",
    "FY": 2015,
    "LN": "1.2",
    "FOA": "2600/T7935002/T790226",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "6251",
    "Encum_Amt": 212824.56,
    "Paid": 117665.45,
    "Balance": 95159.11
}, {
    "PO_Nbr": "3000143638",
    "FY": 2014,
    "LN": "1.1",
    "FOA": "2500/T7935002/T790225",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "6251",
    "Encum_Amt": 32350.84,
    "Paid": 32350.84,
    "Balance": 0
}, {
    "PO_Nbr": "3000143638",
    "FY": 2014,
    "LN": "1.2",
    "FOA": "2600/T7935002/T790226",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "6251",
    "Encum_Amt": 104265.84,
    "Paid": 104265.84,
    "Balance": 0
}, {
    "PO_Nbr": "3000079267",
    "FY": 2013,
    "LN": "1.1",
    "FOA": "2500/T7935002/T790225",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "9025",
    "Encum_Amt": 21339.91,
    "Paid": 21339.91,
    "Balance": 0
}, {
    "PO_Nbr": "3000079267",
    "FY": 2013,
    "LN": "1.2",
    "FOA": "2600/T7935002/T790226",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "9025",
    "Encum_Amt": 68778.06,
    "Paid": 68778.06,
    "Balance": 0
}, {
    "PO_Nbr": "3000038524",
    "FY": 2012,
    "LN": "1.1",
    "FOA": "2500/T7935002/T790225",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "9025",
    "Encum_Amt": 12752.54,
    "Paid": 12752.54,
    "Balance": 0
}, {
    "PO_Nbr": "3000038524",
    "FY": 2012,
    "LN": "1.2",
    "FOA": "2600/T7935002/T790226",
    "Proj_ID": "TLRR922B",
    "Srce_Type": "9025",
    "Encum_Amt": 41101.1,
    "Paid": 41101.1,
    "Balance": 0
}];

var table = $('#EncumbranceSummaryTable').DataTable({
    "data": POdata,
    "columns": [
        {
            "title": "PO #",
            "data": "PO_Nbr",
            "visible": false
        }, {
            "title": "FY",
            "data": "FY"
        }, {
            "title": "LN",
            "data": "LN",
            "sortable": false
        }, {
            "title": "F/O/A",
            "data": "FOA",
            "sortable": false
        }, {
            "title": "Project ID",
            "data": "Proj_ID",
            "sortable": false
        }, {
            "title": "Srce Type",
            "data": "Srce_Type",
            "sortable": false
        }, {
            "title": "Encumbrance",
            "data": "Encum_Amt",
            "sortable": false
        }, {
            "title": "Paid",
            "data": "Paid",
            "sortable": false
        }, {
            "title": "Balance",
            "data": "Balance",
            "sortable": false
        }
    ],
    "drawCallback": function (settings) {
        var api = this.api();
        var rows = api.rows({
            page: 'current'
        }).nodes();
        var last = null;
        api.column(0, {
            page: 'current'
        }).data().each(function (group, i) {
            if (last !== group) {
                $(rows).eq(i).before(
                $("<tr></tr>", {
                    "class": "group",
                    "data-id": group
                }).append($("<td></td>", {
                    "colspan": 5,
                    "class": "pocell",
                    "text": "PO # " + group
                })).append($("<td></td>", {
                    "id": "e" + group,
                    "class": "noCount",
                    "text": "0.00"
                })).append($("<td></td>", {
                    "id": "p" + group,
                    "class": "noCount",
                    "text": "0.00"
                })).append($("<td></td>", {
                    "id": "b" + group,
                    "class": "noCount",
                    "text": "0.00"
                })).prop('outerHTML'));
                last = group;
            }
            val = api.row(api.row($(rows).eq(i)).index()).data();
            $("#e" + val.PO_Nbr).text(parseFloat($("#e" + val.PO_Nbr).text()) + parseFloat(val.Encum_Amt));
            $("#p" + val.PO_Nbr).text(parseFloat($("#p" + val.PO_Nbr).text()) + parseFloat(val.Paid));
            $("#b" + val.PO_Nbr).text(parseFloat($("#b" + val.PO_Nbr).text()) + parseFloat(val.Balance));
        });
    },
    "footerCallback": function (row, data, start, end, display) {
        var api = this.api();
        $(api.column(6).footer()).html(
            api.column(6).data().reduce(function (a, b) {
                return parseFloat(a) + parseFloat(b);
            })
        );
        $(api.column(7).footer()).html(
            api.column(7).data().reduce(function (a, b) {
                return parseFloat(a) + parseFloat(b);
            })
        );
        $(api.column(8).footer()).html(
            api.column(8).data().reduce(function (a, b) {
                return parseFloat(a) + parseFloat(b);
            })
        );
    }
});

Working JSFiddle.