MMEX Discussion Forum

Posted: **Tue Feb 18, 2014 9:51 pm**

change two lines in SQL script before use

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 1188 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>

Posted: **Wed May 21, 2014 4:47 pm**

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

Posted: **Fri May 23, 2014 11:34 am**

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

PS Split is working!

Posted: **Sat May 24, 2014 12:17 pm**

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.

MMEX Discussion Forum

Payee Comparison Report

Payee Comparison Report

Re: Payee Comparison Report

Re: Payee Comparison Report

Re: Payee Comparison Report