Getting the sum of a rendered column

kenrayner

If anyone can point me in the right direction here, I'd be hugely grateful.

The basics are: I have a DataTables table, with Editor, and I have a calculated column at column 15. The column is calculated based on data in the other columns. To render that, I use a render function.

Now, I also want to get the total sum of that column. Here's the stripped-down code:

// JavaScript Document

var editor; // use a global for the submit and return data

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../../../assets/et/php/campaign_lines.php",
        table: "#campaign_lines-table",
        display: "bootstrap",
        fields: [ {
                label: "Some Data",
                name: "campaign_lines.some_data"
            },{
                label: "Some Other Data",
                name: "campaign_lines.some_other_data"
            },{
                label: "Cost",
                name: "campaign_lines.spot_cost"
            }
            ]

    } );

    // Activate an inline edit on click of a table cell
    $('#campaign_lines-table').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            onBlur: 'submit'
        } );
    } );

    var table=$('#campaign_lines-table').DataTable( {
    responsive: true,
        dom: "Bfrtip",
        ajax: {
            url: "../../../assets/et/php/campaign_lines.php",
            type: 'POST'
        },
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "campaign_lines.some_data" },                       
            { data: "campaign_lines.some_other_data" },                     
            { data: "campaign_lines.spot_cost" },           
            { data: null,
                render: function ( data, type, row ) {
                        // for simplicity here I'm just returning the number 10 - the function normally returns data based on some_data, some_other_data and spot_cost
                        return 10;
                        }
        ],
        order: [ 1, 'asc' ],
        select: {
            style:    'os',
            selector: 'td:first-child'
        },
                buttons: [
          { extend: "create", editor: editor },
              { extend: "edit",   editor: editor },
              { extend: "remove",   editor: editor }                      
        ],
                "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
            total = api
                .column( 4 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Total over this page
            pageTotal = api
                .column( 4, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $( api.column( 4 ).footer() ).html(
                '$'+pageTotal +' ( $'+ total +' total)'
            );
        }

    } );
} );

The footerCallback is directly lifted from the example (https://datatables.net/examples/advanced_init/footer_callback.html) - it works totally on any other column but returns 0 on that column - I suspect because it's trying to collate rendered data.

Currently going out of my tiny mind...

tangerine

You could build a running total in your "render" function, maybe into a global variable. It rather depends on what you need to do with that figure afterwards.

render: function ( data, type, row ) {
        this-value = relevant-calculation;
        running-total = running-total + this-value;
         return this-value;
         }

kenrayner

Ah. Good thought. It's just for display in the footer so that should work. I'll give it a go - thanks.

allan

You can use cell().render() to get a rendered value. The *.data() methods access the underlying data.

Allan

kenrayner

Thanks Allan - yes, in the meantime (after a couple of hours of trying to total up the data as a running total and always getting about 3 times the right amount with no discernible pattern!), I did a bit more forum searching and found:

https://datatables.net/forums/discussion/21742/api-column-data-call-on-rendered-column

So now I have:

            // Total over all pages
            data = api.column( 15 ).cache('search');
        total = data.length ?
                data.reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                } ) :
                0;

Which appears to work - do let me know if there's something which will mean that won't work out!

Again, thanks for all your help - I don't know what I'd do without DataTables/Editor.