How to apply a numeric format to a column

georgeforster

Following on from my earlier question regarding making a column total work, I now want to format the number to match the rest of the data. I'm populating the table via HTML, and the main data is written out with the correct numeric formatting. However the dynamic column totals have no numeric formatting applied at the moment.

I believe that I can use column rendering to achieve this, but my efforts so far have failed. I'm using version 1.10.19

This is my script so far, I'm trying to set column 5 to 3dp with a comma for thousand separator

    <script>
    $(document).ready(function() 
        {
        $('table.example').DataTable
            (
                {
                    dom: 'lfrtBip',
                    searching: true,
                    ordering: true,
                    order: [[4, "desc"]],
                    paging : true,
                    info : true,
                    columns: 
                    [
                        null,
                        null,
                        null,
                        null,
                        null,
                        {
                            data: null, render: $.fn.dataTable.render.number(',', '.', 3, '');
                        },
                        null
                    ],

                    // This section adds a filter to the columns selected. This needs a table footer

                    initComplete: function () {
                        this.api().columns([3]).every( function () {

                            var column = this;

                            var select = $('<select style="font-size:inherit;"><option value="">All</option></select>')
                                .appendTo( $(column.footer()).empty() )
                                .on( 'change', function () {
                                    var val = $.fn.dataTable.util.escapeRegex(
                                        $(this).val()
                                    );

                                    column
                                        .search( val ? '^'+val+'$' : '', true, false )
                                        .draw();
                                } ); 

                            column.data().unique().sort().each( function ( d, j ) {
                            var val = $('<div/>').html(d).text();
                            select.append( '<option value="' + val + '">' + val + '</option>' );

                            } );

                        } );
                    },

                    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
                        totalflights = api
                            .column( 4 , { search: 'applied'})
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        totalhours = api
                            .column( 5 , { search: 'applied'})
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        totalpassengers = api
                            .column( 6 , { search: 'applied'})
                            .data()
                            .reduce( function (a, b) {
                                return intVal(a) + intVal(b);
                            }, 0 );

                        // Update footer
                            $( api.column( 4 ).footer() ).html(
                                totalflights
                            );
                            $( api.column( 5 ).footer() ).html(
                                totalhours
                            );
                            $( api.column( 6 ).footer() ).html(
                                totalpassengers
                            );

                        }
                    }
            );                  
        } 
    );
        </script>

kthorngren

I think it will work if you use columnDefs instead. Remove the columns config and replace with this:

                  columnDefs: [
                    {
                      targets: 5,
                      render: $.fn.dataTable.render.number(',', '.', 3, '')
                    }
                  ],

Also make sure you don't have the ; at the end of the line as that is a syntax error.

Kevin

georgeforster

Thanks Kevin, That changes the format of the cells in the body of the table, but the data in the footer is unformatted

This is my script for the column Defs

columnDefs: 
[
    {
        targets: 4,
        render: $.fn.dataTable.render.number(',', '.', 0, '')
    },
    {
        targets: 5,
        render: $.fn.dataTable.render.number(',', '.', 3, '')
    },
    {
        targets: 6,
        render: $.fn.dataTable.render.number(',', '.', 1, '')
    }
],

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

georgeforster

Here is a copy of the page that I'm working on

http://flywestwind.org/testcases/PassengerPilots.php

kthorngren

You need to apply the formatting in the footer manually. You can chain .display() onto the number renderer in the footer code. See this example:
$.fn.dataTable.render.number('.', ',', 2, '').display('123532')

Kevin

georgeforster

Thanks Kevin

That's exactly what I wanted