Budget Report

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

Moderator: Renato

Post Reply
jaywhy
New MMEX User
Posts: 3
Joined: Fri Sep 01, 2017 6:49 am
Are you a spam bot?: No

Budget Report

Post by jaywhy »

I have a sql query which returns a list of categories and their budget and actual amounts.

Code: Select all

select
ifnull(CategoryName,(select categname from CATEGORY_V1 where ifnull(CategoryName,rtrim(btj,-1)) = categid)) Category
,abs(ifnull(Budget,0)) Budget
,case when CategoryName like 'Income%' then abs(ifnull(Actual,0)) else ifnull(Actual,0) end Actual
,abs(ifnull(Budget,0))-case when CategoryName like 'Income%' then abs(ifnull(Actual,0)) else ifnull(Actual,0) end Difference
from
(select
c.catjoin ccj
,bt.catjoin btj
,CategoryName
,Budget
,Actual
from
(select
ca.categid||ifnull(s.subcategid,'-1') catjoin
,case ifnull(subcategname,'') when '' then categname else categname||' : '||subcategname end CategoryName
from CATEGORY_V1 ca
left join SUBCATEGORY_V1 s on ca.categid=s.categid) c
left join
(select
ifnull(bj,tj) catjoin
,Budget
,Actual
from
(select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select 
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
left join
   (select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then  splittransamount else  transamount end) else case che.categid when -1 then  splittransamount else  transamount end end) Actual
   from checkingaccount_v1 che
      left join splittransactions_v1 s on s.transid=che.transid
   group by categ||subcateg) t
using (joiner)
union all
select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
   (select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then  splittransamount else  transamount end) else case che.categid when -1 then  splittransamount else  transamount end end) Actual
   from checkingaccount_v1 che
      left join splittransactions_v1 s on s.transid=che.transid
   group by categ||subcateg) t
left join
(select 
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
using (joiner)
where b.joiner is null)) bt
using (catjoin)
union all
select
c.catjoin ccj
,bt.catjoin btj
,CategoryName
,Budget
,Actual
from
(select
ifnull(bj,tj) catjoin
,Budget
,Actual
from
(select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
(select 
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
left join
   (select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then  splittransamount else  transamount end) else case che.categid when -1 then  splittransamount else  transamount end end) Actual
   from checkingaccount_v1 che
      left join splittransactions_v1 s on s.transid=che.transid
   group by categ||subcateg) t
using (joiner)
union all
select
b.joiner bj
,t.joiner tj
,Budget
,Actual
from
   (select
case ifnull(che.categid, -1) when -1 then s.categid else che.categid end as categ
, case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end as subcateg
, case ifnull(che.categid, -1) when -1 then s.categid else che.categid end||case ifnull(che.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(che.subcategid,-1) end joiner
, sum(case transcode when 'Deposit' then 0-(case che.categid when -1 then  splittransamount else  transamount end) else case che.categid when -1 then  splittransamount else  transamount end end) Actual
   from checkingaccount_v1 che
      left join splittransactions_v1 s on s.transid=che.transid
   group by categ||subcateg) t
left join
(select 
categid categ
,subcategid subcateg
,categid||subcategid joiner
,amount Budget
from BUDGETTABLE_V1) b
using (joiner)
where b.joiner is null)) bt
left join
(select
ca.categid||ifnull(s.subcategid,'-1') catjoin
,case ifnull(subcategname,'') when '' then categname else categname||' : '||subcategname end CategoryName
from CATEGORY_V1 ca
left join SUBCATEGORY_V1 s on ca.categid=s.categid) c
using (catjoin)
where c.catjoin is null)
where Category != 'Transfer'
and Category != 'Budget S/D : Z'
order by Category
Is there a way with Lua or something else to split the results into two tables on the output - one for expenses and one for income based on category name starts with x? I am new to MMEX and have never come accross Lua before so please excuse my ignorance!
jaywhy
New MMEX User
Posts: 3
Joined: Fri Sep 01, 2017 6:49 am
Are you a spam bot?: No

Re: Budget Report

Post by jaywhy »

Further to the above, I have found an html template which means I could potentially manipulate the results of a sql query using javascript (which is perfect for me) from this post: viewtopic.php?f=16&t=5521&p=10890&hilit=json#p10890. However, the tmpl_var JSONCONTENTS is not returning the results of my sql query. Has the variable name changed or am I misunderstanding this?

Please someone help me...!
jaywhy
New MMEX User
Posts: 3
Joined: Fri Sep 01, 2017 6:49 am
Are you a spam bot?: No

Re: Budget Report

Post by jaywhy »

I am using MMEX version 1.3.3 on Windows 10 if that helps. I struggle to believe that nobody knows the answer to this question!!
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Budget Report

Post by Nikolay »

I can help.
Post Reply