Epicor version 9.05.701
Hi, this time there is no problem ;-), I’m just looking for suggestions and ideas.
- The project will be to create a report to show suppliers on time delivery.
- Following point 1) they need to be given a score so that the supply chain manager can review how good the suppliers are.
Does anyone already use the above?
What format did you choose? Crystal? Dashboard? Excel?
Anybody that would like to share what they use ( BAQs or screenshots etc) - it would be greatly appreciated.
Thanks in advance.
What I did was create a BAQ against the PO Receipt lines. A column was to calculate the due date vs the receipt date and then a on-time logical column based on standard criteria.
As an example if the company standard is within 0 days early and 2 days late the like was set to ‘O’ else if Early then ‘E’ and if Late then L.
Then using the BAQ created a dashboard with a supplier filter and receipt date cutoff.
Three panels on the dashboard (on-time receipts, Early receipts and Late receipts).
There is your start right there.
Would you be so kind to export & attach your BAQ & dashboard (if your using the same version, 9.05.701) so I could take a look?
Roel Martinez posted the attached files on the yahoo group back in 2010, which you might find useful. There are 2 crystal files and 2 BAQ xml files.
STSVndrScore.xml (4.6 KB)
STSVndrScoreSum.rpt (51.5 KB)
STSVndrScore.rpt (52 KB)
SCC-STSVndrScore.xml (118.9 KB)
I made a “Supplier Metrics” report with the following:
- Rcpt Qty (qty received against PO Release)
- Running total rcvd to PO relese
- Running short
- Days late (negative is early)
- Average late (average of days late. Might need work as you may want to have “early” receipts count as 0 days late, or weight it based on qty. Right now, getting 1 pc 10 days early, and 49 pcs’ 10 days late, averages to 0 days late).
- Max days late
I can post the BAQ and SSRS report (just realized you’re E9, sorry).
Edit: Even though you’re E9, I could export the BAQ’s SQL statement, and you could see what I did there, and try to duplicate in E9’.
I quite like this metric and report…thank you for the info.
- Yes if the table headers/fields are the same it should be quite easy to put together a BAQ…if you could attach that would be fab.
Thank you so much.
Here’s the SQL from the BAQ. I don’t recall how much processing I did in the report…
SupMet2.SQL.txt (1.6 KB)
Edit: And here’s the formulas for some of the fileds that are calculated inside the report:
SupMet_RptCalc Fields.txt (1.6 KB)
The data is starting to come together, just having a few problems with the running totals for:
“Received Qty Running Total” and the “Quantity short” calculation…
I did both of those in the Report.
For the running total, you’d probably need a grouping on the PO, Line and Rel.
And all of this possible in CR, as this was originally created in V8 for Crystal Reports
I probably could if I still had access but I don’t.
It should be fairly simple though. POReceiptDetail to PORelease to PODetail to POHeader to Vendor
Create a calculated field calculating the number of days between the PO due date on the PO release and the PO Reecipt.
Create a calculated field if the number of days creates an “E,L or O” field depending on your on-time standards such as:
IF NumofDays <=0 then (Early Standard) Then “E”
ELSE IF Num of Days >= (Late Standard) Then “L”
From there you can build the dashboard panels filtering on the calculated field you created for the code. Or just have one on-time panel color coded based on the field code.
Do you happen to have the BAQ from Epicor ERP 10 by chance that you could post here?
You get brownie points for an excellent use of greenshot.
Here you go … I don’t think there are any UD fields.
CK-SupMet3.baq (26.1 KB)
One thing though, I probably do a lot of the heavy calcs in the RLD
here’s the RDL
CK-SupMet3.rdl (85.2 KB)
When I did this for a client they were measuring other metrics already and they had the SRM module, which provides fields for rating On Time, Quality and other things. Their metrics had some flexibility one if something was On Time or not. They wanted the On Time, Quality and other ratings to auto update. So we setup a few BPM’s on Receipt Entry and DMR processing and others to calculate the Rate per their definition and then a Dashboard to show all results. They wanted to then send out a Supplier Rate Report once a quarter with comments. So we setup a Memo Category for ‘SRM’ and they would enter SRM Memo’s in Supplier Entry throughout the quarter. We then created the SSRS report to pull all the data per the Date Rate parameter which included all the SRM memos.
Theses “flexibilities” are usually realized once you start to answer their questions with your own.
Purch Mgr: “It needs to show how many days late the receipt was”
Me: “How should we handle incomplete shipments?”
Purch Mgr: “What do you mean?”
Me: “Say the order was for 100 due on the 5th, and we receive 95 on the 4th, and the last 5 on the 7th. Was that on time? Mostly on time?, or All late? And what if we’re still waiting for those last 5?”
Purch Mgr: “Hmmm … I hadn’t considered that.”
This type of conversation happens to me at least weekly with clients.