sum up different bank account

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)

Moderator: Renato

dmleal
New MMEX User
Posts: 6
Joined: Sun Jan 25, 2015 8:46 pm
Are you a spam bot?: No

sum up different bank account

Post by dmleal »

Hello
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: sum up different bank account

Post by Nikolay »

This report shown accounts balanses
http://forum.moneymanagerex.org/viewtop ... =16&t=5439
dmleal
New MMEX User
Posts: 6
Joined: Sun Jan 25, 2015 8:46 pm
Are you a spam bot?: No

Re: sum up different bank account

Post by dmleal »

What I wanted was to add different accounts that have in the same bank
for example
Bank_1: Account 1
Bank_1: Account 2
Bank_2: Account 1
Bank_2: Account 2
I just wanted to calculate the sum total of the accounts bank_1

How can I do it

thank you
nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Re: sum up different bank account

Post by nuvito »

Hi,

as a short hint: the name of the bank can be set in the field "HeldAt" of the account. So, you can group by this field instead of the "AccountName".

HTH
Kind regards,

nuvito
dmleal
New MMEX User
Posts: 6
Joined: Sun Jan 25, 2015 8:46 pm
Are you a spam bot?: No

Re: sum up different bank account

Post by dmleal »

Yes, thanks for the help

but with this code can view these accounts, but how do I know their total value? :roll:
--------------------------------------------------------------------------------------------------------------
replace 'HELDAT' by desired value

select a.ACCOUNTNAME, a. Heldat, a. Status
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID, TRANSDATE, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, TRANSDATE, 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 Heldat in ('HELDAT') And a.Status='Open'
and t.STATUS<>'V'
group by a.ACCOUNTID
order by ACCOUNTNAME;
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: sum up different bank account

Post by Nikolay »

Code: Select all

select a.ACCOUNTNAME, a. Heldat, a. Status
, a.INITIALBAL + total(t.TRANSAMOUNT) as Balance
from
(select ACCOUNTID, TRANSDATE, STATUS,
(case when TRANSCODE='Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, TRANSDATE, 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 like 'Bank_1%'
group by a.ACCOUNTID
order by ACCOUNTNAME;
User avatar
Renato
MVP MMEX User
Posts: 213
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich
Contact:

Re: sum up different bank account

Post by Renato »

Hi Nikolay

Thank you very much, nice evaluation.

However, I miss the conversion of foreign currency into the base currency and the final total.
regards Renato use MMEX since 2009
dmleal
New MMEX User
Posts: 6
Joined: Sun Jan 25, 2015 8:46 pm
Are you a spam bot?: No

Re: sum up different bank account

Post by dmleal »

Thank you for your reply.
I leave a image below as an example of what I want to do.
Please help me because I am not an expert on the matter :roll:

Link for the image: https://www.dropbox.com/s/1h6a42p3cbc2f ... 1.png?dl=0

Thanks
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: sum up different bank account

Post by Nikolay »

Try this report. It's needed some formating...
Attachments
Test_BBP.grm
(1.84 KiB) Downloaded 605 times
dmleal
New MMEX User
Posts: 6
Joined: Sun Jan 25, 2015 8:46 pm
Are you a spam bot?: No

Re: sum up different bank account

Post by dmleal »

Wonderful. :D
It was really what I wanted :lol:
thank you very much.

DMLeal
Post Reply