Add or Subtract script results in reports

V5 Question
1

Is it possible to add or subtract script results in reports.
I know its possible to do via sql but this requires another script.

I would be simple for totals for example.
I have two scripts, one for cash in and one for cash out. I need to subtract the two to get total in drawer.
I have tried >Cash In Drawer|[@@CashIn]-[@@CashOut]

2

Calculations are usually in this format [= A - B ]

If not just do within a JScript…

The script would do the sum and the report would then be {CALL:reports.cashInDrawer}

Obviously the reports.cashInDrawer part would depend on what you called your script.

3

This does not work.

I was trying to avoid creating new scripts because the list is getting very long.

Could you give an example on how to do this using JScript??
I have no understanding of JS at all.

4

Your two SQL queries are plain series right? No parameters?
Give me 15-20mins to jump on PC and I’ll show you the script that will do it.

I’m intrigued to what those queries are looking up if you wouldn’t mind showing them?
Won’t make difference to script just intrigued what they are querying.

5

Thanks its something i cannot seem to get my head around.

I’m slowly starting to figure out SQL, my queries might not be the best but they work.

@@CashIn basically reports all cash in from both tickets and customer account payments.

SELECT 
SUM(amount)
FROM
(SELECT 
SUM(Amount) AS amount
FROM
AccountTransactions
INNER JOIN AccountTransactionDocuments 
ON AccountTransactions.AccountTransactionDocumentId = AccountTransactionDocuments.Id
WHERE
AccountTransactionDocuments.Date > '{Start}'
AND AccountTransactionDocuments.Date < '{End}'
AND AccountTransactions.Name LIKE '%Cash Payment%' 
AND AccountTransactions.Name NOT LIKE '%Transaction%'  
UNION ALL SELECT 
SUM(Amount) AS amount
FROM Payments
WHERE
Payments.Date > '{Start}'
AND Payments.Date < '{End}'
AND Payments.Name = 'Cash') 
AS amounts

@@Cashout is for payouts to suppliers etc.

6

They both return a single value right?

7

Yes both return the total value for in and out

8 
function cashInDrawer() {
	var cashIn			= sql.Query(@@CashIn).First;
	var cashOut			= sql.Query(@@CashOut).First;
	var cashInDrawer	        = cashIn - cashOut;
	return cashInDrawer
}
1 Like
9

That would be called with {CALL:CashScript.cashInDrawer}

1 Like