sum up different bank account
Moderator: Renato
sum up different bank account
Hello
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: sum up different bank account
This report shown accounts balanses
http://forum.moneymanagerex.org/viewtop ... =16&t=5439
http://forum.moneymanagerex.org/viewtop ... =16&t=5439
Re: sum up different bank account
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
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
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
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
nuvito
Re: sum up different bank account
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;
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;
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: sum up different bank account
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;
- Renato
- MVP User
- Posts: 674
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: sum up different bank account
Hi Nikolay
Thank you very much, nice evaluation.
However, I miss the conversion of foreign currency into the base currency and the final total.
Thank you very much, nice evaluation.
However, I miss the conversion of foreign currency into the base currency and the final total.
Renato Forum Administrator
Re: sum up different bank account
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
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
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: sum up different bank account
Try this report. It's needed some formating...
- Attachments
-
- Test_BBP.grm
- (1.84 KiB) Downloaded 815 times
Re: sum up different bank account
Wonderful.
It was really what I wanted
thank you very much.
DMLeal
It was really what I wanted
thank you very much.
DMLeal
- Renato
- MVP User
- Posts: 674
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: sum up different bank account
Hi Nikolay
Thanks, this is precisely the evaluation I needed.
However, I have in SQL
order by ACCOUNT NAME;
by
order by HELDAT,ACCOUNT NAME;
replaced.
And in html
<th class = "i18n"> Balance </th>
<th class = "i18n"> Conv rate </th>
<th class = "i18n"> Balance </th>
by
<th class = "text-right"> Balance </th>
<th class = "text-right"> Conv rate </th>
<th class = "text-right"> Balance </th>
replaced.
Thanks, this is precisely the evaluation I needed.
However, I have in SQL
order by ACCOUNT NAME;
by
order by HELDAT,ACCOUNT NAME;
replaced.
And in html
<th class = "i18n"> Balance </th>
<th class = "i18n"> Conv rate </th>
<th class = "i18n"> Balance </th>
by
<th class = "text-right"> Balance </th>
<th class = "text-right"> Conv rate </th>
<th class = "text-right"> Balance </th>
replaced.
Renato Forum Administrator
- Renato
- MVP User
- Posts: 674
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: sum up different bank account
and a.accountname like 'CS%' or a.accountname like 'SG%' or a.accountname like '.%' or a.accountname like 'Spar%' or a.accountname like 'Post%'
list full names for all needed accounts
Code: Select all
and a.accountname in( 'CS..', 'SG..' , 'Spar..', 'Post..')
Renato Forum Administrator