Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Report Builder - Prior/Future Periods - SOLVED
03-01-2018, 12:17 AM (This post was last modified: 03-10-2018 05:44 AM by VortecCPI.)
Post: #1
Report Builder - Prior/Future Periods - SOLVED
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.

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
03-01-2018, 10:03 AM
Post: #2
RE: Report Builder - Prior Periods
You can use the sales analysis reporting for this.

Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Visit this user's website Find all posts by this user
Quote this message in a reply
03-02-2018, 02:12 PM (This post was last modified: 03-02-2018 02:13 PM by VortecCPI.)
Post: #3
RE: Report Builder - Prior Periods
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

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
03-03-2018, 05:33 PM
Post: #4
RE: Report Builder - Prior Periods
No
Suggest you write a script for this ... start with one of the existing sales reports.

Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Visit this user's website Find all posts by this user
Quote this message in a reply
03-03-2018, 10:30 PM (This post was last modified: 03-03-2018 11:56 PM by VortecCPI.)
Post: #5
RE: Report Builder - Prior Periods
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...

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
03-03-2018, 11:49 PM (This post was last modified: 03-07-2018 05:43 AM by VortecCPI.)
Post: #6
RE: Report Builder - Prior Periods
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...

   


Attached File(s)
.zip  reportwriter.zip (Size: 15.32 KB / Downloads: 7)

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
03-07-2018, 09:21 AM
Post: #7
RE: Report Builder - Prior Periods - SOLVED
Report writer changes committed.
Find all posts by this user
Quote this message in a reply
03-09-2018, 06:34 AM (This post was last modified: 03-09-2018 06:38 AM by VortecCPI.)
Post: #8
RE: Report Builder - Prior Periods - SOLVED
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


Attached File(s)
.zip  reportwriterNEW.zip (Size: 15.76 KB / Downloads: 3)

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
03-09-2018, 03:31 PM
Post: #9
RE: Report Builder - Prior Periods - SOLVED
New changes committed.
Find all posts by this user
Quote this message in a reply
03-10-2018, 12:19 AM
Post: #10
RE: Report Builder - Prior Periods - SOLVED
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...

https://www.linkedin.com/in/eclipsepaulbecker
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)