webERP Forum
Report Builder - Prior/Future Periods - SOLVED - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (/forumdisplay.php?fid=1)
+--- Forum: Feature Requests (/forumdisplay.php?fid=5)
+--- Thread: Report Builder - Prior/Future Periods - SOLVED (/showthread.php?tid=8110)

Pages: 1 2


Report Builder - Prior/Future Periods - SOLVED - VortecCPI - 03-01-2018 12:17 AM

It sure would be nice to add prior periods to the Report Builder:

+ Yesterday
+ Last Week
+ Last Month
+ Last Quarter
+ Last Year

When I do commission reports it is for the prior month.


RE: Report Builder - Prior Periods - phil - 03-01-2018 10:03 AM

You can use the sales analysis reporting for this.


RE: Report Builder - Prior Periods - VortecCPI - 03-02-2018 02:12 PM

Thanks Phil. I have not looked there yet.

Can I create semi-complex queries with operators using sales analysis reporting?

This is what I now have:
Code:
SELECT salesman.salesmanname,
salesorders.debtorno,
LEFT(debtorsmaster.clientsince, 10), ROUND(DATEDIFF(NOW(), debtorsmaster.clientsince) / (365/12), 2), ROUND(SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice * (1 - salesorderdetails.discountpercent)), 2),
salesman.commissionrate1,
salesman.breakpoint,
salesman.commissionrate2, IF(DATEDIFF(NOW(), debtorsmaster.clientsince) / (365/12) <= salesman.breakpoint, salesman.commissionrate1, salesman.commissionrate2), ROUND(IF(DATEDIFF(NOW(), debtorsmaster.clientsince) / (365/12) <= salesman.breakpoint, salesman.commissionrate1, salesman.commissionrate2) * SUM(salesorderdetails.qtyinvoiced * salesorderdetails.unitprice * (1 - salesorderdetails.discountpercent)), 2)
FROM salesorders
INNER JOIN salesorderdetails ON salesorders.orderno=salesorderdetails.orderno
INNER JOIN debtorsmaster ON salesorders.debtorno=debtorsmaster.debtorno
INNER JOIN salesman ON salesorders.salesperson=salesman.salesmancode
GROUP BY salesmanname



RE: Report Builder - Prior Periods - phil - 03-03-2018 05:33 PM

No
Suggest you write a script for this ... start with one of the existing sales reports.


RE: Report Builder - Prior Periods - VortecCPI - 03-03-2018 10:30 PM

I actually did use the existing sales report to build the sales commission report which is why I mentioned it would be nice to have choices for prior ranges.

Our Sales Commission report is run at the first of every month for the prior month. It works great and looks great but I have to enter dates manually so a "Last Month" choice sure would be nice.

I started to look at the code but I figured I would ask first as I thought perhaps somebody in here may have already tackled it.

I will have a crack at it to see if I can add it myself and then share it with all of you...


RE: Report Builder - Prior Periods - VortecCPI - 03-03-2018 11:49 PM

Done.

Attached is a zip archive containing files for webERP 4.14.1 for review.

I added Yesterday, Last Week, Last Month, Last Quarter, and Last Year.

PLEASE NOTE my changes must be merged with those already in SVN!

Also... I corrected some errors with respect to the "This Week" period.

And... I corrected some errors with respect to criteria and font sizes.

And... It would be nice to reconcile WriteReport.inc with WriteForm.inc.

I hope others can also make use of these prior period selection choices...

[attachment=775]


RE: Report Builder - Prior Periods - SOLVED - TurboPT - 03-07-2018 09:21 AM

Report writer changes committed.


RE: Report Builder - Prior Periods - SOLVED - VortecCPI - 03-09-2018 06:34 AM

More work to these scripts.

I added Last Week To Date, Last Month To Date, Last Quarter To Date, and Last Year To Date periods.

So now we have:

+ Yesterday
+ Last Week
+ Last WTD
+ Last Month
+ Last MTD
+ Last Quarter
+ Last QTD
+ Last Year
+ Last YTD


RE: Report Builder - Prior Periods - SOLVED - TurboPT - 03-09-2018 03:31 PM

New changes committed.


RE: Report Builder - Prior Periods - SOLVED - VortecCPI - 03-10-2018 12:19 AM

Thank you for your patience with me and incremental commits Paul.

I only have limited time to work on this so I am doing it in small working pieces...