Question about embedding logic in custom reports

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

Moderator: Renato

Post Reply
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Question about embedding logic in custom reports

Post by dimidimi »

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
guanlisheng
MMEX Developer
Posts: 375
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

Post by guanlisheng »

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.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Question about embedding logic in custom reports

Post by dimidimi »

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 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.

Thanks again
Dimitrios
guanlisheng
MMEX Developer
Posts: 375
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

Post by guanlisheng »

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

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
;
note that [Average Spending] can be calculated in lua

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)
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Question about embedding logic in custom reports

Post by dimidimi »

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

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
;
note that [Average Spending] can be calculated in lua

Code: Select all

function handle_record(record)
    record:set("Average Spending", record:get("Total Spend") / record:get("Months Active"));
end
Thanks so much for the suggestion and the Lua example!

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
Nikolay
MMEX 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

Post by Nikolay »

I've attached updated report
http://www.codelathe.com/forum/viewtopi ... 6&start=10
Please learn and update as you wish.
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Question about embedding logic in custom reports

Post by dimidimi »

Nikolay wrote:I've attached updated report
http://www.codelathe.com/forum/viewtopi ... 6&start=10
Please learn and update as you wish.
Thank you very very much, Nikolay! I will study it very carefully :)
Post Reply