sum up different bank account

[dmleal]

Hello
Is there any way to sum up different bank accounts through General Reports Manager?
Thank you

[Nikolay]

This report shown accounts balanses
http://forum.moneymanagerex.org/viewtop ... =16&t=5439

[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]

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

[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;

[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;

[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.

[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

[Nikolay]

Try this report. It's needed some formating...

[dmleal]

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

DMLeal

[Renato]

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.

[Renato]

and a.accountname like 'CS%' or a.accountname like 'SG%' or a.accountname like '.%' or a.accountname like 'Spar%' or a.accountname like 'Post%'

Code: Select all

and a.accountname in( 'CS..', 'SG..' , 'Spar..', 'Post..')

list full names for all needed accounts