JTRTech
September 10, 2016, 5:09pm
1
Just thought Id post a sample of a basic work period adjustment incorporating cusomter accounts;
[Ticket Payments:2, 1]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:((PT=Cash)||(PT=Credit Card)||(PT=Voucher)) AND (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
[Payment Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
Adapted Payment details tag to only show cash, card and voucher and then put account charges on separate combined line rather than listing each used account as a payment type.
This section should balance against sales total (including refunds if any)
Then added a separate table for account payments as this should represent the gap between ticket payments and total cash/card received.
Then have a total cash and card received table.
Initial tests show expected results so farâŠ
2 Likes
Wait, have what you detailed above work?
This is something which I need desperately, its clogging up my end of day printout.
Matt
pauln
September 11, 2016, 2:12am
3
Hey @JTRTech - why is this a request? You wish to change or add something?
1 Like
JTRTech
September 11, 2016, 10:37am
4
Oops, good shout, not request.
1 Like
awesome, been trying to achieve this for a while!
Thanks
1 Like
JTRTech
September 11, 2016, 9:24pm
6
Unfortunately since account payments are each named differently you cant even try to exclude accounts allowing all others dynamically.
Would be nice to have a Account Type to constrain against so we could say account type=payment accounts making report dynamic while excluding individual account payments.
pauln
September 12, 2016, 12:24am
7
Umm I could post my syntax here but it pretty âseriousâ and might just confuse many posters - heck I still try and understand my own work
In the immortal kendash style here is a hint for that one above @JTRTech :
&& Payment.Name.Contains("Eftpos")
I use that to separate payment types - but obviously you need a well planned naming convention before system build.
JTRTech
September 12, 2016, 12:57am
8
Yer, thats true, just had a thought though⊠1 secâŠ
JTRTech
September 12, 2016, 1:16am
9
Ok, so thinking something like this;
{REPORT SQL DETAILS: SELECT a.[Name] FROM [Accounts] a JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name] = "Payment Accounts":F.AccountList::{0}:,}
But still working on itâŠ
pauln
September 12, 2016, 1:19am
10
If I can add (possibly you doing this already) but if you take away the â@â and just list the output i.e $1 THAT becomes the input for the Report tag - it helped me heaps.
{REPORT SQL DETAILS: x}
$1
Leave $1 outside of any [Report Syntax], rightâŠ
JTRTech
September 12, 2016, 1:27am
11
Needed to specify an ASâŠ
[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
$1|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
JTRTech
September 12, 2016, 1:36am
12
Ok, progress so far;
[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}
[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}
[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}
[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Total|{REPORT ENTITY DETAILS:E.Balance.Sum}
{ACCOUNT TOTAL:Customer Accounts}
If can sort balance for customer accounts can add BF and CF lines of account balances and should be good to go I thinkâŠ
JTRTech
September 12, 2016, 1:44am
13
Hey @QMcKay as SQL pro, does this look like it works?
Seems to give the value I expect after few tests;
SELECT SUM([Exchange]) FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5
This is to get the current balance of Customer Accounts.
Although guess would need to add date constraints if it was expected to be backdateable on reports.
1 Like
JTRTech
September 12, 2016, 1:52am
14
Getting there, but could definatly do with a cleaner solution;
[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
>Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-{ACCOUNT TOTAL:Customer Accounts}]
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
JTRTech
September 12, 2016, 2:06am
15
Debtor Movement;
[Debtor Movement:2, 1]
Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
Debtor Charges|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
Debtor Payments|-[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
JTRTech
September 12, 2016, 2:06am
16
Leaving it at that for tonight;
[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}
[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}
[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]='Payment Accounts':F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}
[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
[Debtor Movement:2, 1]
Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
Debtor Charges|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
Debtor Payments|-[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Total|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
2 Likes
JTRTech
September 12, 2016, 2:17am
17
Little moment summary;
[Movement Summary:2, 1]
Debtors BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
+ Sales|{REPORT TICKET TOTAL}
- Payments|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
Debtors CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
It is all a little specific, something more generic would be good but does the job for now I think.
I still believe the default work period should have some form of debtor section or if not a separate report for it.
Dont think work period report should treat individual account payments for tickets like it does hence the adaption made above.
It should also incorporate account payments.
I understand the reasoning behind this as there is nothing distinguishing sales and payment accounts from debtor accounts from a data standpoint other than the report itself and transaction configurations handling the relationships.
To resolve this I think you would need to have some other form of higher identification for account type - say class or an option to specify debtor style account types which triggers a BF/CF style reporting.
JTRTech
September 23, 2016, 12:29am
18
Had a though on this topic about account refund⊠Given there is no transaction type for refund (sale or payment) I am guessing a refund is a minus value credit or debit depending on account(still not got the two completely straight in my head) rather than a reverse transaction?
@Jesse you good with accounts, can you confirm that logic?
I ask as using account credit and debit report expressions and just realised I hadnât checked how refund effects the reportâŠ
JTRTech:
[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}
[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}
[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]=âPayment Accountsâ:F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}
[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
[Debtor Movement:2, 1]
Balance BF|[={REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}-({ACCOUNT TOTAL:Customer Accounts})]
Debtor Charges|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
Debtor Payments|-[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
Balance CF|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Total|{REPORT SQL DETAILS: SELECT SUM([Exchange]) AS [Balance] FROM [AccountTransactionValues] WHERE [AccountTypeId] = 5:F.Balance::{0}}
Hello, why only two digits appear in total?
JTRTech:
[Sales:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}
[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}
[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}
[Ticket Payments:2, 1]
@{REPORT SQL DETAILS: SELECT a.[Name] AS [AccountList] FROM [Accounts] a LEFT JOIN [AccountTypes] t ON a.[AccountTypeId] = t.[Id] WHERE t.[Name]=âPayment Accountsâ:F.AccountList::{0}:,}
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PT=$1) && (Payment.Amount > 0)}
Account|{ACCOUNT TRANSACTION DEBIT TOTAL:Customer Account Transaction}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}
[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}
[Account Payments:2, 1]
Cash|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}
Credit Card|{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}
>Total|[={ACCOUNT TRANSACTION CREDIT TOTAL:Customer Cash Payment}+{ACCOUNT TRANSACTION CREDIT TOTAL:Customer Credit Card Payment}]
[Income Totals:2, 1]
Cash|{ACCOUNT TOTAL:Cash}
Credit Card|{ACCOUNT TOTAL:Credit Card}
>Total|[={ACCOUNT TOTAL:Cash}+{ACCOUNT TOTAL:Credit Card}]
[Account Balances:2, 1, 1]
@{REPORT SQL DETAILS:SELECT [Name] AS [AccountName] FROM [Accounts] WHERE [AccountTypeId] = 5:F.AccountName::{0}:,}
{REPORT ENTITY DETAILS:E.Name,E.Balance.Sum;0.00:(EN=$1):{0}|{1}::$2!=0}
>Total|{REPORT ENTITY DETAILS:E.Balance.Sum}
{ACCOUNT TOTAL:Customer Accounts}
Hello I need to make a custom report where the clients who have paid their bills appear, the name of customer, also I need to show what kind of payment the client made, that is, cash or card.