Question about embedding logic in custom reports

Page 1 of 1
7 posts
Question about embedding logic in custom reports

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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
Re: Question about embedding logic in custom reports

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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.
Re: Question about embedding logic in custom reports

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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
Re: Question about embedding logic in custom reports

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
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
Re: Question about embedding logic in custom reports

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
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
Re: Question about embedding logic in custom reports

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I've attached updated report
viewtopic.php?f=16&t=5446&start=10
Please learn and update as you wish.
Re: Question about embedding logic in custom reports

dimidimi
Super MMEX User

Posts: 43
Joined: Tue Mar 04, 2014 4:46 am
Nikolay wrote:I've attached updated report
viewtopic.php?f=16&t=5446&start=10
Please learn and update as you wish.


Thank you very very much, Nikolay! I will study it very carefully :)
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest