Jasper Reports Customer Statements

ERPNext Reports
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

2 Likes
2

Good Day

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

Thank You

Albertus Geyser

2 Likes
3

Good Day

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

2 Likes
4

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
5

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
6

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

1 Like
7

Can you give steps to add it in my server

8

I know we can do a lot of reports in ERPNext and over the years they have made printing the reports more powerful, but there are areas where it lacks. Solutions like Jasper Reports give out more professional reports with proper Header, Footer, watermarks etc. This has been discussed before, a solution like Jasper Report will be a strong value add to ERPNext.

9

honestly, reporting should be one of the strongest areas of an ERP because that’s one of the primary purposes of implementing in the first place. Personally, I’d rather more effort be put into ensuring ERPNext has a very versatile reporting engine than relying on external solutions

Of course, you can always connect your external solutions to the ERPNext db if you really need to

Cheers

10

Have a look at my solution here

I don\t mean to hijack Albertus’s post. His excellent solution can run for multiple clients while mine does one at a time.