ERPNext Foundation ERPNext Cloud Chat Blog Discuss Frappé* Donate

Jasper Reports Customer Statements

customization

#1

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


Customer Statements
#2

Good Day

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

Thank You

Albertus Geyser