How to generate a Customer Account Report?

[sayuru_ranshan]

How do you generate a Customer Account Report?
I need to generate a customer account report for each customer and the name of the customer can be filtered.

I need to create a table for each customer including Date, Bill Number, Bill Amount, Payment, and Balance as topics and finally I want to get a total bill amount balance.

Guide me in this task

Thank you

[WilkoJV]

This might help with adding some tweaks to the report.
https://forum.sambapos.com/t/custom-reports-customer-balance/7247/33

https://kb.sambapos.com/en/9-8-1-remaining-balanced-customers-report/

https://kb.sambapos.com/en/2-4-12-d-how-to-create-custom-report-and-statements/

[sayuru_ranshan]

I need Help to Fix My report

[Credit Customer Report:1, 1, 1, 1, 1]
>Customer Name|Date|Ticket Number|Bill Amount|Payments|Outstanding Balance
{REPORT SQL DETAILS:
SELECT e.Name as [Customer Name], 
       t.Date as [Transaction Date], 
       t.TicketNumber as [Ticket Number], 
       t.TotalAmount as [Bill Amount], 
       ISNULL(SUM(p.Amount), 0) as [Payments], 
       (t.TotalAmount - ISNULL(SUM(p.Amount), 0)) as [Balance]
FROM Tickets t
JOIN Entities e ON t.CustomerId = e.Id
LEFT JOIN Payments p ON t.Id = p.TicketId
GROUP BY e.Name, t.Date, t.TicketNumber, t.TotalAmount
HAVING (t.TotalAmount - ISNULL(SUM(p.Amount), 0)) > 0}

[Totals:1, 1, 1]
>Grand Total Outstanding Balance|{REPORT SQL DETAILS: SELECT SUM(t.TotalAmount - ISNULL(SUM(p.Amount), 0)) AS [Total Outstanding] FROM Tickets t LEFT JOIN Payments p ON t.Id = p.TicketId}

[Bob_be]

You are really close to getting this to work. CustomerId is not a valid column name. Customers are entities. The Ticket table does not have any references to any type of Entities. But there is a TicketEntites table that does. Find the common column between the 2 tables (Tickets & TicketEntities) and use a JOIN clause to reference those columns.

You are missing a defined column:

@sayuru_ranshan

… Report:1, 1, 1, 1, 1]

You will need to define the fields for Samba to use.

@sayuru_ranshan

…SUM(p.Amount), 0)) > 0}

You will need to add them and make it look like this:
…SUM(p.Amount), 0)) > 0:F.Customer Name,F.Transaction Date,...}
The names come from the SQL column names (after the SELECT).