Transaction Statistics

Page 1 of 1
6 posts
Transaction Statistics

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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 227 times
Re: Transaction Statistics

pacome
New MMEX User

Posts: 3
Joined: Mon Nov 24, 2014 4:22 am
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 196 times
Re: Transaction Statistics

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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!
Re: Transaction Statistics

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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 194 times
Re: Transaction Statistics

pacome
New MMEX User

Posts: 3
Joined: Mon Nov 24, 2014 4:22 am
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.
Re: Transaction Statistics

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
IMHO GRM is nice tool for learning languages.
JS, Lua, SQL, css, html5 in one.

Also this report partially is the same
viewtopic.php?f=16&t=5509
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 3 guests