Code: Select all
select strftime('%Y', TRANSDATE) as YEAR
, total('01' = strftime('%m', TRANSDATE)) as Jan
, total('02' = strftime('%m', TRANSDATE)) as Feb
, total('03' = strftime('%m', TRANSDATE)) as Mar
, total('04' = strftime('%m', TRANSDATE)) as Apr
, total('05' = strftime('%m', TRANSDATE)) as May
, total('06' = strftime('%m', TRANSDATE)) as Jun
, total('07' = strftime('%m', TRANSDATE)) as Jul
, total('08' = strftime('%m', TRANSDATE)) as Aug
, total('09' = strftime('%m', TRANSDATE)) as Sep
, total('10' = strftime('%m', TRANSDATE)) as Oct
, total('11' = strftime('%m', TRANSDATE)) as Nov
, total('12' = strftime('%m', TRANSDATE)) as Dec
from CHECKINGACCOUNT_V1
group by year
order by year desc;
Code: Select all
local totals = {0,0,0,0,0,0,0,0,0,0,0,0}
local grand_total = 0; local count = 0;
local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
function handle_record(record)
local year_total = 0
for i=1,12 do
year_total = year_total + record:get(months[i]);
record:set(months[i], math.floor(record:get(months[i])));
totals[i] = totals[i] + record:get(months[i]);
end
count = count +1;
record:set("YEAR_TOTAL", year_total)
grand_total = grand_total + year_total;
end
function complete(result)
for i=1,12 do
result:set("AVERAGE_" .. months[i], math.floor(totals[i]/count));
end
result:set("GRAND_TOTAL", grand_total);
end
Code: Select all
<h3><TMPL_VAR REPORTNAME></h3>
<TMPL_VAR TODAY><hr>
<CENTER>
<table cellspacing='1' width='90%'>
<tr><td><table cellspacing='1' >
<tr bgcolor='#EEEEEE'>
<th>Year</th>
<th> Jan </th>
<th> Feb </th>
<th> Mar </th>
<th> Apr </th>
<th> May </th>
<th> Jun </th>
<th> Jul </th>
<th> Aug </th>
<th> Sep </th>
<th> Oct </th>
<th> Nov </th>
<th> Dec </th>
<th bgcolor='#D5D6DE'>Total</th>
</tr>
<TMPL_LOOP NAME=CONTENTS>
<TMPL_IF __ODD__>
<tr align='center'>
<TMPL_ELSE>
<tr bgcolor='#E1EDFB' align='center'>
</TMPL_IF>
<td><TMPL_VAR "YEAR"></td>
<td><TMPL_VAR "Jan"></td>
<td><TMPL_VAR "Feb"></td>
<td><TMPL_VAR "Mar"></td>
<td><TMPL_VAR "Apr"></td>
<td><TMPL_VAR "May"></td>
<td><TMPL_VAR "Jun"></td>
<td><TMPL_VAR "Jul"></td>
<td><TMPL_VAR "Aug"></td>
<td><TMPL_VAR "Sep"></td>
<td><TMPL_VAR "Oct"></td>
<td><TMPL_VAR "Nov"></td>
<td><TMPL_VAR "Dec"></td>
<td bgcolor='#D5D6DE'><TMPL_VAR YEAR_TOTAL></td>
</tr>
</TMPL_LOOP>
<tr bgcolor='#B8CCE4' align='center'>
<td>Average:</td>
<td><TMPL_VAR AVERAGE_Jan></td>
<td><TMPL_VAR AVERAGE_Feb></td>
<td><TMPL_VAR AVERAGE_Mar></td>
<td><TMPL_VAR AVERAGE_Apr></td>
<td><TMPL_VAR AVERAGE_May></td>
<td><TMPL_VAR AVERAGE_Jun></td>
<td><TMPL_VAR AVERAGE_Jul></td>
<td><TMPL_VAR AVERAGE_Aug></td>
<td><TMPL_VAR AVERAGE_Sep></td>
<td><TMPL_VAR AVERAGE_Oct></td>
<td><TMPL_VAR AVERAGE_Nov></td>
<td><TMPL_VAR AVERAGE_Dec></td>
<td bgcolor='#DEE3E7'><TMPL_VAR GRAND_TOTAL></td>
</tr>
</td><td></td></tr></table></table>
</CENTER>
<TMPL_LOOP ERRORS>
<hr>
<TMPL_VAR ERROR>
</TMPL_LOOP>