Hi Mc,
I have a SQL-report that shows the result of the CashIn/CashOut a waiter might have during his shift.
(He gives 10 to go and buy an apple and receive 4 as the change, so he spent 6; cashin/out is -6)
It is not perfect but it works.
The only small issue I have is that when there is no cashin/out transaction during his shift, no record is returned from the query, and the printed formating of my report is not the way I like. (the visual report looks good).
Here is a photo of the printed report, 2 columns but the item’s name (CashIn/CashOut) is RIGHT aligned when no record is returned from the SQL.
Here is my report:
[Cierre de Caja:4 , 1]
@ {SETTING:CURRENTUSER},
{REPORT SQL DETAILS:
SELECT
'Cash In/Cash Out' as CashIN
,sum([Debit]+[Credit]*-1) as [txTotal]
FROM [AccountTransactionValues]
WHERE [Name] LIKE '%$1'+'%' AND [AccountId]=5
AND SUBSTRING(REPLACE(CONVERT(varchar(50),[Date],126),'T',' '),1,16) >= SUBSTRING(REPLACE(CONVERT(varchar(50),'{Start}',126),'T',' '),1,16)
AND SUBSTRING(REPLACE(CONVERT(varchar(50),[Date],126),'T',' '),1,16) <= SUBSTRING(REPLACE(CONVERT(varchar(50),'{End}',126),'T',' '),1,16)
:F.CashIN, F.txTotal
}
Is there a way, without using a script, to:
- initialize txTotal to “0.00” in the SQL
- insert in the SQL, an IF no record, THEN txTotal = 0
- … ?
Marc