Oops, good shout, not request.
1 Like
awesome, been trying to achieve this for a while!
Thanks 
1 Like
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.
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.
Yer, thats true, just had a thought though… 1 sec…
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…
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…
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}
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…
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
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}}
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}}
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
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.
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…
Hello, why only two digits appear in total?
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.
Looks like you have some system formatting variances.
What is desired format.
Some figures are xx.xxx and others have a comma?
Ok, it may be, but what do you mean by variations?
Where can I check that?
Hi. How do I add the new of the customer who paid his bill to this report