MMEX Discussion Forum

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>&nbspJan&nbsp</th>
        <th>&nbspFeb&nbsp</th>
        <th>&nbspMar&nbsp</th>
        <th>&nbspApr&nbsp</th>
        <th>&nbspMay&nbsp</th>
        <th>&nbspJun&nbsp</th>
        <th>&nbspJul&nbsp</th>
        <th>&nbspAug&nbsp</th>
        <th>&nbspSep&nbsp</th>
        <th>&nbspOct&nbsp</th>
        <th>&nbspNov&nbsp</th>
        <th>&nbspDec&nbsp</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>