Grouping with subtotals and grandtotal
Grouping with subtotals and grandtotal
Malcolm_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
I guess you are referring to this code:
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