Transaction Statistics

[Nikolay]

To create statistic report like this use General Report Manager

statistics.png (38.95 KiB) Viewed 2245 times

SQL script:

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;

Lua script:

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

html template

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>

[pacome]

I'm learning to create my own reports and  I have adapted the transaction statistics to my needs.

Here I leave it.

[guangong]

I'm learning to create my own reports and  I have adapted the transaction statistics to my needs.

Here I leave it.

cool and you are a real programer now!

[Nikolay]

Hi there,

I've updated a bit  the report
Please use left join to get data if subcategory empty.
Add accounts and currency table for multicurrency support.
Translate months to Spain (if needed)

Then it will brilliant!

Good luck!

[pacome]

First of all, sorry because the translation. I do the reports in spanish and later I translate some parts of them to the english but not completely. Next time I'll translate it completely.

I don't remember but I didn´t need to include the transfers into the report so, because of that, I didn't use "left join".

Thanks for your corrections. How I said I'm learning everything about using the program.

[Nikolay]

IMHO GRM is nice tool for learning languages.
JS, Lua, SQL, css, html5 in one.

Also this report partially is the same
http://forum.moneymanagerex.org/viewtop ... =16&t=5509