Reports

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

Moderator: Renato

Post Reply
alaintz
New MMEX User
Posts: 4
Joined: Sat Mar 07, 2015 4:27 pm
Are you a spam bot?: No

Reports

Post by alaintz »

Hi, I am quite new with MMEX (on MacAir Pro) which is already matching 99% of my needs. I miss a specific report and I am not familiar enough with SQL to create mine; if somebody could assist me ???? I need for a period of time ( let's say one specific year or one specific month or ....) the accounts balance history ( let's say each end of month when asking year history, each day when asking one specific month etc.. ) The data I need is the sum of all bank account balances ( current, CC ..). Possible to make it account specific ( like revenues/expenses existing report) Thanks for help !!
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Reports

Post by Nikolay »

It may be combination of this

Code: Select all

select t.id
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end as ID
, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID
, case when (julianday(TRANSDATE)- julianday('2015-01-01'))<0 then 0 else TRANSID end
, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE='Transfer') as t
inner join ACCOUNTLIST_V1 as a on a.ACCOUNTID=t.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c on a.CURRENCYID=c.CURRENCYID
where a.Status='Open'
and t.STATUS<>'V'
and a.accountname = 'BBP:1'
group by t.id
And

http://forum.moneymanagerex.org/viewtop ... =16&t=5909
Post Reply