Payee Comparison Report

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

Payee Comparison Report

Post by Nikolay »

change two lines in SQL script before use

Code: Select all

and p.payeename in ('PayeeI', 'PayeeII') --enter payee names here
and strftime('%Y', t.TRANSDATE)='2013'-- change to proper year
Payee_Comparison.png
(42.98 KiB) Downloaded 1025 times
SQL:

Code: Select all

select 
'' as COLOR ,
Payeename,
total(case strftime('%m', TRANSDATE) when '01' then transamount end) as Jan,
total(case strftime('%m', TRANSDATE) when '02' then transamount end) as Feb,
total(case strftime('%m', TRANSDATE) when '03' then transamount end) as Mar,
total(case strftime('%m', TRANSDATE) when '04' then transamount end) as Apr,
total(case strftime('%m', TRANSDATE) when '05' then transamount end) as May,
total(case strftime('%m', TRANSDATE) when '06' then transamount end) as Jun,
total(case strftime('%m', TRANSDATE) when '07' then transamount end) as Jul,
total(case strftime('%m', TRANSDATE) when '08' then transamount end) as Aug,
total(case strftime('%m', TRANSDATE) when '09' then transamount end) as Sep,
total(case strftime('%m', TRANSDATE) when '10' then transamount end) as Oct,
total(case strftime('%m', TRANSDATE) when '11' then transamount end) as Nov,
total(case strftime('%m', TRANSDATE) when '12' then transamount end) as Dec,
round(total(transamount ), 2) as Total
from(

select p.payeename, p.payeeid, t.TRANSDATE,
(case when t.TRANSCODE='Deposit' then t.transamount else -t.transamount end)
    * c.baseconvrate as transamount
from checkingaccount_v1 t
inner join accountlist_v1 a on a.accountid=t.accountid
inner join currencyformats_v1 c on a.currencyid=c.currencyid
inner join payee_v1 p on p.payeeid=t.payeeid
where transcode!='Transfer' and a.status!='Closed'
and p.payeename in ('PayeeI', 'PayeeII') --enter payee names here
and strftime('%Y', t.TRANSDATE)='2014'-- change t oproper year

)m
group by payeeid;
Lua:

Code: Select all

local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local colors = {"#FBD4B4", "#E5B8B7", "#B6DDE8", "#B8CCE4", "#F0EDCE", "#DEE3E7", "#99FF99"};
local data = '';
local count = 0;
local json = '{ fillColor : "rgba(255,255,255,0)", strokeColor :  "%s", pointStrokeColor : "#fff", pointColor : "rgba(0,0,0,1)", data : [%s] },';

function handle_record(record)
    count = count + 1;
    local color = colors[count];
    record:set('COLOR', color);
    local d = '';
    for i=1,12 do
        d = d .. record:get(months[i]) .. ',';
        record:set(months[i], string.format("%05.2f", record:get(months[i])));
    end
    data = data .. string.format(json, color, d);
end

function complete(result)
    result:set('TREND_DATA', data);
end
Template:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title><TMPL_VAR REPORTNAME></title>
    <script src = "Chart.js"></script>
    <link href = "master.css" rel = "stylesheet" />
</head>
<body>
    <div class="container">
        <h3>Payees Comparison Report for 2013</h3>
        <p><TMPL_VAR TODAY></p>
        <div class="row">

            <div class="col-xs-1">
                <canvas id="reportChart" width="750" height="300"></canvas><br>
                <table class="table table-condences">
                <thead>
                  <tr>
                    <th>Name</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>Total</th>
                  </tr>
                </thead>
                <tbody>
                    <TMPL_LOOP NAME=CONTENTS>
                    <tr bgcolor='<TMPL_VAR "COLOR">'>
                        <th ><TMPL_VAR Payeename></th>
                        <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><TMPL_VAR Total></td>
                    </tr>
                    </TMPL_LOOP>
                </tbody>
                </table>
            </div>
            <table class="table table-simple">
                <tbody>
                <TMPL_LOOP ERRORS>
                    <tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
                    <tr><td><TMPL_VAR ERROR></td></tr>
                </TMPL_LOOP>
                </tbody>
            </table>
        </div>
    </div>   
</body>
<script type="text/javascript">
    var data = {
        labels : ["January","February","March","April","May","June","July",
                      "August","September","October","November","December"],
        datasets : [<TMPL_VAR "TREND_DATA">]
    }
    var ctx = document.getElementById("reportChart").getContext("2d");
    var reportChart = new Chart(ctx).Line(data);
  </script>
</html>
Attachments
Payees_compare.grm
(2.17 KiB) Downloaded 597 times
User avatar
avma
Senior MMEX User
Posts: 30
Joined: Wed Sep 02, 2009 6:47 am
Are you a spam bot?: No
Location: Israel

Re: Payee Comparison Report

Post by avma »

Hi Nikolay

This is really a great report and i use it a lot so thank you very much.
I think though, it is not including split transactions...
If i'm correct, could you pleas amend it.

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

Re: Payee Comparison Report

Post by Nikolay »

payee for split transactions the same, should work. Anyway i'll check.

PS Split is working!
User avatar
avma
Senior MMEX User
Posts: 30
Joined: Wed Sep 02, 2009 6:47 am
Are you a spam bot?: No
Location: Israel

Re: Payee Comparison Report

Post by avma »

Nikolay wrote:payee for split transactions the same, should work. Anyway i'll check.

PS Split is working!

Yes you are right, my bad...
what happened is, i took your sql code and altered it so i can compere categories over 12 months. now this report does not work on split transactions for is a transaction is split between 2 or more categories you'll have to join the splittansaction_v1 table into the query as well... and i don't know how to do it. the only other way i could do it is using alldata view, but unfortunately it is much slower then you report. so if it is not too complecated could you please help me on this?

thanks a lot
avi


Code: Select all

select 
'' as COLOR ,
Categname,
total(case strftime('%m', TRANSDATE) when '01' then transamount end) as Jan,
total(case strftime('%m', TRANSDATE) when '02' then transamount end) as Feb,
total(case strftime('%m', TRANSDATE) when '03' then transamount end) as Mar,
total(case strftime('%m', TRANSDATE) when '04' then transamount end) as Apr,
total(case strftime('%m', TRANSDATE) when '05' then transamount end) as May,
total(case strftime('%m', TRANSDATE) when '06' then transamount end) as Jun,
total(case strftime('%m', TRANSDATE) when '07' then transamount end) as Jul,
total(case strftime('%m', TRANSDATE) when '08' then transamount end) as Aug,
total(case strftime('%m', TRANSDATE) when '09' then transamount end) as Sep,
total(case strftime('%m', TRANSDATE) when '10' then transamount end) as Oct,
total(case strftime('%m', TRANSDATE) when '11' then transamount end) as Nov,
total(case strftime('%m', TRANSDATE) when '12' then transamount end) as Dec,
round(total(transamount ), 2) as Total
from (select  categSORT, p.categname, p.categid, t.TRANSDATE, 
(case when t.TRANSCODE='Deposit' then t.transamount else -t.transamount  end)
    * c.baseconvrate as transamount
from checkingaccount_v1 as t

inner join accountlist_v1 a on a.accountid=t.accountid

inner join currencyformats_v1 c on a.currencyid=c.currencyid
inner join category_v1 p on p.categid=t.categid
where transcode!='Transfer' and a.status!='Closed'

and strftime('%Y', t.TRANSDATE)='2014'-- change t oproper year
)m
group by categid
order by  categSORT
;
p/s categSORT is a column i add to category_v1 so i can sort my categorizes the way i want them to show.
Post Reply