Totals Row

Totals Row

erixoltanerixoltan Posts: 7Questions: 2Answers: 0

We have some standard database queries with a home-grown table control, and we are now giving our report designers the option to use a datatables table, which they are very excited about.

We have a request to be able to add at totals row at the bottom, either in the footer or as the first or last row in the table. I don't see this as a feature of datatables, but I can't imagine it would be that hard to add.

Is there a way to do this, or have others had success providing this sort of capability? Are there any established best practices? I can't imagine that this would be an unusual request.

Thanks,
Erik

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,466Questions: 26Answers: 4,804
    Answer ✓

    See this example of how to place totals in the footer.

    Kevin

  • erixoltanerixoltan Posts: 7Questions: 2Answers: 0

    Thanks, that looks awesome. Glad I asked!

  • erixoltanerixoltan Posts: 7Questions: 2Answers: 0

    I've mostly gotten this working, but I'm down to a single issue.

            this.data_table = $(inner_id).dataTable({
                data: data,
                columns: columns,
                footerCallback: function() {
                    let sumdef, sumcol;
                    let api = this.api();
                    let int_val = function(i) {
                        if (typeof i === 'string') {
                            return i.replace(/[\$,]/g,'') * 1;
                        }
                        if (typeof i === 'number') {
                            return i;
                        }
                        return 0;
                    };
                    let reduction = function(a,b) {
                        return int_val(a) + int_val(b);
                    };
                    for (sumdef of sums) {
                        sumcol = sumdef.col;
                        let sumtype = sumdef.type;
                        let coltot = api.column(sumcol).data().reduce(reduction, 0);
                        let pagetot = api.column(sumcol, {page: 'current'}).data().reduce(reduction, 0);
                        let colcode = `${F.convert_value(pagetot, sumtype)}<br>${F.convert_value(coltot, sumtype)}`;
                        console.log(colcode);
                        $(api.column(sumcol).footer()).html(colcode);
                    }
                },
            });
    

    On line 25, the console.log statement shows that the correct value is going into the colcode variable. On line 26, nothing happens the the footer() method returns null. I see that the table tag has been assigned class="data_tables_wrapper no-footer", but I don't know how to give it a footer. I've searched the documentation but am coming up blank. Has anyone else faced this issue? The data table looks great except there is no totals row at the bottom.

  • tangerinetangerine Posts: 3,352Questions: 37Answers: 394
    edited March 2023

    Does your HTML table have a tfoot tag?

  • kthorngrenkthorngren Posts: 20,466Questions: 26Answers: 4,804
    edited March 2023

    Datatables doesn't add a footer. You need to add it in HTML or use Javascript or jQuery to programmatically add the footer before Datatables is initialized.

    Kevin

  • erixoltanerixoltan Posts: 7Questions: 2Answers: 0

    The HTML code for the <table> tag is dynamically generated based on the column definitions. Then a Data Table is added to it when the data callback comes back from the server. Here is the starting code for the table.

    <div id='card_17' class='alx_card col_6 height_6'>YTD Top 50 Products &nbsp; 
       <table id='data_table_17' class='display'>
          <thead></thead>
          <tbody><tr><th>Updating...</th></tr></tbody>
          <tfoot>
             <tr><th>Totals</th><th></th><th></th><th></th><th></th><th></th></tr>
          </tfoot>
       </table>
    </div>
    

    The Data Table itself looks great, but there is no footer row with my totals.

  • allanallan Posts: 62,086Questions: 1Answers: 10,180 Site admin
    Answer ✓

    I suspect we'll need a link to a test case showing the issue please. It sounds like DataTables might not be registering the footers in your table for some reason. Perhaps try generating empty th elements for the header as well.

    Allan

  • erixoltanerixoltan Posts: 7Questions: 2Answers: 0

    Thanks, allan. As so often happens, when I was composing the test case to explain the problem in more detail, I figured it out and got the whole thing to work!

    It turns out that after setting everything up correctly according to the advice I had received here, I was not disabling a line of code that we use in other cases, that removes the innerHTML before repopulating our other tables and charts. As a result, it was blowing away the <tfoot> tag which was preventing my footer work from doing everything. When I disabled that line of code for data tables, the footer suddenly worked!

    I'm marking this as answered, and thanks to everyone who helped me.

Sign In or Register to comment.