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
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?
--------------------------------------------------------------------------------------------------------------
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
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.
It was really what I wanted
thank you very much.
DMLeal