how to sum 2 cells and then total the column?

monkeyboy

Allan,

I have created a column which is the sum of 2 cells in the row.
When I total (footerCallback) the columns, the original 2 columns total just fine, but the rendered summed columns fail to total.
I tried to remove currency rendering from all my data, but that had no effect.

Any help would be greatly appreciated, as I have spent all day on this, and fail to understand why it does not work.

Javascript:

        var myTable = $("#myTable").DataTable( {
            dom: "t",
            "rowCallback": function( row, data, index ) 
            {
                if ( data.myDbTable.id == 6 ) 
                {
                    $("td", row).closest("tr").removeClass("even");
                    $("td", row).closest("tr").removeClass("odd");
                    $("td", row).closest("tr").css("background-color","blue");
                    $("td", row).closest("tr").css("color","white");
                    $("td", row).closest("tr").css("font-weight","bold");
                    //$("td", row).eq(1).css("font-weight","bold");
                }
            },
            "ordering": false,
            "serverSide": false,
            "paging": false,
            ajax:   {
                "url":  "myAjax.php",
                "type": "POST",
                data: function(d) {
                    d.param1="' . $param1 .'";
                    d.param2="'.$param2.'";
                    d.param3="'.$param3.'";
                }                                
            },
            columns: [
                { data: "myDbTable.id",     "sClass": "binCentered" },
                { data: "myDbTable.field1", "sClass": "binCentered" },
                { data: "myDbTable.field2", "sClass": "binCentered",
                    render: $.fn.dataTable.render.number( ",", ".", 0, "$" ) },
                { data: "myDbTable.field3", "sClass": "binCentered" ,
                    render: $.fn.dataTable.render.number( ",", ".", 0, "$" )},
                { data: "myDbTable.field4", "sClass": "binCentered" },
                { data:  null, "sClass": "binCentered", render: function (data, type, row) 
                    {
                        var numFormat = $.fn.dataTable.render.number( ",", ".", 0, "$" ).display;
                        var total = parseInt(data.myDbTable.field2) + parseInt(data.myDbTable.field3);
                        return numFormat(total);
                    }
                },

                { data:  null, "sClass": "binCentered", render: function (data, type, row) 
                    {
                        var numFormat = $.fn.dataTable.render.number( ",", ".", 0, "$" ).display;
                        var total = parseInt(data.myDbTable.field2) + parseInt(data.myDbTable.field3);
                        return numFormat(total);
                    }
                },
                { data: "myDbTable.field4", "sClass": "binCentered" },
                { data: "myDbTable.field5", "sClass": "binCentered" }
            ],
            order: [[0, "asc"]], // sort by hidden id column
            "columnDefs": [
                {
                    "targets": [ 0 ],
                    "visible": false  // Hide my ID column
                }
                ],
            buttons: [
                "excelHtml5"
            ],              

            "footerCallback": function ( row, data, start, end, display ) 
            {
                var api = this.api(), data;
                var numFormat = $.fn.dataTable.render.number( ",", ".", 0, "$" ).display;
    
                // 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 Column 2 over all pages
                total = api
                    .column( 2 )
                    .data()
                    .reduce( function (a, b) 
                    {
                        return intVal(a) + intVal(b);
                    }, 0 );
  
 
                // Update footer
                $( api.column( 2 ).footer() ).html
                (
                    numFormat(total)
                );
    
                // Total Column 3 over all pages
                total = api
                    .column( 3 )
                    .data()
                    .reduce( function (a, b) 
                    {
                        return intVal(a) + intVal(b);
                    }, 0 );
    
    
                // Update footer
                $( api.column( 3 ).footer() ).html
                (
                    numFormat(total)
                );
    
                // Total Column 4 over all pages
                total = api
                    .column( 4 )
                    .data()
                    .reduce( function (a, b) 
                    {
                        return intVal(a) + intVal(b);
                    }, 0 );
  
 
                // Update footer
                $( api.column( 4 ).footer() ).html
                (
                    numFormat(total)
                );
    
                // Total Column 5 over all pages
                total = api
                    .column( 5 )
                    .data()
                    .reduce( function (a, b) 
                    {
                        return intVal(a) + intVal(b);
                    }, 0 );
  
 
                // Update footer
                $( api.column( 5 ).footer() ).html
                (
                    numFormat(total)
                );
 
                // Total Column 6 over all pages
                total = api
                    .column( 6 )
                    .data()
                    .reduce( function (a, b) 
                    {
                        return intVal(a) + intVal(b);
                    }, 0 );
  
                // Update footer
                $( api.column( 6 ).footer() ).html
                (
                    numFormat(total)
                );
            }
    });     
    

html

    
    
<table id="myTable" class="cell-border table-bordered"  cellspacing="0" width="100%" margin-left=auto margin-right=auto>
        <thead>
            <tr style="height: 10px;">
                <th>ID</th>
                <th></th>
                <th>field2</th>
                <th>field3</th>
                <th>field4</th>
                <th>field5</th>
                <th>field6</th>
                <th>field7</th>
                <th>field8</th>
        </thead>    
        <tfoot>
            <tr>
                <th></th>
                <th></th>
                <th>field2</th>
                <th>field3</th>
                <th>field4</th>
                <th>field5</th>
                <th>field6</th>
                <th>field7</th>
                <th>field8</th>
            </tr>
        </tfoot>
    </table>    
    

monkeyboy

Oddly - when I alert the variables in:
return intVal(a) + intVal(b)

I see 0. When I alert the tyepof (a) and typeof(b)
i see "number" "object"

Head exploding...

allan

i see "number" "object"

And if you console.log( a )?

Allan

monkeyboy

console.log shows
0
Object { DT_RowId: "row_1", myTable: Object }

Columns 2 and 3 (my operands for the addition) are simple integers
column 4 Row(x) is rendered as the addition of field2 and field3 in row(x)

{ data:  null, "sClass": "binCentered", render: function (data, type, row) 
    {
        var numFormat = $.fn.dataTable.render.number( ",", ".", 0, "$" ).display;
        var total = parseInt(data.myTable.field2) + parseInt(data.myTable.field3);
        return numFormat(total);
    }
},

I sum the columns in the footer callback thus:

total = api
    .column( 6 )
    .data()
    .reduce( function (a, b) 
    {
        console.log(a);
        console.log(b);
        return intVal(a) + intVal(b);
    }, 0 );

The console log clearly shows something wrong in fetching data for summing the column.
Apparently rendering the data for the cell changes something in the footer callback.

sm1l3y

I had a similar task, I needed to take a column and take into account a the footer (sum) of the column to the left in a calculation. What I did was instead modify the JSON data directly (summed the column to the left and set as a variable) before passing the JSON to the data tables api.

allan

Ah

data: null

Yes. In that case it would be the row's data object that is passed in as the data parameter. You could access a specific property from it using data.myDbTable.field1. If you want to have the data parameter take the value of a specific parameter in the object, you would set it to be that value.

See the columns.data documentation for details on how it works when null is given as its value.

Allan