Reportin subtotals.
Posted: Thu Apr 19, 2018 5:11 pm
I've created a custom report to print category totals by payee. I would like to print the category total after the last payee in the category. My lua code has local variables to save the current category name as well as
accumulating each category' s total in a table indexed by category name in the handle_record function. The template file attempts to print the category subtotal after the </TMPL_LOOP> tag, but
nothing is printed.
The Lua code and template are listed below.
As an aside, is there any documentation on writing custom reports - particularly the record the and result tables and the template file tags?
Lua code :
--[[
Report showing total for each category by payee with a subtotal
after each category.
]]
local category_name = 0
local total = 0
local category_totals = {}
function handle_record(record)
category = record:get('Category')
amount = record:get('Amount')
if(not category == category_name) then
record:set('SubTotal', category_totals.category_name)
category_name = category
category_total.category = 0
end
total = total + amount
category_totals.category = category_totals.category + amount
end
function complete(result)
result:set('TOTAL', total)
end
Template:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http - equiv = "Content-Type" content = "text/html">
<title><TMPL_VAR REPORTNAME></title>
<script src = "ChartNew.js"></script>
<script src = "sorttable.js"></script>
<link href = "master.css" rel = "stylesheet">
</head>
<body>
<div class = "container">
<h3><TMPL_VAR REPORTNAME></h3>
<TMPL_VAR TODAY><hr>
<div class = "row">
<div class = "col-xs-2"></div>
<div class = "col-xs-8">
<table class = "table">
<thead>
<tr>
<th>Category</th>
<th>SubCategory</th>
<th>Payee</th>
<th>Type</th>
<th>Amount</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR "Category"></td>
<td><TMPL_VAR "SubCategory"></td>
<td><TMPL_VAR "Payee"></td>
<td><TMPL_VAR "Type"></td>
<td class = "text-right"><TMPL_VAR "Amount"></td>
</tr>
</TMPL_LOOP>
<tr>
<td></td>
<td></td>
<td></td>
<td>SubTotal</td>
<td><TMPL_VAR "SubTotal"></td>
</tr>
<tfoot>
<tr class="total">
<td></td>
<td></td>
<td class="i18n">Total:</td>
<td></td>
<td></td>
<td class="money" prefix="<TMPL_VAR PFX_SYMBOL>" suffix="<TMPL_VAR SFX_SYMBOL>" nowrap><TMPL_VAR TOTAL></td>
<td></td>
<td></td>
</tr>
</tfoot>
</tbody>
</table>
</div>
<TMPL_LOOP ERRORS>
<TMPL_VAR ERROR>
</TMPL_LOOP>
</div>
</div>
</body>
<script>
<!-- Format double to base currency -->
function currency(n) {
n = parseFloat(n);
n = isNaN(n) ? 0 : n.toFixed(2);
var out = n.toString().replace(".", "|");
out = out.toString().replace(/\B(?=(\d{3})+(?!\d))/g, "<TMPL_VAR GROUP_SEPARATOR>");
out = out.replace("|", "<TMPL_VAR DECIMAL_POINT>");
return out;
}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {
var element = elements;
element.style.textAlign='right';
if (element.innerHTML.indexOf("-") > -1) {
element.style.color="#ff0000";
}
element.innerHTML = '<TMPL_VAR PFX_SYMBOL>' + currency(element.innerHTML) +'<TMPL_VAR SFX_SYMBOL>';
}
</script>
</html>
accumulating each category' s total in a table indexed by category name in the handle_record function. The template file attempts to print the category subtotal after the </TMPL_LOOP> tag, but
nothing is printed.
The Lua code and template are listed below.
As an aside, is there any documentation on writing custom reports - particularly the record the and result tables and the template file tags?
Lua code :
--[[
Report showing total for each category by payee with a subtotal
after each category.
]]
local category_name = 0
local total = 0
local category_totals = {}
function handle_record(record)
category = record:get('Category')
amount = record:get('Amount')
if(not category == category_name) then
record:set('SubTotal', category_totals.category_name)
category_name = category
category_total.category = 0
end
total = total + amount
category_totals.category = category_totals.category + amount
end
function complete(result)
result:set('TOTAL', total)
end
Template:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta http - equiv = "Content-Type" content = "text/html">
<title><TMPL_VAR REPORTNAME></title>
<script src = "ChartNew.js"></script>
<script src = "sorttable.js"></script>
<link href = "master.css" rel = "stylesheet">
</head>
<body>
<div class = "container">
<h3><TMPL_VAR REPORTNAME></h3>
<TMPL_VAR TODAY><hr>
<div class = "row">
<div class = "col-xs-2"></div>
<div class = "col-xs-8">
<table class = "table">
<thead>
<tr>
<th>Category</th>
<th>SubCategory</th>
<th>Payee</th>
<th>Type</th>
<th>Amount</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR "Category"></td>
<td><TMPL_VAR "SubCategory"></td>
<td><TMPL_VAR "Payee"></td>
<td><TMPL_VAR "Type"></td>
<td class = "text-right"><TMPL_VAR "Amount"></td>
</tr>
</TMPL_LOOP>
<tr>
<td></td>
<td></td>
<td></td>
<td>SubTotal</td>
<td><TMPL_VAR "SubTotal"></td>
</tr>
<tfoot>
<tr class="total">
<td></td>
<td></td>
<td class="i18n">Total:</td>
<td></td>
<td></td>
<td class="money" prefix="<TMPL_VAR PFX_SYMBOL>" suffix="<TMPL_VAR SFX_SYMBOL>" nowrap><TMPL_VAR TOTAL></td>
<td></td>
<td></td>
</tr>
</tfoot>
</tbody>
</table>
</div>
<TMPL_LOOP ERRORS>
<TMPL_VAR ERROR>
</TMPL_LOOP>
</div>
</div>
</body>
<script>
<!-- Format double to base currency -->
function currency(n) {
n = parseFloat(n);
n = isNaN(n) ? 0 : n.toFixed(2);
var out = n.toString().replace(".", "|");
out = out.toString().replace(/\B(?=(\d{3})+(?!\d))/g, "<TMPL_VAR GROUP_SEPARATOR>");
out = out.replace("|", "<TMPL_VAR DECIMAL_POINT>");
return out;
}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {
var element = elements;
element.style.textAlign='right';
if (element.innerHTML.indexOf("-") > -1) {
element.style.color="#ff0000";
}
element.innerHTML = '<TMPL_VAR PFX_SYMBOL>' + currency(element.innerHTML) +'<TMPL_VAR SFX_SYMBOL>';
}
</script>
</html>