Grouping with subtotals and grandtotal

Grouping with subtotals and grandtotal

Malcolm_PetersenMalcolm_Petersen Posts: 1Questions: 1Answers: 0

Hi

I add subtotal and grandtotal rows dynamically using javascript then pass my table to datatables. Problem is my grandtotal value includes the sub total values thus duplicating the grandtotal value. So I am trying the exclude the subtotal values from the grandtotal value based on the class I have assigned to the subtotal rows but can't get it working. Please help

Code below:

<!DOCTYPE html>
<html>
<head>
    <title>Sub-Total Table</title>

    <link href="Themes/cupertino/jquery-ui.css" rel="stylesheet" type="text/css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.jqueryui.min.css"  type="text/css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css"  type="text/css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/rowgroup/1.4.0/css/rowGroup.dataTables.min.css"  type="text/css" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.4.0/css/font-awesome.min.css"  type="text/css" />
    <script src="https://code.jquery.com/jquery-3.7.0.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/rowgroup/1.4.0/js/dataTables.rowGroup.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.jqueryui.min.js" type="text/javascript"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.colVis.min.js" type="text/javascript"></script>
    <script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/plug-ins/1.10.12/api/sum().js" type="text/javascript"></script>

    <style type="text/css">
       .subtotal
       {
           font-weight: bold;
           text-align: right;
       }
    </style>

</head>
<body>
    <table id="data-table">
        <thead>
            <tr>
                <th>Category</th>
                <th align=right>Value</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Brandy</td>
                <td align=right>10.50</td>
            </tr>
            <tr>
                <td>Brandy</td>
                <td align=right>15.99</td>
            </tr>
            <tr>
                <td>Whisky</td>
                <td align=right>10.69</td>
            </tr>
            <tr>
                <td>Whisky</td>
                <td align=right>9.99</td>
            </tr>
           <tr>
                <td>Rum</td>
                <td align=right>11.50</td>
            </tr>
            <tr>
                <td>Rum</td>
                <td align=right>19.49</td>
            </tr>

        </tbody>
    </table>

 <script>

function InsertSubtotals() {
            let table = document.getElementById('data-table');
            let rows = table.getElementsByTagName('tbody')[0].getElementsByTagName('tr');
            let currentCategory = rows[0].getElementsByTagName('td')[0].textContent;
            let subtotal = 0;
            let grandtotal = 0;

            for (let i = 0; i < rows.length; i++) {
                let row = rows[i];
                let category = row.getElementsByTagName('td')[0].textContent;
                let value = parseFloat(row.getElementsByTagName('td')[1].textContent);
                //alert(category + '/' + value + '/' + currentCategory);

                if (category !== currentCategory) {
                    // Insert a sub-total row
                    value = 0;
                    let subtotalRow = document.createElement('tr');
                    subtotalRow.classList.add('subtotal');
                    let cell = document.createElement('td');
                    cell.innerHTML = currentCategory + " Sub-Total";
                    //cell.classList.add("subtotal");
                    subtotalRow.appendChild(cell);

                    cell = document.createElement('td');
                    //cell.setAttribute('style','font-size: 20px; cursor: pointer; text-align:right;');
                    cell.innerHTML = subtotal.toFixed(2);
                    //cell.classList.add("subtotal");
                    subtotalRow.appendChild(cell);

                    table.getElementsByTagName('tbody')[0].insertBefore(subtotalRow, row);
                    
                    // Reset subtotal for the new category
                    subtotal = 0;
                    currentCategory = category;
                }

                subtotal += value;
                grandtotal += value;
            }
            var newRow = table.insertRow(-1);
            newRow.className = 'subtotal';
            var cell1 = newRow.insertCell(0);
            var cell2 = newRow.insertCell(1);
            //cell2.setAttribute('style','font-size: 20px; cursor: pointer; text-align:right;');
            cell1.innerHTML = currentCategory + " Sub-Total";
            //cell1.className = 'subtotal';
            cell2.innerHTML = subtotal.toFixed(2);
            cell2.className = "subtotal";

            var footer = table.createTFoot();
            var gnewRow = footer.insertRow(-1);
            var gcell1 = gnewRow.insertCell(0);
            var gcell2 = gnewRow.insertCell(1);
            gcell1.setAttribute('style','font-size: 20px; cursor: pointer; text-align:left;');
            gcell2.setAttribute('style','font-size: 20px; cursor: pointer; text-align:right;');
            gcell1.innerHTML = "<B>Grand Total</B>";



}

        InsertSubtotals();

$('#data-table').DataTable({
            "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 excluding rows with a certain class
                total = api
                    .column( 1, {search:'applied'} ) // Apply DataTables search filter
                    .data()
                    .reduce( function (a, b) {
                        // Skip rows with a certain class
                        if (!$(api.row(api.row(this).index()).node()).hasClass('subtotal')) {
                            return intVal(a) + intVal(b);
                        } else {
                            return intVal(a);
                        }
                    }, 0 );

                // Update footer
                $( api.column( 1 ).footer() ).html(
                    '$'+total
                );
            }
        });


 </script>

</body>
</html>

Answers

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    I guess you are referring to this code:

                            // Skip rows with a certain class
                            if (!$(api.row(api.row(this).index()).node()).hasClass('subtotal')) {
                                return intVal(a) + intVal(b);
                            } else {
                                return intVal(a);
                            }
    

    It looks reasonable. There is a lot of code used to build the table and hard to visualize the result without actually seeing it. Can you build a simple test case so we can see what the final table looks like? You can start here f you like:
    https://live.datatables.net/

    Kevin

Sign In or Register to comment.