Transaction Statistics

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Transaction Statistics

Post by Nikolay »

To create statistic report like this use General Report Manager
statistics.png
(38.95 KiB) Downloaded 1244 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>
Attachments
Transatcion_Statistics.grm
Transaction Statistics Report
(1.43 KiB) Downloaded 543 times
pacome
New MMEX User
Posts: 3
Joined: Mon Nov 24, 2014 10:22 am
Are you a spam bot?: No

Re: Transaction Statistics

Post by pacome »

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

Here I leave it.
Attachments
sumary income and expenditure.grm
(2.19 KiB) Downloaded 1127 times
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Transaction Statistics

Post by guanlisheng »

pacome wrote: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!
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Transaction Statistics

Post by Nikolay »

Hi there,

I've updated a bit Image 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!
Attachments
sumary income and expenditure_v2.grm
(2.72 KiB) Downloaded 932 times
pacome
New MMEX User
Posts: 3
Joined: Mon Nov 24, 2014 10:22 am
Are you a spam bot?: No

Re: Transaction Statistics

Post by 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
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Transaction Statistics

Post by 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
Post Reply