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.
You can use the sales analysis reporting for this.
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
No
Suggest you write a script for this ... start with one of the existing sales reports.
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...
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]
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
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...