CUSTOM REPORTS - Displaying ticket change

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)}
1 Like

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…

:heavy_check_mark: =[PA.SALES Cash USD.sum]
:heavy_check_mark: =[PA.SALES Cash USD.sum]-5
:heavy_check_mark: =[PA.SALES Cash USD.sum] - [=5]
:x: =[PA.SALES Cash USD.sum] - [=TN(5)]
:x: =[PA.SALES Cash USD.sum] - [=TN('5')]
:x: =[PA.SALES Cash USD.sum] - [=TN(5)]
:heavy_check_mark: =[=PA.SALES Cash USD.sum]
:x: : =[=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.

1 Like

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! :slight_smile:

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! :smile:

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 :slight_smile:

1 Like

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}

I know, right? That is the weird shit I am talking about. Lead with space-pipe or just pipe probably works too. There are things about the parser that are very strange sometimes, and there is no way to predict that. But we always find ways to break stuff that @emre was never expecting, and then we sometimes, somehow, come up with a work-around, even if it seems to make no sense at all.

It would be almost impossible for me to explain that quirk that I found, and have worked around by giving an empty column at the beginning with space-pipe or just pipe. Somehow, somewhere, sometime, I ran across that and scratched my head, where it was working, then it wasn’t on another line, and what was the difference? Then put the leading thing in and bang it works again.

How do you describe that to someone, nevermind explain the “why”.

1 Like

Inventive. I could never have come up with that, and would have given up and gone SQL looong before. :stuck_out_tongue_winking_eye:

Thanks Q, I’m enjoying finding the quirks!

I’d like to convert these REPORT SQL tags to CALL tags and store the SQL inside a script, but I can’t seem to successfully pass in the {Start} and {End} parameters in to a CALL. Do you know if it’s possible?

I am having an issue using an IF statement in a Custom Report

[=TN('{CALL:co.getChangeByType($1,2)}')] works perfectly and outputs either a 0 or the amount of change, but when I try to use this in an IF statment, it doesn’t seem to work (I don’t want to output the 0’s, I only want an output when change was givien).

This is not working (nothing is output regardless of the change amount). What am I doing wrong?
[='TN('{CALL:co.getChangeByType($1,2)}')' == '0' ? 'Change Given' : 'No Change']

Try this [=TN('{CALL:co.getChangeByType($1,2)}') == '0' ? 'Change Given' : 'No Change']

1 Like

Perfect, thanks @Jesse

PS. My IfTrue and IfFalse responses are the wrong way around, but that’s just a typo on my part :slight_smile: