Trend Reports

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

Moderator: Renato

Post Reply
siena123
New MMEX User
Posts: 9
Joined: Thu Jan 24, 2013 1:00 am
Are you a spam bot?: No

Trend Reports

Post by siena123 »

Image
  • Account
  • SQL ([color=#FF0000]Update account name in line 29.[/color]) [code] select strftime('%Y', TRANSDATE) as YEAR , strftime('%m', TRANSDATE) as MONTH , '' as DATE , total(TRANSAMOUNT) + (select INITIALBAL + total(TRANSAMOUNT) from (select ACCOUNTID, TRANSDATE, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer') t2 where t2.ACCOUNTID = t1.ACCOUNTID and t2.STATUS <> 'V' and (strftime('%Y', t2.TRANSDATE) < strftime('%Y', t1.TRANSDATE) or (strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE) and strftime('%m', t2.TRANSDATE) < strftime('%m', t1.TRANSDATE))) ) as Balance from (select ACCOUNTID, TRANSDATE, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer') t1 inner join ACCOUNTLIST_V1 a on a.ACCOUNTID = t1.ACCOUNTID where ACCOUNTNAME = 'Account1' and t1.STATUS <> 'V' group by YEAR, MONTH order by YEAR asc, MONTH asc; [/code] Lua [code] local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}; local labels = 'labels : ['; local data = 'datasets : [{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(0,0,0,0.5)",pointColor:"rgba(0,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:['; function handle_record(record) local date = months[tonumber(record:get("MONTH"))] .. ' ' .. record:get("YEAR"); labels = labels .. '"' .. date .. '",'; record:set("DATE", date); local balance = string.format("%.2f", tonumber(record:get("Balance"))); if tonumber(string.sub(balance,-1)) == 0 and tonumber(string.sub(balance,-2)) ~= 0 then data = data .. '\'' .. balance .. '\','; else data = data .. balance .. ','; end end function complete(result) result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]"); end [/code] Template [code] <!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> <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE> <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT> </head> <body> <div class="container"> <h3>Account Trend Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class "table-condensed"> <tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr> <tr><td>&nbsp;</td></tr> <tr><td><table class="table"> <thead> <tr> <th>Date</th> <th class="text-right">Balance</th> </tr> </thead> <tbody> <TMPL_LOOP NAME=CONTENTS> <tr> <td><TMPL_VAR DATE></td> <td class="money text-right"><TMPL_VAR Balance></td> </tr> </TMPL_LOOP> </tbody> </table></td></tr></table> </div> <table class="table"> <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"> <!-- Format numbers --> function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);} var elements= document.getElementsByClassName("money"); for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);} <!-- Chart --> var data = {<TMPL_VAR "TREND_DATA">} var ctx = document.getElementById("reportChart").getContext("2d"); var reportChart = new Chart(ctx).Line(data); </script> </html> [/code]
  • Category
  • SQL ([color=#FF0000]Update category and subcategory names in line 21.[/color]) [code] select strftime('%Y', t1.TRANSDATE) as YEAR , strftime('%m', t1.TRANSDATE) as MONTH , '' as DATE , total(t1.TRANSAMOUNT * c1.BASECONVRATE) + (select total(split.SPLITTRANSAMOUNT * c2.BASECONVRATE) from CHECKINGACCOUNT_V1 as t2 inner join ACCOUNTLIST_V1 as a2 on a2.ACCOUNTID = t1.ACCOUNTID inner join CURRENCYFORMATS_V1 as c2 on c2.CURRENCYID = a2.CURRENCYID inner join SPLITTRANSACTIONS_V1 as split on t2.TRANSID = split.TRANSID where strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE) and strftime('%m', t2.TRANSDATE) = strftime('%m', t1.TRANSDATE) and split.CATEGID = cat.CATEGID and split.SUBCATEGID = subcat.SUBCATEGID and t2.STATUS <> 'V') as AMOUNT from CHECKINGACCOUNT_V1 as t1 inner join ACCOUNTLIST_V1 as a1 on a1.ACCOUNTID = t1.ACCOUNTID inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID inner join CATEGORY_V1 as cat on t1.CATEGID = cat.CATEGID inner join SUBCATEGORY_V1 as subcat on t1.SUBCATEGID = subcat.SUBCATEGID where cat.CATEGNAME = 'Food' and subcat.SUBCATEGNAME = 'Groceries' and t1.STATUS <> 'V' group by year, month order by year asc, month asc; [/code] Lua [code] local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}; local labels = 'labels : ['; local data = 'datasets : [{fillColor : "rgba(0,0,0,0.5)", strokeColor : "rgba(0,0,0,0.5)", data : ['; local average = 0; local count = 0; function handle_record(record) local date = months[tonumber(record:get("MONTH"))] .. ' ' .. record:get("YEAR"); labels = labels .. '"' .. date .. '",'; record:set("DATE", date); local amount = string.format("%.2f", tonumber(record:get("AMOUNT"))); if tonumber(string.sub(amount,-1)) == 0 and tonumber(string.sub(amount,-2)) ~= 0 then data = data .. '\'' .. amount .. '\','; else data = data .. amount .. ','; end average = average + amount; count = count + 1; end function complete(result) if (count > 0) then average = string.format("%.2f", average / count); else average = 0; end result:set("AVERAGE", average); result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]"); end [/code] Template [code] <!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> <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE> <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT> </head> <body> <div class="container"> <h3>Category Trend Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class "table-condensed"> <tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr> <tr><td>&nbsp;</td></tr> <tr><td><table class="table"> <thead> <tr> <th>Date</th> <th class="text-right">Amount</th> </tr> </thead> <tbody> <TMPL_LOOP NAME=CONTENTS> <tr> <td><TMPL_VAR DATE></td> <td class="money text-right"><TMPL_VAR AMOUNT></td> </tr> </TMPL_LOOP> </tbody> <tfoot> <tr class="total"> <td>Average</td> <td class="money text-right"><TMPL_VAR AVERAGE></td> </tr> </tfoot> </table></td></tr></table> </div> <table class="table"> <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"> <!-- Format numbers --> function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);} var elements= document.getElementsByClassName("money"); for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);} <!-- Chart --> var data = {<TMPL_VAR "TREND_DATA">} var ctx = document.getElementById("reportChart").getContext("2d"); var reportChart = new Chart(ctx).Bar(data); </script> </html> [/code]
  • Payee
  • SQL ([color=#FF0000]Update payee name in line 20.[/color]) [code] select strftime('%Y', TRANSDATE) as YEAR , '' as COLOR , total(case when '01' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jan , total(case when '02' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Feb , total(case when '03' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Mar , total(case when '04' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Apr , total(case when '05' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as May , total(case when '06' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jun , total(case when '07' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jul , total(case when '08' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Aug , total(case when '09' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Sep , total(case when '10' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Oct , total(case when '11' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Nov , total(case when '12' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Dec from CHECKINGACCOUNT_V1 inner join ACCOUNTLIST_V1 on ACCOUNTLIST_V1.ACCOUNTID = CHECKINGACCOUNT_V1.ACCOUNTID inner join CURRENCYFORMATS_V1 on CURRENCYFORMATS_V1.CURRENCYID = ACCOUNTLIST_V1.CURRENCYID inner join PAYEE_V1 on CHECKINGACCOUNT_V1.PAYEEID = PAYEE_V1.PAYEEID where PAYEENAME = 'Payee1' and CHECKINGACCOUNT_V1.TRANSCODE='Withdrawal' and CHECKINGACCOUNT_V1.STATUS <> 'V' group by year order by year desc; [/code] Lua [code] local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}; local colors = {"#FF6666", "#FFB266", "#FFFF66", "#B2FF66", "#66FF66", "#66FFB2", "#66FFFF", "#66B2FF", "#6666FF", "#B266FF", "#FF66FF", "#FF66B2"}; local data = ''; function handle_record(record) local color = colors[1 + (tonumber(record:get('YEAR')) % 12)]; record:set('COLOR', color); local datalimit = 12; if record:get("YEAR") >= os.date('%Y') then for i=12,os.date('%m'),-1 do if tonumber(record:get(months[i])) > 0 then datalimit = i; break; end end end data = data .. '{fillColor : "rgba(255,255,255,0)", strokeColor : "' .. color .. '", pointColor : "' .. color .. '",pointStrokeColor:"rgba(255,255,255,0)", data : ['; for i=1,12 do if i <= datalimit then local amount = string.format("%.2f", tonumber(record:get(months[i]))); if tonumber(string.sub(amount,-1)) == 0 and tonumber(string.sub(amount,-2)) ~= 0 then data = data .. '\'' .. amount .. '\''; else data = data .. amount; end if i ~= datalimit then data = data .. ','; end end end data = data .. ']},'; end function complete(result) result:set('TREND_DATA', string.sub(data,1,-1)); end [/code] Template [code] <!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> <STYLE><TMPL_INCLUDE NAME="master.css"></STYLE> <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT> </head> <body> <div class="container"> <h3>Payee Trend Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class "table-condensed"> <tr><td><canvas id="reportChart" width="750" height="300"></canvas></td></tr> <tr><td>&nbsp;</td></tr> <tr><td><table class="table"> <thead> <tr> <th>Year</th> <th class="text-right">Jan</th> <th class="text-right">Feb</th> <th class="text-right">Mar</th> <th class="text-right">Apr</th> <th class="text-right">May</th> <th class="text-right">Jun</th> <th class="text-right">Jul</th> <th class="text-right">Aug</th> <th class="text-right">Sep</th> <th class="text-right">Oct</th> <th class="text-right">Nov</th> <th class="text-right">Dec</th> </tr> </thead> <tbody> <TMPL_LOOP NAME=CONTENTS> <tr> <th bgcolor='<TMPL_VAR "COLOR">'><TMPL_VAR YEAR></th> <td class="money text-right"><TMPL_VAR Jan></td> <td class="money text-right"><TMPL_VAR Feb></td> <td class="money text-right"><TMPL_VAR Mar></td> <td class="money text-right"><TMPL_VAR Apr></td> <td class="money text-right"><TMPL_VAR May></td> <td class="money text-right"><TMPL_VAR Jun></td> <td class="money text-right"><TMPL_VAR Jul></td> <td class="money text-right"><TMPL_VAR Aug></td> <td class="money text-right"><TMPL_VAR Sep></td> <td class="money text-right"><TMPL_VAR Oct></td> <td class="money text-right"><TMPL_VAR Nov></td> <td class="money text-right"><TMPL_VAR Dec></td> </tr> </TMPL_LOOP> </tbody> </table></td></tr></table> </div> <table class="table"> <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"> <!-- Format numbers --> function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);} var elements= document.getElementsByClassName("money"); for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);} <!-- Chart --> 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> [/code]
Attachments
Payee Trend Report.grm
(2.4 KiB) Downloaded 570 times
Category Trend Report.grm
(2.21 KiB) Downloaded 618 times
Account Trend Report.grm
(2.08 KiB) Downloaded 603 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Trend Reports

Post by Nikolay »

Lua for Account Trend report

Code: Select all

local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local labels = '';
local data ='';
local json = [[
labels : [%s], 
datasets : [{
fillColor:"rgba(255,255,255,0)",
strokeColor:"rgba(0,0,0,0.5)",
pointColor:"rgba(0,0,0,0.5)",
pointStrokeColor:"rgba(255,255,255,0)",
data:[%s] }]
]];

function handle_record(record)
    local date = string.format('%s %s', months[tonumber(record:get("MONTH"))], record:get("YEAR"));
    labels = labels  .. string.format('"%s",', date);
    record:set("DATE", date);
    local balance = string.format("%.2f", tonumber(record:get("Balance")));
    if tonumber(string.sub(balance,-1)) == 0  and tonumber(string.sub(balance,-2)) ~= 0 then
        data = data .. '\'' .. balance .. '\',';
    else
        data = data .. balance .. ',';
    end
end

function complete(result)
    result:set('TREND_DATA', string.format(json, labels, data));
end
Post Reply