Page 1 of 2

sum up different bank account

Posted: Sun Jan 25, 2015 8:48 pm
by dmleal
Hello
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you

Re: sum up different bank account

Posted: Mon Jan 26, 2015 12:18 pm
by Nikolay
This report shown accounts balanses
http://forum.moneymanagerex.org/viewtop ... =16&t=5439

Re: sum up different bank account

Posted: Mon Jan 26, 2015 4:04 pm
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

Re: sum up different bank account

Posted: Tue Jan 27, 2015 6:14 am
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

Re: sum up different bank account

Posted: Tue Jan 27, 2015 11:59 am
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;

Re: sum up different bank account

Posted: Tue Jan 27, 2015 3:20 pm
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;

Re: sum up different bank account

Posted: Tue Jan 27, 2015 3:50 pm
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.

Re: sum up different bank account

Posted: Tue Jan 27, 2015 6:04 pm
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

Re: sum up different bank account

Posted: Wed Jan 28, 2015 1:31 pm
by Nikolay
Try this report. It's needed some formating...

Re: sum up different bank account

Posted: Wed Jan 28, 2015 7:12 pm
by dmleal
Wonderful. :D
It was really what I wanted :lol:
thank you very much.

DMLeal