Forecast 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

Forecast Reports

Post by siena123 »

Disclaimer: Forecast accuracy depends on correlating the method used to the data. These are only intended to be an example of some different methods and how they can be implemented.
  • Asset Forecast Report (uses the appreciation/depreciation rates entered for each asset)
  • [attachment=1]Asset Forecast Report.grm[/attachment] SQL [code] SELECT STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE FROM ASSETS_V1; [/code] Lua [code] local total = 0; local forecast = {0, 0, 0, 0, 0} local period = 5; function is_leap_year(year) local ly = 0; if year % 4 == 0 then if year % 100 == 0 then if year % 400 == 0 then ly = 1; end else ly = 1; end end return ly; end function get_days(month, year) local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 } local d = days[tonumber(month)]; if tonumber(month) == 2 and is_leap_year(year) ~= 0 then d = 29; end return d; end function get_years(record) local date = record:get("STARTDATE"); local aYear = string.sub(date,1,4); local aMonth = string.sub(date,6, 7); local aDay = string.sub(date,9); local cYear = os.date('%Y'); local cMonth = os.date('%m'); local cDay = os.date('%d'); local nbrYears = 0; if aYear == cYear then local days = 0; if aMonth == cMonth then days = cDay - aDay; else days = get_days(aMonth, aYear) - aDay; if tonumber(aMonth) + 1 > tonumber(cMonth) - 1 then for i = (aMonth + 1), (cMonth - 1) do days = days + get_days(i, aYear); end end days = days + cDay; end if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then nbrYears = days / 366; else nbrYears = days / 365; end else nbrYears = cYear - aYear; local days = get_days(aMonth, aYear) - aDay; for i = (aMonth + 1), 12 do days = days + get_days(i, aYear); end if tonumber(cMonth) > 1 then for i = 1, (cMonth - 1) do days = days + get_days(i, cYear); end end days = days + cDay; if tonumber(aMonth) < tonumber(cMonth) then nbrYears = nbrYears - 1; elseif aMonth == cMonth and tonumber(aDay) <= tonumber(cDay) then nbrYears = nbrYears - 1; end if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then nbrYears = nbrYears + (days / 366); else nbrYears = nbrYears + (days / 365); end end return nbrYears; end function handle_record(record) local value = record:get("VALUE"); local fvalue = {} for i = 1,period do fvalue[i] = value; end local change = record:get("VALUECHANGE"); if change ~= 'None' then local rate = record:get("VALUECHANGERATE") / 100; local years = get_years(record); local scale = 1; local fscale = {} for i = 1,period do fscale[i] = scale; end if change == 'Appreciates' then scale = math.pow(1 + rate, years); for i = 1,period do fscale[i] = math.pow(1 + rate, years + i); end elseif change == 'Depreciates' then scale = math.pow(1 - rate, years); for i = 1,period do fscale[i] = math.pow(1 - rate, years + i); end end value = value * scale; for i = 1,period do fvalue[i] = fvalue[i] * fscale[i]; end end total = total + value; for i = 1,period do forecast[i] = forecast[i] + fvalue[i]; end end function complete(result) local curYear = os.date('%Y'); local curMonth = os.date('%m'); local curDay = os.date('%d'); result:set("Current_Total", total); for i = 1,period do result:set("Year" .. i .. "_Total", forecast[i]); local date = os.date("*t", os.time{year=curYear+i,month=curMonth,day=curDay}); result:set("Year" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day)); end 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> <link href = "master.css" rel = "stylesheet"> </head> <body> <div class="container"> <h3>Asset Forecast Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class="table"> <thead> <tr> <th>Date</th> <th class="text-right">Value</th> </tr> </thead> <tbody> <tr> <td>Current</td> <td class="money text-right"><TMPL_VAR Current_Total></td> </tr> </tbody> <tfoot> <tr bgcolor='rgba(255,0,0,0.5)'> <td>1 year <TMPL_VAR Year1_Label></td> <td class="money text-right"><TMPL_VAR Year1_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>2 years <TMPL_VAR Year2_Label></td> <td class="money text-right"><TMPL_VAR Year2_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>3 years <TMPL_VAR Year3_Label></td> <td class="money text-right"><TMPL_VAR Year3_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>4 years <TMPL_VAR Year4_Label></td> <td class="money text-right"><TMPL_VAR Year4_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>5 years <TMPL_VAR Year5_Label></td> <td class="money text-right"><TMPL_VAR Year5_Total></td> </tr> </tfoot> </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);} </script> </html> [/code]
  • Account Forecast Report (uses data from repeating transactions)
  • [attachment=3]Account Forecast Report.grm[/attachment] SQL ([color=#FF0000]Update account name in line 24.[/color]) [code] select TRANSAMOUNT, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE, ( select INITIALBAL + total(TRANSAMOUNT) from (select ACCOUNTID, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, STATUS, TOTRANSAMOUNT from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer') as t where t.ACCOUNTID = a.ACCOUNTID and t.STATUS <> 'V') as Balance from (select ACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT from BILLSDEPOSITS_V1 union all select TOACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE, TOTRANSAMOUNT from BILLSDEPOSITS_V1 where TRANSCODE = 'Transfer') as b inner join ACCOUNTLIST_V1 as a on b.ACCOUNTID = a.ACCOUNTID where ACCOUNTNAME = 'Account1' and b.STATUS <> 'V'; [/code] Lua [code] local total = 0; local forecast = {} local period = 6; local initialized = 0; local repeatcode = 0; local numrepeats = 0; function is_leap_year(year) local ly = 0; if year % 4 == 0 then if year % 100 == 0 then if year % 400 == 0 then ly = 1; end else ly = 1; end end return ly; end function get_days(month, year) local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 } local d = days[tonumber(month)]; if tonumber(month) == 2 and is_leap_year(year) ~= 0 then d = 29; end return d; end function get_time_difference(year, month, day) local curYear = os.date('%Y'); local curMonth = os.date('%m'); local curDay = os.date('%d'); local d = day - curDay; local m = 0; if d < 0 then d = d + get_days(curMonth, curYear); m = -1; end m = m + month - curMonth; local y = 0; if m < 0 then m = m + 12; y = -1; end y = y + year - curYear; return {y, m, d} end function get_next_date(record, previousYear, previousMonth, previousDay) local originalnumber = tonumber(record:get("NUMOCCURRENCES")); -- Auto Execute User Acknowlegement required if repeatcode >= 100 then repeatcode = repeatcode - 100; end -- Auto Execute Silent mode if repeatcode >= 100 then repeatcode = repeatcode - 100; end if repeatcode ~= -1 then if repeatcode < 11 or repeatcode > 14 then numrepeats = numrepeats - 1; end end local nextDate= os.time{year=previousYear,month=previousMonth,day=previousDay} local updatedate = 0; if originalnumber == -1 then updatedate = 1; elseif numrepeats > 0 then updatedate = 1; end if updatedate ~= 0 then -- weekly if repeatcode == 1 then nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 7} -- biweekly elseif repeatcode == 2 then nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 14} -- month elseif repeatcode == 3 then nextDate = os.time{year=previousYear,month=previousMonth + 1,day=previousDay} -- bimonth elseif repeatcode == 4 then nextDate = os.time{year=previousYear,month=previousMonth + 2,day=previousDay} -- quarterly elseif repeatcode == 5 then nextDate = os.time{year=previousYear,month=previousMonth + 3,day=previousDay} -- half yearly elseif repeatcode == 6 then nextDate = os.time{year=previousYear,month=previousMonth + 6,day=previousDay} -- yearly elseif repeatcode == 7 then nextDate = os.time{year=previousYear + 1,month=previousMonth,day=previousDay} -- quad monthly elseif repeatcode == 8 then nextDate = os.time{year=previousYear,month=previousMonth + 4,day=previousDay} -- quad weekly elseif repeatcode == 9 then nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 28} -- daily elseif repeatcode == 10 then nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 1} -- repeat in X days or repeat in X months elseif repeatcode == 11 or repeatcode == 12 then if numrepeats ~= -1 then numrepeats = -1; end -- every X days elseif repeatcode == 13 then nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + numrepeats} -- every X months elseif repeatcode == 14 then nextDate = os.time{year=previousYear,month=previousMonth + numrepeats,day=previousDay} -- month last day or monthly last business day elseif repeatcode == 15 or repeatcode == 16 then local m = previousMonth + 1; local y = previousYear; if m > 12 then m = 1; y = y + 1; end nextDate = os.time{year=y,month=m,day=get_days(m, y)} -- monthly last business day if repeatcode == 16 then local n = os.date("*t", nextDate); -- Sunday if n.wday == 0 then nextDate = os.time{year=n.year,month=n.month,day=n.day - 2} -- Saturday elseif n.wday == 7 then nextDate = os.time{year=n.year,month=n.month,day=n.day - 1} end end end end local next = os.date("*t", nextDate); return {next.year, next.month, next.day} end function handle_record(record) if initialized == 0 then total = record:get("Balance"); for i=1,period do forecast[i] = total; end initialized = 1; end repeatcode = tonumber(record:get("REPEATS")); numrepeats = record:get("NUMOCCURRENCES"); local amount = record:get("TRANSAMOUNT"); local nextDate = record:get("NEXTOCCURRENCEDATE"); local nextYear = tonumber(string.sub(nextDate,1,4)); local nextMonth = tonumber(string.sub(nextDate,6, 7)); local nextDay = tonumber(string.sub(nextDate,9)); local timediff = get_time_difference(nextYear, nextMonth, nextDay); while (timediff[2] < period and timediff[1] == 0) or timediff[1] < 0 do local m = timediff[2] + 1; if timediff[1] < 0 then m = 1; end for i=m,period do forecast[i] = forecast[i] + amount; end local next = get_next_date(record, nextYear, nextMonth, nextDay); if next[1] == nextYear and next[2] == nextMonth and next[3] == nextDay then break; end nextYear = next[1]; nextMonth = next[2]; nextDay = next[3]; timediff = get_time_difference(nextYear, nextMonth, nextDay); end end function complete(result) local curYear = os.date('%Y'); local curMonth = os.date('%m'); local curDay = os.date('%d'); result:set("Current_Total", total); for i=1,period do result:set("Month" .. i .. "_Total", forecast[i]); local date = os.date("*t", os.time{year=curYear,month=curMonth+i,day=curDay}); result:set("Month" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day)); end 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> <link href = "master.css" rel = "stylesheet"> </head> <body> <div class="container"> <h3>Account Forecast Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class="table"> <thead> <tr> <th>Date</th> <th class="text-right">Value</th> </tr> </thead> <tbody> <tr> <td>Current</td> <td class="money text-right"><TMPL_VAR Current_Total></td> </tr> </tbody> <tfoot> <tr bgcolor='rgba(255,0,0,0.5)'> <td>1 month <TMPL_VAR Month1_Label></td> <td class="money text-right"><TMPL_VAR Month1_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>2 months <TMPL_VAR Month2_Label></td> <td class="money text-right"><TMPL_VAR Month2_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>3 months <TMPL_VAR Month3_Label></td> <td class="money text-right"><TMPL_VAR Month3_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>4 months <TMPL_VAR Month4_Label></td> <td class="money text-right"><TMPL_VAR Month4_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>5 months <TMPL_VAR Month5_Label></td> <td class="money text-right"><TMPL_VAR Month5_Total></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td>6 months <TMPL_VAR Month6_Label></td> <td class="money text-right"><TMPL_VAR Month6_Total></td> </tr> </tfoot> </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);} </script> </html> [/code]
  • Account Trend with Forecast Report (uses linear regression)
  • [attachment=2]Account Trend with Forecast Report.grm[/attachment] 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:['; -- forecast date local year = 0; local month = 0; -- Forecast line variables (Linear Regression) local count = 0; local count_sum = 0; local balance_sum = 0; local product_sum = 0; local squared_sum = 0; function handle_record(record) year = record:get("YEAR"); month = record:get("MONTH"); local date = months[tonumber(month)] .. ' ' .. 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 -- Update forecast line calculation values local x = count + 1; count = count + 1; count_sum = count_sum + x; balance_sum = balance_sum + balance; product_sum = product_sum + (x * balance); squared_sum = squared_sum + (x * x); end function complete(result) -- Forecast line equation local slope = ((count * product_sum) - (count_sum * balance_sum)) / ((count * squared_sum) - (count_sum * count_sum)); local intercept = (balance_sum - (slope * count_sum)) / count; -- Forecast line values data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(255,0,0,0.5)",pointColor:"rgba(255,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:['; for i=1,count do data = data .. ','; end for i=1,12 do -- Label month = month + 1; if month > 12 then year = year + 1; month = 1; end local date = months[tonumber(month)] .. ' ' .. year; labels = labels .. '"' .. date .. '",'; result:set('FLABEL' .. i, date); -- Value local value = (slope * (count + i)) + intercept; local fval = string.format("%.2f", value); if tonumber(string.sub(fval,-1)) == 0 and tonumber(string.sub(fval,-2)) ~= 0 then data = data .. '\'' .. fval .. '\','; else data = data .. fval .. ','; end result:set('FVALUE' .. i, string.format("%.2f", value)); end 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> <link href = "master.css" rel = "stylesheet"> <script src="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> <tfoot> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL1></td> <td class="money text-right"><TMPL_VAR FVALUE1></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL2></td> <td class="money text-right"><TMPL_VAR FVALUE2></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL3></td> <td class="money text-right"><TMPL_VAR FVALUE3></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL4></td> <td class="money text-right"><TMPL_VAR FVALUE4></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL5></td> <td class="money text-right"><TMPL_VAR FVALUE5></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL6></td> <td class="money text-right"><TMPL_VAR FVALUE6></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL7></td> <td class="money text-right"><TMPL_VAR FVALUE7></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL8></td> <td class="money text-right"><TMPL_VAR FVALUE8></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL9></td> <td class="money text-right"><TMPL_VAR FVALUE9></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL10></td> <td class="money text-right"><TMPL_VAR FVALUE10></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL11></td> <td class="money text-right"><TMPL_VAR FVALUE11></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL12></td> <td class="money text-right"><TMPL_VAR FVALUE12></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).Line(data); </script> </html> [/code]
  • Category Trend with Forecast Report (uses historical average)
  • [attachment=0]Category Trend with Forecast Report.grm[/attachment] 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 total = 0; -- forecast date local year = 0; local month = 0; -- Forecast line variables (Average) local average = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}; local count = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}; function handle_record(record) year = record:get("YEAR"); month = record:get("MONTH"); local date = months[tonumber(month)] .. ' ' .. 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 total = total + 1; -- Update forecast line calculation values count[tonumber(month)] = count[tonumber(month)] + 1; average[tonumber(month)] = average[tonumber(month)] + amount; end function complete(result) -- Forecast line values data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,0,0,0.5)",strokeColor:"rgba(255,0,0,0.5)",data:['; for i=1,total do data = data .. ','; end for i=1,12 do -- Label month = month + 1; if month > 12 then year = year + 1; month = 1; end local date = months[tonumber(month)] .. ' ' .. year; labels = labels .. '"' .. date .. '",'; result:set('FLABEL' .. i, date); -- Value local value = "0"; if (count[tonumber(month)] > 0) then value = string.format("%.2f", average[tonumber(month)] / count[tonumber(month)]); end local fval = string.format("%.2f", value); if tonumber(string.sub(fval,-1)) == 0 and tonumber(string.sub(fval,-2)) ~= 0 then data = data .. '\'' .. fval .. '\','; else data = data .. fval .. ','; end result:set('FVALUE' .. i, string.format("%.2f", value)); end 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 bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL1></td> <td class="money text-right"><TMPL_VAR FVALUE1></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL2></td> <td class="money text-right"><TMPL_VAR FVALUE2></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL3></td> <td class="money text-right"><TMPL_VAR FVALUE3></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL4></td> <td class="money text-right"><TMPL_VAR FVALUE4></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL5></td> <td class="money text-right"><TMPL_VAR FVALUE5></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL6></td> <td class="money text-right"><TMPL_VAR FVALUE6></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL7></td> <td class="money text-right"><TMPL_VAR FVALUE7></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL8></td> <td class="money text-right"><TMPL_VAR FVALUE8></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL9></td> <td class="money text-right"><TMPL_VAR FVALUE9></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL10></td> <td class="money text-right"><TMPL_VAR FVALUE10></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL11></td> <td class="money text-right"><TMPL_VAR FVALUE11></td> </tr> <tr bgcolor='rgba(255,0,0,0.5)'> <td><TMPL_VAR FLABEL12></td> <td class="money text-right"><TMPL_VAR FVALUE12></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]
Attachments
Category Trend with Forecast Report.grm
(2.61 KiB) Downloaded 597 times
Asset Forecast Report.grm
(2.21 KiB) Downloaded 583 times
Account Trend with Forecast Report.grm
(2.66 KiB) Downloaded 545 times
Account Forecast Report.grm
(2.99 KiB) Downloaded 780 times
danyall
New MMEX User
Posts: 5
Joined: Mon Mar 31, 2014 3:54 pm
Are you a spam bot?: No

Re: Forecast Reports

Post by danyall »

Hello everybody!

I had imported the report but i get the following error: "could not open file for reading" After that, the program crash.

This is the version:
MMEX is using the following support products:
wxWidgets 3.0.0
SQLite3 3.8.4.1
wxSQLite3 3.0.6
Microsoft Visual Studio 1800

I think could be related with paramters ...
Can you help me?
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Forecast Reports

Post by Renato »

chance in Html

Code: Select all

<link href = "master.css" rel = "stylesheet">
Renato Forum Administrator
danyall
New MMEX User
Posts: 5
Joined: Mon Mar 31, 2014 3:54 pm
Are you a spam bot?: No

Re: Forecast Reports

Post by danyall »

Thank you!! It's working fine!
Conrado
New MMEX User
Posts: 5
Joined: Thu Jun 26, 2014 4:47 pm
Are you a spam bot?: No

Re: Forecast Reports

Post by Conrado »

Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Forecast Reports

Post by Nikolay »

Conrado wrote:Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
No ideas, could you provide screenshot?
User avatar
GabrieleV
MMEX Developer
Posts: 158
Joined: Wed Apr 09, 2014 7:45 pm
Are you a spam bot?: No
Location: Italy

Re: Forecast Reports

Post by GabrieleV »

http://forum.moneymanagerex.org/viewtop ... =16&t=5455

Replace style and script at the top of HTML as described and it will work.
Conrado
New MMEX User
Posts: 5
Joined: Thu Jun 26, 2014 4:47 pm
Are you a spam bot?: No

Re: Forecast Reports

Post by Conrado »

ScreenShot996.jpg
ScreenShot996.jpg (6.81 KiB) Viewed 7936 times
Nikolay wrote:
Conrado wrote:Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?
No ideas, could you provide screenshot?
Hi thanks, I all ready make it work coping and pasting the code from forum.
  • - I added the print screen with the error. This error occurs is when import the report.
  • - Replacing the code from the html it didn't work i could not find this line "<SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT>" to replaced.
Thanks Conrado
Attachments
ScreenShot996.jpg
ScreenShot996.jpg (6.81 KiB) Viewed 7936 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: Forecast Reports

Post by Nikolay »

Line 6 of template should be 

Code: Select all

<link href = "master.css" rel = "stylesheet">
Attachments
Account Forecast Report.grm
(3.02 KiB) Downloaded 562 times
omalleypat
Super MMEX User
Posts: 112
Joined: Tue Jul 28, 2009 10:34 pm
Are you a spam bot?: No
Location: Atchison, KS

Re: Forecast Reports

Post by omalleypat »

siena123 wrote:
  • Account Forecast Report (uses data from repeating transactions)
FYI...this report does not seem to include data from split transactions.
Post Reply