Try ERPNext Buy Support Partners Foundation

Jasper Reports Customer Statements

Good Day

I designed a report in Jasper Reports and would love it if someone could recreate on ERPNext to be included in Standard Reports.

SQL Used:

SELECT posting_date, account, party_type, party,CAST(SUM(debit)AS Double) AS debit, CAST(SUM(credit)AS DOUBLE) AS credit,voucher_type, voucher_no, cost_center, project,against_voucher_type, against_voucher, account_currency,remarks,(SELECT SUM(tabSales Invoice.outstanding_amount) FROM tabSales Invoice WHERE tabSales Invoice.customer_name = $P{customer} AND tabSales Invoice.posting_date BETWEEN $P{FDate} AND last_day($P{FDate}) GROUP BY tabSales Invoice.customer_name) AS ZDays, (SELECT SUM(tabSales Invoice.outstanding_amount) FROM tabSales Invoice WHERE tabSales Invoice.customer_name = $P{customer} AND tabSales Invoice.posting_date BETWEEN $P{Min1Month} AND last_day($P{Min1Month}) GROUP BY tabSales Invoice.customer_name) AS THDays, (SELECT SUM(tabSales Invoice.outstanding_amount) FROM tabSales Invoice WHERE tabSales Invoice.customer_name = $P{customer} AND tabSales Invoice.posting_date BETWEEN $P{Min2Month} AND last_day($P{Min2Month}) GROUP BY tabSales Invoice.customer_name) AS SIDays, (SELECT SUM(tabSales Invoice.outstanding_amount) FROM tabSales Invoice WHERE tabSales Invoice.customer_name = $P{customer} AND tabSales Invoice.posting_date < last_day($P{Min3Month}) GROUP BY tabSales Invoice.customer_name) AS NIDays, (SELECT CAST(SUM(tabSales Invoice.outstanding_amount)AS Double) FROM tabSales Invoice WHERE tabSales Invoice.customer_name = $P{customer} AND tabSales Invoice.posting_date < last_day($P{FDate}) GROUP BY tabSales Invoice.customer_name) AS TotalOut FROM tabGL Entry WHERE company = “Standerton Mills” AND Party_type=“Customer” AND posting_date BETWEEN $P{FDate} AND last_day($P{FDate}) AND party=$P{customer} GROUP BY voucher_no ORDER BY posting_date, account

Jasper_Statement

Hope This could be added at some point.

Thank You

Albertus Geyser

1 Like

Good Day

Herewith link to Jasper Reports File for anyone who needs it:

Thank You

Albertus Geyser

2 Likes

Good Day

Herewith updated Jasper Reports Customer Statement for anyone who needs it:

http://www.mediafire.com/file/5u3hsa1cut45910/Customer_Statement.jrxml/file

1 Like

Thank you Albertus

I is so sad that an important feature like this keeps getting pushed aside and a external solution has to be provided. I would rather this in an ERP than a TODO list.

1 Like

Total agreeing to your point.
But we can design it in erpnext.
I agree that this should be standardized.

We have been working in designing many reports ourself.
Will look into this as this is something required by every company who gives credit facility to customers.

1 Like

That is encouraging news. I look forward to progress in this regards.

1 Like