Show Currencies in Reports

[aercan]

Hi everyone. I need help about showing currency total in my reports.
I have TL, $, €, £ as cash payment options. As you can see from picture below, all my foreign currency payments are converted to TL. I want to see them as foreign currency.

The report line for picture is also below.

>Income Totals||
Cash||{ACCOUNT DEBIT TOTAL:Cash}
Dollar||{ACCOUNT DEBIT TOTAL:Dollar Cash}
GBP||{ACCOUNT DEBIT TOTAL:Gbp Cash}
Euro||{ACCOUNT DEBIT TOTAL:Euro Cash}
Credit Card||{ACCOUNT DEBIT TOTAL:Credit Card}

I tried to check forum and could not figure it out. Thank you for help.

[Posflow]

@aercan

>Income Totals||
Cash||{ACCOUNT DEBIT TOTAL:Cash}
Dollar||{ACCOUNT DEBIT TOTAL:Dollar Cash}
GBP||{ACCOUNT DEBIT TOTAL:Gbp Cash}
Euro||{ACCOUNT DEBIT TOTAL:Euro Cash}
Credit Card||{ACCOUNT DEBIT TOTAL:Credit Card}

Like this?

>Income Totals||
Cash||{ACCOUNT DEBIT TOTAL:Cash}
Dollar||$ {ACCOUNT DEBIT TOTAL:Dollar Cash}
GBP||£  {ACCOUNT DEBIT TOTAL:Gbp Cash}
Euro||€ {ACCOUNT DEBIT TOTAL:Euro Cash}
Credit Card||{ACCOUNT DEBIT TOTAL:Credit Card}

[aercan]

Hi friend. No, this is only adding symbols before payments. $,€,£ still converted to TL in report.
What i need is i want to see the amount of foreign currency in reports. The picture that i gave shows me TL exchange of currencies. Report need to be something like this ;

>Income Totals||
Cash||{ACCOUNT DEBIT TOTAL:Cash}
Dollar||$ {ACCOUNT DEBIT TOTAL:Dollar Cash}  **648TL / Dollar rate = The currency in my case**
GBP||£  {ACCOUNT DEBIT TOTAL:Gbp Cash} **605TL / GBP rate = The currency in my case**
Euro||€ {ACCOUNT DEBIT TOTAL:Euro Cash} **787.50TL / Euro rate = The currency in my case**
Credit Card||{ACCOUNT DEBIT TOTAL:Credit Card} 

I need this because i am accepting foreign currencies but at the end of day i do not know how much dollar, euro, pound i got in my case.
Thank you for your help.

[Posflow]

How does your setup work? You have different payment types per currency or you have different currency for same payment type?

[aercan]

Our local currency is TL. So i have diffrent currency for same payment type. if a table bill is 100TL, i have option for customer to pay this 100tl exchange of $,€,£. I got some help with this line and this is what i want ;

>Income Total||
TL||{ACCOUNT DEBIT TOTAL:Cash}
Dolar||[=F(TN('{ACCOUNT DEBIT TOTAL:Dolar Cash}')/TN('{EXCHANGE RATE:Dolar}'),'#,##0.##')]
GBP||[=F(TN('{ACCOUNT DEBIT TOTAL:Gbp Cash}')/TN('{EXCHANGE RATE:Gbp}'),'#,##0.##')]
Euro||[=F(TN('{ACCOUNT DEBIT TOTAL:Euro Cash}')/TN('{EXCHANGE RATE:Euro}'),'#,##0.##')]

Even the names are correct, data is empty.

[ilyas]

>Gelirler Toplam||
TL||{ACCOUNT DEBIT TOTAL:Nakit}
Dolar||[=F(TN('{ACCOUNT DEBIT TOTAL:Dolar Nakit}')/TN('{REPORT SQL DETAILS:SELECT ExchangeRate FROM ForeignCurrencies Where Name = 'Dolar' :F.ExchangeRate}'))]
GBP||[=F(TN('{ACCOUNT DEBIT TOTAL:Gbp Nakit}')/TN('{REPORT SQL DETAILS:SELECT ExchangeRate FROM ForeignCurrencies Where Name = 'Gbp' :F.ExchangeRate}'))]
Euro||[=F(TN('{ACCOUNT DEBIT TOTAL:Euro Nakit}')/TN('{REPORT SQL DETAILS:SELECT ExchangeRate FROM ForeignCurrencies Where Name = 'Euro' :F.ExchangeRate}'))]
Credit Card||{ACCOUNT DEBIT TOTAL:Credit Card}

[ilyas]

@QMcKay

Report Tags seem to give values in Default currency. And Reports have no access to the {EXCHANGE RATE:X} tag either. So in my case, I need to store the XR in a Program Setting like {SETTING:XR_USDtoHNL}, then do this:

…

[ilyas]

[Castech]

Did you manage? I use a similar setup, I can help you. I have different rates for each payment type. I created a multi currency report which shows me totals already converted to foreign currency.

[jerryfin]

@Castech I have a similar setup trying to create work period reports with correct change type.

[MarkNarsingh]

Hi there, i read that you have different rates for eacht payment type…can you help with the multi currency report?

thanks in advance

[MarkNarsingh]

Hi there, did you manage to create work period reports with correct currency amount?

thank you in advance

[Castech]

Hello, I use this report, you will have to know your account IDs for your payment accounts then replace my currencies with yours

[Multi Currency Report:2, 1, 1, 1]
>Currency||Amount

USD Cash||[=F(TN('{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Cash}')+TN('{ACCOUNT TRANSACTION TOTAL:Customer Cash Payment:Cash}'))]
INT CC||[=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 3)) as [Total] FROM AccountTransactionValues where AccountId = 588 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
Zim Switch|[=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 6 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
Ecocash|[=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 7 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
Bond| [=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 582 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
>Totals (Exc USD)|[=F(TN('{ACCOUNT TOTAL:LOYAL}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 6 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 7 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 582 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId =  and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
>Total USD Cash||[=F(TN('{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Cash}')+TN('{ACCOUNT TRANSACTION TOTAL:Customer Cash Payment:Cash}'))]
>Total Int CC|| [=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 588 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
>Totals (Inc USD)|[=F(TN('{ACCOUNT TOTAL:LOYALTY CARD}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 6 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 7 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 588 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 582 and [Date] > '{Start}' and [Date] < '{End}':F.Total}')+TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(Exchange, 2)) as [Total] FROM AccountTransactionValues where AccountId = 5 and [Date] > '{Start}' and [Date] < '{End}':F.Total}'))]
>|
>Fill In Today's Rate:||

[jerryfin]

@MarkNarsingh, sorry for the late response did you get this solve. If not I will share the database code that I ran to achieve this.

[jerryfin]

When using multiple currency the amount on the report operates very strange. You can follow this topic on the forum:

Minus Change from settle amount

I am trying to get the exact payment amount by users. This would calculated as Payment Amount - Change Issued I got this from the work period report but the change issue is not minus/deducted @{REPORT PAYMENT DETAILS:P.User,P.Amount.Sum::{0}:,} [Settled by $1:1, 1, 1] {REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(PU=$1)} Total Income|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU=$1)}

I was able to find a solution thanks to other users on the forum. remember to test each solution before going live.

[MarkNarsingh]

@jerryfin sorry for my late response i was working offshore for a while no this was not achieved