Page 1 of 1

Reportin subtotals.

Posted: Thu Apr 19, 2018 5:11 pm
by edrubins
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>

Re: Reportin subtotals.

Posted: Fri Apr 20, 2018 3:55 pm
by edrubins
Rereading my question, even I got confused. Let me try and clarify it.

I have a custom report the lists payee totals by category. It's a straight SQL
query using the generated template. What I want to do is show the subtotal for
each category after the last payee in that category. To that end I added the
following lua script to the report.


--[[
Report showing total for each category by payee with a subtotal
after each category.
]]
--[[
ToDo:
Show the subtotal after each category.
]]
local category_name = 0
local total = 0
local category_totals = {}
local the_record = nil

function handle_record(record)
if(not the_record) then
the_record = print(record)
end
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)
result:set('RECORD', the_record)
end

I then modified the generated template by adding a table row after the
<TMPL_LOOP NAME=CONTENT> tag.

.
.
.
<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>
<!-- Start additional code
Print the total for this category -->
<tr>
<td></td>
<td></td>
<td></td>
<td>SubTotal</td>
<td><TMPL_VAR SubTotal></td>
</tr>
<!-- End additional code. -->
.
.
.

The Subtotal row does not show up in the body of the report. Rather it shows after
the last category, before the total line. See the screenshot at https://www.dropbox.com/s/9ejy1n2tl57li ... s.png?dl=0.

Also, is there any documentation of the templating system and / the lua hand_record and complete functions? Or which HTTP template system is used?

Re: Reportin subtotals.

Posted: Sun Apr 22, 2018 10:45 am
by Nikolay
Could you provide the report SQL script?

Re: Reportin subtotals.

Posted: Fri Apr 27, 2018 4:18 pm
by edrubins
Sorry for the delay in responding.

SELECT
c.categname AS Category, s.subcategname as SubCategory,p.PAYEENAME AS Payee, a.TRANSCODE AS Type, SUM(a.TRANSAMOUNT) AS Amount
FROM
CHECKINGACCOUNT_V1 AS a, PAYEE_V1 AS p, CATEGORY_V1 AS c, SUBCATEGORY_V1 as s
WHERE
a.PAYEEID = p.PAYEEID AND c.CategID = a.CategID AND a.subcategid = s.subcategid AND a.TRANSDATE >= '2017-01-01' AND a.TRANSDATE <= '2017-12-31'
GROUP BY
a.CATEGID, SubCategory, Payee
ORDER BY
Category, SubCategory, Payee

Re: Reportin subtotals.

Posted: Mon May 07, 2018 4:19 pm
by Nikolay