Page 1 of 1

Forecast Reports

Posted: Sat Mar 01, 2014 3:13 pm
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]

Re: Forecast Reports

Posted: Mon Mar 31, 2014 3:58 pm
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?

Re: Forecast Reports

Posted: Mon Mar 31, 2014 4:07 pm
by Renato
chance in Html

Code: Select all

<link href = "master.css" rel = "stylesheet">

Re: Forecast Reports

Posted: Mon Mar 31, 2014 5:35 pm
by danyall
Thank you!! It's working fine!

Re: Forecast Reports

Posted: Thu Jun 26, 2014 4:55 pm
by Conrado
Hi, when i execute the Account Forecast Report.grm shows "Block not terminated". Any advice?

Re: Forecast Reports

Posted: Thu Jun 26, 2014 6:17 pm
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?

Re: Forecast Reports

Posted: Sat Jun 28, 2014 9:08 am
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.

Re: Forecast Reports

Posted: Fri Jul 04, 2014 2:23 pm
by Conrado
ScreenShot996.jpg
ScreenShot996.jpg (6.81 KiB) Viewed 7940 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

Re: Forecast Reports

Posted: Fri Jul 04, 2014 9:04 pm
by Nikolay
Line 6 of template should be 

Code: Select all

<link href = "master.css" rel = "stylesheet">

Re: Forecast Reports

Posted: Sat Jul 19, 2014 9:20 pm
by omalleypat
siena123 wrote:
  • Account Forecast Report (uses data from repeating transactions)
FYI...this report does not seem to include data from split transactions.