Custom Reports -Customer Balance

The Print account Transactions Template is hard-coded right? Is it possible to create a custom report with the same results as the Print account Transactions? I would like to include the Ticket ID

Are you talking about the Print button from Accounts Screen? Can you show a screenshot of what you mean?

Yes that is hard coded.

You can create a report of anything.

PS: Create a new post use v5 Question category and title It Custom Report Help and then describe in detail what kind of report you want and what you want in it and how you want it to look.

1 Like

Can Community please check this report I want to see only customer list with balances , in this report its showing all customers.


[Customer Balances:2,2,3, 2]
{REPORT ENTITY DETAILS:EC.Customer Name,E.Name,EC.Address,E.Balance:(ET=Customers) && E.AccountId>0:::$4!=0}

Same I have tips and cashout report showing all tickets if I select a month which shows 100’s of tickets only related tickets I need thanks.


[Cash Outs by Tickets:1,1,1,1,1,1,1,1,]
>Ticket Number|Date|Time|Cashout Amount|Service Amount|Orders Amount|Total Amount|Payment Type
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,CA.Cash Out,CA.Service,=[T.TotalAmount]-[CA.Cash Out]-[CA.Service],T.TotalAmount,TT.PT2:(TS.Status=Paid)}

[Payment Totals:1, 1]
Eftpos Total: |${REPORT PAYMENT TOTAL:(PT=Eftpos)}
Cash Total: |${REPORT PAYMENT TOTAL:(PT=Cash)}
Cash Out Total: |${REPORT CALCULATION TOTAL:(CT=Cash Out)}
>TOTAL:|${ACCOUNT TOTAL:Payment Accounts}

As I’ve explained on your previous question you’re missing “.sum” in field name (E.Balance). Please check my previous example on your other topic.

Please format your posts properly while adding templates. Without formatting it is really hard to read. I’m fixing this one for you.

1 Like

thank a lot , I just started liking /bookmark posts so can track easily before I just read that way I missed many posts,and its difficult find them back

I have Customer Entities names being changed into member, and have modified gsreddy’s template into :

[Member Balance:2,2,3, 2]

Member ID|Name|Address|Balance
{REPORT ENTITY DETAILS:EC.Member Name,E.Name,EC.Address,E.Balance:(ET=Members) && E.AccountId>0:::$4!=0}[/code]

But showing negative balance while the member has positive balance :

The Members balance that i have followed is Custom Simple Loyalty Card Features tutorial.

Also how to display Member ID custom field from “Members” entity type ? . And the template that i wanted to display is like this :

No.   "Member ID"     Name       Address      Balance
1.         0001      Prodigy      Asia          $100

How to display the report like above ?

First of all that template is wrong. You should write E.Balance field as E.Balance.sum.

What do you mean by negative balance? Did you credited customer’s account?

Try adding EC.Member ID field to the report.

Thanks, based on your reply i have modified as following :

[Member Balance:5,5,5,5, 5]

>Member ID|Name|Address|Balance

{REPORT ENTITY DETAILS:EC.Member EC.Member ID,Name,E.Name,EC.Address,E.Balance.sum:(ET=Members) && E.AccountId>0:::$4!=0}

But the the preview or report now returning a empty reply
Changing EC.Member ID into EC.Member.Member ID also returning a empty reply

Yes i have credited 100 amount to the Name Prodigy

Also what does “[Member Balance:5,5,5,5, 5]” means?, formatting ?, it means 5 column ?

Your missing a , after EC.Member

Also your expression $4!=0 is telling it E.Name has to not equal 0. I am sure you did not mean for E.Name to be evaluated if it equals 0?

Try this instead:

[Member Balance:5,5,5,5,5, 5]
>Member ID|Name|Address|Balance
{REPORT ENTITY DETAILS:EC.Member,EC.Member ID,Name,E.Name,EC.Address,E.Balance.sum:(ET=Members) && E.AccountId>0:::$6!=0}

My custom field in Members entity is “Member ID” so space is intended ?.

Or do you mean like this :

[Member Balance:5,5,5,5, 5]

Member ID|Name|Address|Balance

{REPORT ENTITY DETAILS:EC.Member, EC.Member ID,Name,E.Name,EC.Address,E.Balance.sum:(ET=Members) && E.AccountId>0:::$4!=0}[/code]

Still returning empty reply :frowning:

What is EC.Member for? Your using 6 fields but defined only 4 columns.

I see another mistake you had…

Try this:

[Member Balance:5,5,5, 5]
>Member ID|Name|Address|Balance
{REPORT ENTITY DETAILS:EC.Member ID,E.Name,EC.Address,E.Balance.sum:(ET=Members) && E.AccountId>0:::$4!=0}

Thanks!, It works partially as the field name and column being truncated and the balance still displayed as a negative

How to expand the column ?

You should increase width of the report and copy the most recent syntax I gave you I made an edit.

BTW the part where you have [Member Balance:5,5,5, 5] the 5,5,5, 5 is column width percents.

You probably have the accounting messed up and are putting it in the wrong line.

Are you wanting your Customer Accounts to behave like a Debit system where you put money into the account and then they spend it? Or are you wanting it to run like credit where they build up a tab?

EDIT: Ok your using Loyalty system. You setup your transaction type wrong for adding funds to loyalty card. It should go into Debit not Credit. Credit is where funds go when they use their card to purchase something. Maybe you should read the tutorial a little closer and be sure you setup your transaction types correctly. It appears you set it up backwards.

I think for now like a debit system, customer also able to pay by cash, cc, voucher, and member balance (credited funds)

Can you help me, i have setup transaction types as such in the tutorial, i have change the name Customer Accounts into Member Accounts for easy recognizing.

The setup working fine, spending member balance works fine.

Did you change the Account Type for Member Accounts to Credit? Can you show the transactions for that member?

Yes Member Accounts is already Credit, transactions :

Hey @Prodigy
The syntax $4!=0 means “not equal” so I think you need to use $4>0 to test if the E.Balance.Sum > 0?
EDIT: Oh I see you want the -100 to show as just $100 or I what I do is ($100).
Sorry misread I think - stab in the dark multiply by sum by “-1” but if they owe money it will reverse the result…

Yes the balance is incorrectly displayed or incorrectly credited as @Jesse mentioned :frowning:, that would be not proper solution…

A “negative balance” indicates that the Account has Credit - that is, the account contains funds that are available to spend. This is normal, and is by design in the way that the SambaPOS Accounts work (and any “real” accounting system for that matter).

I implore you, do not change this by modifying your Transactions Types. The Balance showing as (negative) is normal. If the customer account has a “positive” balance that means it is overdrawn, and the customer owes money to their account. When a payment to their account is made, it reduces the value to bring it closer to zero (it becomes more negative).

Have you never noticed that when you make a Sale and look at the Sales account that it is shown as (negative)? Again, this is normal… it is in a “Credit” state. If you change it for Customer Accounts, you need to change if for everything else - every Account, including Sales, etc. and every Transaction Type in your system needs to be reversed. Do not do this.

I understand why you may think this is backwards - I had a very hard time with the concept for a very long time… I finally accepted it as “that is the way it is”, and eventually, I came realize it makes sense.

If you do not like the way it appears in the Report, simply multiply the value by -1 to get your desired visual result… but please, do not mess with the accounts.