Hi!
I was wondering is its possible to including some type of logic in custom reports. I guess something like this is accomplished through the Lua interface. The report I want to build cannot be produced using a single SQL query. I looks like I will need either two queries or some sort of a super-query which I will have to programmatically handle and create a new 'output' record set. Is it possible to accomplish something like this?
Thanks
Dimitrios
Question about embedding logic in custom reports
Moderator: Renato
-
- Developer
- Posts: 382
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Question about embedding logic in custom reports
First, let's call it general report which is based on sql/html/css/js and template engining.
You can see the underly logic on this site with some descriptions and simple samples (https://github.com/moneymanagerex/general-reports)
Basically, now we only support one single SQL.
You can see the underly logic on this site with some descriptions and simple samples (https://github.com/moneymanagerex/general-reports)
Basically, now we only support one single SQL.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Question about embedding logic in custom reports
Thanks for the reply guanlisheng! I have seen the samples and the general descriptions, which are very helpful for a quick start on general report. While I can handle really well the SQL and adequately html and js, css and lua are really new to me. I'll keep on researching on those two. In the mean time I made "my averages report" a lot more accurate as far as account activity is concerned, but I had to manually create some views in the database.guanlisheng wrote:First, let's call it general report which is based on sql/html/css/js and template engining.
You can see the underly logic on this site with some descriptions and simple samples (https://github.com/moneymanagerex/general-reports)
Basically, now we only support one single SQL.
Thanks again
Dimitrios
-
- Developer
- Posts: 382
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Question about embedding logic in custom reports
Hi Dimitrios, the manual created views is not recommended since it will make your general report more complex.
i just reviewed you SQL deeply and found we still can do something to simplify itself. the key is to connect
note that [Average Spending] can be calculated in lua
i just reviewed you SQL deeply and found we still can do something to simplify itself. the key is to connect
Code: Select all
select ifnull(split.CATEGID, tran.CATEGID) as CATEGID, c.CATEGNAME as Category
, ifnull(split.SUBCATEGID, tran.SUBCATEGID) as SUBCATEGID, s.SUBCATEGNAME as Subcategory
, sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) as [Total Spend]
, count(distinct strftime('%m', tran.TRANSDATE)) as [Months Active]
, sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) / count(distinct strftime('%m', tran.TRANSDATE)) as [Average Spending]
from CHECKINGACCOUNT_V1 as tran
left join SPLITTRANSACTIONS_V1 as split on tran.TRANSID = split.TRANSID
join CATEGORY_V1 as c on (c.CATEGID = ifnull(split.CATEGID, tran.CATEGID))
join SUBCATEGORY_V1 as s on (s.CATEGID = c.CATEGID and s.SUBCATEGID = ifnull(split.SUBCATEGID, tran.SUBCATEGID))
where tran.transcode = 'Withdrawal'
and tran.payeeid != 15
group by Category, Subcategory
;
Code: Select all
function handle_record(record)
record:set("Average Spending", record:get("Total Spend") / record:get("Months Active"));
end
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Question about embedding logic in custom reports
Thanks so much for the suggestion and the Lua example!guanlisheng wrote:Hi Dimitrios, the manual created views is not recommended since it will make your general report more complex.
i just reviewed you SQL deeply and found we still can do something to simplify itself. the key is to connect
note that [Average Spending] can be calculated in luaCode: Select all
select ifnull(split.CATEGID, tran.CATEGID) as CATEGID, c.CATEGNAME as Category , ifnull(split.SUBCATEGID, tran.SUBCATEGID) as SUBCATEGID, s.SUBCATEGNAME as Subcategory , sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) as [Total Spend] , count(distinct strftime('%m', tran.TRANSDATE)) as [Months Active] , sum(ifnull(split.SPLITTRANSAMOUNT, tran.TRANSAMOUNT)) / count(distinct strftime('%m', tran.TRANSDATE)) as [Average Spending] from CHECKINGACCOUNT_V1 as tran left join SPLITTRANSACTIONS_V1 as split on tran.TRANSID = split.TRANSID join CATEGORY_V1 as c on (c.CATEGID = ifnull(split.CATEGID, tran.CATEGID)) join SUBCATEGORY_V1 as s on (s.CATEGID = c.CATEGID and s.SUBCATEGID = ifnull(split.SUBCATEGID, tran.SUBCATEGID)) where tran.transcode = 'Withdrawal' and tran.payeeid != 15 group by Category, Subcategory ;
Code: Select all
function handle_record(record) record:set("Average Spending", record:get("Total Spend") / record:get("Months Active")); end
The problem, though, is that the above SQL code does not compute the correct average as it does not take into account the period (max date per category/subcategory - min date per category/subcategory) in which the expenses took place. In order to make the calculation more accurate (based in the actual activity), then either the SQL query would be more elaborate or you have to build more complex SQL code , such as iterating of the SQL recordset and find the actual number of days of activity. Currently, I was able to implement a more "fair" calculation by I had to implement my own views, which honestly I believe it would be a good idea to be included in the original database design.
Let me know if you need me to elaborate on this and/or send you the complete report implementation. Thanks again
Dimitrios
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: Question about embedding logic in custom reports
I've attached updated report
http://www.codelathe.com/forum/viewtopi ... 6&start=10
Please learn and update as you wish.
http://www.codelathe.com/forum/viewtopi ... 6&start=10
Please learn and update as you wish.
Re: Question about embedding logic in custom reports
Thank you very very much, Nikolay! I will study it very carefullyNikolay wrote:I've attached updated report
http://www.codelathe.com/forum/viewtopi ... 6&start=10
Please learn and update as you wish.