sum up different bank account

12 posts
sum up different bank account

dmleal
New MMEX User

Posts: 7
Joined: Sun Jan 25, 2015 2:46 pm
Hello
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you
Re: sum up different bank account

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
This report shown accounts balanses
viewtopic.php?f=16&t=5439
Re: sum up different bank account

dmleal
New MMEX User

Posts: 7
Joined: Sun Jan 25, 2015 2:46 pm
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
Re: sum up different bank account

nuvito
New MMEX User

Posts: 17
Joined: Mon Jun 11, 2012 11:15 am
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
Re: sum up different bank account

dmleal
New MMEX User

Posts: 7
Joined: Sun Jan 25, 2015 2:46 pm
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;
Re: sum up different bank account

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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;
Re: sum up different bank account
User avatar
Renato
Super MMEX User

Posts: 106
Joined: Mon Apr 25, 2011 1:36 pm
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
Re: sum up different bank account

dmleal
New MMEX User

Posts: 7
Joined: Sun Jan 25, 2015 2:46 pm
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
Re: sum up different bank account

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Try this report. It's needed some formating...
Attachments
Test_BBP.grm
(1.84 KiB) Downloaded 298 times
Re: sum up different bank account

dmleal
New MMEX User

Posts: 7
Joined: Sun Jan 25, 2015 2:46 pm
Wonderful. :D
It was really what I wanted :lol:
thank you very much.

DMLeal
Who is online

Users browsing this forum: No registered users and 2 guests

cron