I am trying to build a report to summarise my daily sales. I can drill down to individual payment types, but when I do this, the Payment Amount is the amount tendered (which makes sense), but I have a few change types which I then use depending on the currency, however I can’t seem to be able to access these through standard reporting tags. Is there a way to access ticket change values, or can it only to found through SQL?
M
Emre added a new Report Tag for Change values just recently, and I don’t think it is documented. Let me look around…
I see it is in Report Help, but cannot find the post where he demonstrated it’s use:
Grrrrr - How did I miss that
I have no idea what syntax to use to get the fields.
The Auto-Complete hints don’t seem to work for that Tag, and I cannot seem to find the post that Emre made showing it’s use.
Seems to me it wasn’t that long ago, and it was just mentioned in passing in some Topic with Emre’s lead-up being like “I don’t know how/why we never had this” or “why no one asked for or needed this”.
I searched and searched for the last hour. I think words in code-blocks are excluded from search for some reason so searching for “REPORT CHANGE PAYMENT” comes up with nothing?
Many of the fields seem to be the same of {REPORT PAYMENT DETAILS}, but I have found something weird, perhaps @emre can explain what is happening?..
If I use the following: {REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount} then the P.Amount's are perfect, they are exactly what happened for each ticket. But the P.Type's are completely wrong…
Payment types Change payment types "P.Type" outputs....
Cash Change USD Credit Card
Cash MXN Change MXN Cash
Cash GBP Change GBP Voucher
It’s really weird. When I issue change…
in the default currency, it is reported as “Credit Card”
in MXN, it is reported as “Cash”
in GBP, it is reported as “Voucher”
Voucher is particularly weird, because I never even used that payment method!
Are they consistently the same wrong type?
If it’s always the same wrong type and that voucher isn’t used sounds like maybe mismatched ids.
Yep, always wrong #20CharactersMinimum
What happens if you specify a type in the report? Does it show correct amounts with wrong label or AMMOUNT for another type?
Hmmm… good plan…
All of these return nothing at all (of course, I can’t be sure that they accept PT as an expression, because we can’t find any documentation for this tag)…
[Change1:1,2,1]
{REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount:(PT=Cash MXN)}
[Change2:1,2,1]
{REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount:(PT=Cash)}
[Change3:1,2,1]
{REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount:(PT=Change MXN)}
[Change4:1,2,1]
{REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount:(PT=Voucher)}
[Change5:1,2,1]
{REPORT CHANGE PAYMENT DETAILS:T.TicketNumber,P.Type,P.Amount:(PT=Credit Card)}
So, without being able to filter by change type I need to do this is SQL.
What is the preferred methof for this? {CALL:js.myScript()} or {REPORT SQL DETAILS:Select From}
I was able to get the CALL method working for individual items, but when totaling everything up I needed to pass in the {Start} and {End} parameters in didn’t seem to work. I tried:
{CALL:co.getChangeTotal('Till MXN','{Start}','{End}')} and
{CALL:co.getChangeTotal('Till MXN',{Start},{End})} but neither work, so I have been forced to use the REPORT SQL tag, which does make my custom reports rather large and difficult to debug!
I have run in to another issue when trying to write my report line for column totals…
To work out the USD total, I need to calculate the sum of all the USD payments, minus the sum of all the USD change payments…
This fireld works for the USD payment total…
=[PA.SALES Cash USD.sum]
This field works for the USD change payment total…
=[=TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [Total] FROM [ChangePayments] WHERE [Name] = 'Till USD' AND [Date] > '{Start}' AND [Date] < '{End}':F.Total}')]
The problem is that I don’t seem to be able to combine the two in a total calculation
It seems whenever I try to use TN() nothing is outputted by the entire totals report line (I think because it’s looking for .sum or something). For example…
=[PA.SALES Cash USD.sum]
=[PA.SALES Cash USD.sum]-5
=[PA.SALES Cash USD.sum] - [=5]
=[PA.SALES Cash USD.sum] - [=TN(5)]
=[PA.SALES Cash USD.sum] - [=TN('5')]
=[PA.SALES Cash USD.sum] - [=TN(5)]
=[=PA.SALES Cash USD.sum]
: =[=PA.SALES Cash USD.sum -5]
As you can see, whenever I try and use TN() everything stops working and I get nothing outputted by the whole report line. What am I doing wrong?
EDIT: Now looking to have to do everything in SQL, but I’d rather stick to core report tags…
SELECT (
(SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal]
FROM [Payments]
WHERE [Name] = 'Till USD' AND [Date] > '2017-05-29 15:24:00' AND [Date] < '2017-05-30 10:00:00')
-
(SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [ChangeTotal]
FROM [ChangePayments]
WHERE [Name] = 'Till USD' AND [Date] > '2017-05-29 15:24:00' AND [Date] < '2017-05-30 10:00:00')
) AS NetTotal
Similar issue when trying to sum three differernt types of card payments and then multiple this by an exchange rate retrieved using a TN() command. So I need to do this… (It’s getting long and messy now!)
SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal]
FROM [Payments]
WHERE ([Name] = 'Inbursa MXN' OR [Name] = 'ScotiaPOS MXN' OR [Name] = 'AMEX MXN') AND ([Date] > '2017-05-29 15:24:00' AND [Date] < '2017-05-30 10:00:00')
OK, this is getting really strange…
I have simplified my TOTALS report line for simplicity, but this works
{REPORT TICKET DETAILS:T.TotalAmount.sum,=[=TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal] FROM [Payments] WHERE [Name] = 'Till USD' AND [Date] > '{Start}' AND [Date] < '{End}':F.PaymentTotal}')]}
and this does not work (the entire line does not appear)
{REPORT TICKET DETAILS:T.TotalAmount.sum,=[=TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal] FROM [Payments] WHERE [Name] = 'Till MXN' AND [Date] > '{Start}' AND [Date] < '{End}':F.PaymentTotal}')]}
THe ONLY change here is I changed Till USD to Till MXN. Why would this prevent it from working? When I run this SQL query in management studio it does give the result I would expect.
This really makes no sense - Any other type of [Name] works perfectly. Also, specifying any corresponding PaymentTypeId works as expected, apart from when I specify the payment ID for Till MXN.
This is the data itself, does this shed any light of the situation…
1831 88 1 1 Till USD 2017-05-29 10:54:06.650 0 50.0000 50.0000 1 5 4488 1334 1.0000000000
1832 89 1 1 Till USD 2017-05-29 11:01:14.373 0 10.0000 10.0000 1 5 4492 1335 1.0000000000
1833 90 14 1 PayPal USD 2017-05-29 12:22:40.260 0 10.0000 10.0000 1 1 4494 1336 1.0000000000
1834 90 4 1 53 (Elia) 2017-05-29 12:22:42.810 0 20.0000 20.0000 1 1 4495 1336 1.0000000000
1835 91 1 1 Till USD 2017-05-29 12:34:17.837 0 30.0000 30.0000 1 1 4497 1337 1.0000000000
1836 91 2 1 ScotiaPOS MXN 5555 2017-05-29 12:34:35.377 0 63.0000 63.0000 1 1 4498 1337 0.0540540540
1837 92 6 1 Till MXN 2017-05-29 14:00:25.140 0 54.0541 54.0541 1 5 4506 1338 0.0540540540
1838 93 1 1 Till USD 2017-05-29 15:30:47.310 0 50.0000 50.0000 1 5 4510 1343 1.0000000000
1839 94 6 1 Till MXN 2017-05-29 15:32:24.540 0 5.4054 5.4054 1 5 4513 1344 0.0540540540
1840 95 14 1 PayPal USD 2017-05-30 10:12:51.090 0 10.0000 10.0000 1 5 4516 1345 1.0000000000
1841 95 12 1 ScotiaPOS USD 9999 2017-05-30 10:12:55.320 0 10.0000 10.0000 1 5 4517 1345 1.0000000000
1842 95 6 1 Till MXN 2017-05-30 10:13:05.397 0 9.7297 9.7297 1 5 4518 1345 0.0540540540
1843 96 6 1 Till MXN 2017-05-30 10:13:22.230 0 30.0000 30.0000 1 5 4521 1346 0.0540540540
1844 96 2 1 ScotiaPOS MXN 9999 2017-05-30 10:14:04.553 0 10.8108 10.8108 1 5 4522 1346 0.0540540540
1845 96 13 1 Inbursa MXN 5555 2017-05-30 10:14:09.037 0 27.0270 27.0270 1 5 4523 1346 0.0540540540
1846 96 5 1 AMEX MXN 8888 2017-05-30 10:14:14.957 0 25.1622 25.1622 1 5 4524 1346 0.0540540540
1847 97 2 1 ScotiaPOS MXN 8888 2017-05-30 10:14:30.160 0 2.7027 2.7027 1 5 4527 1347 0.0540540540
1848 97 5 1 AMEX MXN 8888 2017-05-30 10:14:38.043 0 18.1622 18.1622 1 5 4528 1347 0.0540540540
1849 97 6 1 Till MXN 2017-05-30 10:14:40.140 0 10.8108 10.8108 1 5 4529 1347 0.0540540540
I have never seen a Report Tag nested in a Report Tag. It does not surprise me at all that you are getting weird results.
Reports can be finicky when you try to do things that Emre cannot predict. The parser can break and give your garbage, or nothing at all.
When trying to do something very specific, you need to stick to SQL. You already know it works.
Not everything can be done with Report Tags, and I know this well, because I have tried many strange things, and sometimes I get what I want, yet a lot of times I just throw my hands in the air and spend my time writing SQL instead.
Is there another way to have the results of multiple reports on one line? For example ideally I need…
Ticket number Payment Change Net
{T.TicketNumber} {P.Amount} {C.Amount} ={P.Amount}-{C.Amount}
When I have tried to have multiple reports on the same line it doesn’t seem to work…
After a bit more hit an miss, and I have discovered that it’s nothing to do with the name (MXN vs USD). The problem is the total amount. If it is more than 999.99 then this whole thing is not happy and no total line is printed. I guess this issue is due to “1000.00” actually having a comma (1,000.00) - I wonder is this could also be linked to the 1,000 issue I reported here.
BTW - Did you see the issue with multiple type of change I am experiencing? Are you finidng the same on your system @QMcKay? Also have you now made the switch to USD as your default currency on your live system? How’s it going?
Yes, run multiple Report Tags in the same line. For example, my Cashout Count Report does this. There are 5 columns in this Report, and each of those columns is a single Report Tag, or multiple Report Tags that I perform calculations on like adding and multiplying:
[Cashout Count Report:1, 1, 1, 1, 1]
WP {REPORT TASK DETAILS:TSC.id:(TST=Workperiod)}\r[='{REPORT TASK DETAILS:TSC.isOpen:(TST=Workperiod)}'=='true' ? 'OPEN' : 'CLOSED']|[=FD('{REPORT TASK DETAILS:TSC.dateStart:(TST=Workperiod)}','yyyy-MM-dd')]|[=FD('{REPORT TASK DETAILS:TSC.dateStart:(TST=Workperiod)}','HH:mm')]|[=FD('{REPORT TASK DETAILS:TSC.dateEnd:(TST=Workperiod)}','yyyy-MM-dd')]|[=FD('{REPORT TASK DETAILS:TSC.dateEnd:(TST=Workperiod)}','HH:mm')]
>>CASH|Count|(-Float)|Account|+/-
HNL|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}'),'0.00')]|[=F(TN('{ACCOUNT TOTAL:Cash HNL}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}')-(TN('{ACCOUNT TOTAL:Cash HNL}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')),'0.00')]
USD|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}'),'0.00')]|[=F(TN('{ACCOUNT TOTAL:Cash USD}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}')-TN('{ACCOUNT TOTAL:Cash USD}'),'0.00')]
>>CC|Count|(+Devo)|Account|+/-
CC HNL|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}')+TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}'),'0.00')]|[=F(TN('{ACCOUNT TOTAL:Credit Card HNL}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}')+TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}')-(TN('{ACCOUNT TOTAL:Credit Card HNL}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')),'0.00')]
CC USD|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}'),'0.00')]|-|[=F(TN('{ACCOUNT TOTAL:Credit Card USD}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}')-TN('{ACCOUNT TOTAL:Credit Card USD}'),'0.00')]
>>CC Tips|Count|Paid|Account|+/-
HNL|[=F(TN('{REPORT TASK DETAILS:TSC.CountTIPS.Sum:(TST=CO Card HNL)}') + (TN('{REPORT TASK DETAILS:TSC.CountTIPS.Sum:(TST=CO Card USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')),'0.00')]|[=F(TN('{ACCOUNT DEBIT TOTAL:Tips}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F(TN('{ACCOUNT TOTAL:Tips}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F((TN('{ACCOUNT CREDIT TOTAL:Tips}')-TN('{ACCOUNT DEBIT TOTAL:Tips}'))*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]
>>Account|HNL|USD|-|TTL USD
Round|[=F(TN('{ACCOUNT TOTAL:Rounding HNL}'))]|[=F(TN('{ACCOUNT TOTAL:Rounding USD}'))]|-|[=F(TN('{ACCOUNT TOTAL:Rounding USD}')+TN('{ACCOUNT TOTAL:Rounding HNL}'))]
XR|[=F(TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.rateInv.Average:(TST=Exchange Rate)}'),'0.000000')]|-|-
>>TOTALS|Count|(-Float)|Account|+/-
HNL|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}')+TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}')+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F( TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}') + TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F((TN('{ACCOUNT TOTAL:Cash HNL}')+TN('{ACCOUNT TOTAL:Cash USD}')+TN('{ACCOUNT TOTAL:Credit Card HNL}')+TN('{ACCOUNT TOTAL:Credit Card USD}')+TN('{ACCOUNT TOTAL:Tips}'))*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}') + TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}')*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') - ((TN('{ACCOUNT TOTAL:Cash HNL}')+TN('{ACCOUNT TOTAL:Cash USD}')+TN('{ACCOUNT TOTAL:Credit Card HNL}')+TN('{ACCOUNT TOTAL:Credit Card USD}')+TN('{ACCOUNT TOTAL:Tips}')))*TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'),'0.00')]
USD|[=F(((TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}'))/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'))+(TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'))+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}')+TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}'),'0.00')]|[=F( ((TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}'))/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')) + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}') + (TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')+(TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}'))) + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}'),'0.00')]|[=F(TN('{ACCOUNT TOTAL:Cash HNL}')+TN('{ACCOUNT TOTAL:Cash USD}')+TN('{ACCOUNT TOTAL:Credit Card HNL}')+TN('{ACCOUNT TOTAL:Credit Card USD}')+TN('{ACCOUNT TOTAL:Tips}'),'0.00')]|[=F(TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Cash USD)}')-TN('{REPORT TASK DETAILS:TSC.Float.Sum:(TST=CO Cash USD)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') +TN('{REPORT TASK DETAILS:TSC.CountDEVO.Sum:(TST=CO Card HNL)}')/TN('{REPORT TASK DETAILS:TSC.rate.Average:(TST=Exchange Rate)}') + TN('{REPORT TASK DETAILS:TSC.Total.Sum:(TST=CO Card USD)}') - (TN('{ACCOUNT TOTAL:Cash HNL}')+TN('{ACCOUNT TOTAL:Cash USD}')+TN('{ACCOUNT TOTAL:Credit Card HNL}')+TN('{ACCOUNT TOTAL:Credit Card USD}')+TN('{ACCOUNT TOTAL:Tips}')),'0.00')]
Look at the last 2 lines… they are HUGE! Summing multiple Reports, while multiplying some of them for the Exchange Rate. Ugly looking, and hard to read, but it works very well.
I do this because Report Tags have very limited support for Expressions and Functions. So I need to pull one value from one Report at a time, stuff it in a [=TN('{REPORT:X}')] and do the math from there.
OK - So I should be able to do away with the nested reports I have now. Let me take a second look at the mess I’ve created! 
No I have not seen that, at least not so far. I only have a single Foreign Currency. But I guess I don’t type numbers very often - I use the Denomination Buttons on the Left, or the ALL button almost exclusively. Do you have “Auto-decimals” enabled?
Yes, I made the switch and it went fairly smooth. But I took over a month to develop and test it too. Not for the light of heart. I switched both of my Venues and things are working great! 
Awesome - I’m glad it’s going well. I’ll have to vist your place if I get a chance to escape the business here and visit Honduras 
OK - So this was a real head scratcher…
This does not work for me:
[Multi-reports:1,1,1,1]
@{REPORT TICKET DETAILS:T.Id:(TY=Sales Ticket)::,}
{REPORT TICKET DETAILS:T.TicketNumber:T.Id=$1}|{REPORT TICKET DETAILS:T.TotalAmount:T.Id=$1}
So I was thinking, how is this working for @QMcKay, but not me… Then I spotted the “HNL” and “USD” at the start of each row…
Sure enough, this works fine…
[Multi-reports:1,1,1,1]
@{REPORT TICKET DETAILS:T.Id:(TY=Sales Ticket)::,}
Row|{REPORT TICKET DETAILS:T.TicketNumber:T.Id=$1}|{REPORT TICKET DETAILS:T.TotalAmount:T.Id=$1}
Not sure if this has been documented anywhere, so figured I should mention it…
Wahooo! Cracked it!
It’s a little long, but it works…
[Sales Tickets:1,3,1,1,1,1,1,1,1,1,1,1]
>#|Name|Gross|CC|Round|Net|USD|MXN|CC USD|CC MXN|PP USD|Credit USD
@{REPORT TICKET DETAILS:T.Id:(TY=Sales Ticket)::,}
{REPORT TICKET DETAILS:T.TicketNumber,
EN.Customer,
=[T.TotalAmount]-[CA.CC Surcharge]-[CA.SALES Round MXN -]-[CA.SALES Round MXN +]-[CA.SALES Round USD -]-[CA.SALES Round USD +],
CA.CC Surcharge,
=[CA.SALES Round MXN -]+[CA.SALES Round MXN +]+[CA.SALES Round USD -]+[CA.SALES Round USD +],
T.TotalAmount,
=[PA.SALES Cash USD]-[=TN('{CALL:co.getChange($1,'Till USD')}')],
=([PA.SALES Cash MXN]*TN('{CALL:xr.getXR('MXN')}'))-[=TN('{CALL:co.getChange($1,'Till MXN')}')],
PA.SALES ScotiaPOS USD,
=([PA.SALES ScotiaPOS MXN]+[PA.SALES Inbursa MXN]+[PA.SALES AMEX MXN])*TN('{CALL:xr.getXR('MXN')}'),
PA.SALES PayPal USD,
PA.SALES Customer Account USD:
T.Id=$1}
>Total| |{REPORT TICKET DETAILS:=[T.TotalAmount.sum]-[CA.CC Surcharge.sum]-[CA.SALES Round MXN -.sum]-[CA.SALES Round MXN +.sum]-[CA.SALES Round USD -.sum]-[CA.SALES Round USD +.sum],CA.CC Surcharge.sum,=[CA.SALES Round MXN -.sum]+[CA.SALES Round MXN +.sum]+[CA.SALES Round USD -.sum]+[CA.SALES Round USD +.sum],T.TotalAmount.sum}|[=F(TN('{REPORT TICKET DETAILS:PA.SALES Cash USD.sum}') - TN('{REPORT SQL DETAILS:SELECT SUM(ROUND((Amount / ExchangeRate),2)) AS ChangeTotal FROM ChangePayments WHERE Name = 'Till USD' AND [Date] > '{Start}' AND [Date] < '{End}':F.ChangeTotal}'))]|[=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal] FROM [Payments] WHERE [Name] = 'Till MXN' AND [Date] > '{Start}' AND [Date] < '{End}':F.PaymentTotal}')-TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [ChangeTotal] FROM [ChangePayments] WHERE [Name] = 'Till MXN' AND [Date] > '{Start}' AND [Date] < '{End}':F.ChangeTotal}'))]|{REPORT TICKET DETAILS:=[PA.SALES ScotiaPOS USD.sum]}|[=F(TN('{REPORT SQL DETAILS:SELECT SUM(ROUND(([Amount]/[ExchangeRate]),2)) as [PaymentTotal] FROM [Payments] WHERE ([Name] = 'ScotiaPOS MXN' OR [Name] = 'Inbursa MXN' OR [Name] = 'AMEX MXN') AND ([Date] > '{Start}' AND [Date] < '{End}'):F.PaymentTotal}'))]|{REPORT TICKET DETAILS:PA.SALES PayPal USD.sum,PA.SALES Customer Account USD.sum}