Payee Comparison Report

Page 1 of 1
4 posts
Payee Comparison Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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 298 times
Re: Payee Comparison Report
User avatar
avma
Super MMEX User

Posts: 42
Joined: Wed Sep 02, 2009 12:47 am
Location: Israel
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
Re: Payee Comparison Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
payee for split transactions the same, should work. Anyway i'll check.

PS Split is working!
Re: Payee Comparison Report
User avatar
avma
Super MMEX User

Posts: 42
Joined: Wed Sep 02, 2009 12:47 am
Location: Israel
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.
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 3 guests