I’m trying to make a simple bar chart showing Job Operation Estimated Hrs vs Actual Hrs.
The problem I’m having is EDD always wants to SUM the Estimated Hrs.
In my case, I have an op with Estimated Hrs = 16
There have been (12) LaborDtl entries against that Operation… so the bar on my bar chart ends up with a value of 192 (16*12).
Is there any way to NOT have EDD want to sum values?
“Help” states that: “Measures are aggregate calculations such as Sum, Average, or Minimum used to roll up a numeric value.” But I haven’t found a way to switch between Sum & Average. If I could use average, I would get the correct value.
I thought I could potentially use a calculated field in my BAQ for example:
Est. Hrs / count(Labor Entries) would then result in a sum of the original Est. Hrs. value… but I haven’t been able to figure that one out.
What I need is a “measure” to be a static value and not a SUM, but EDD seems severely limited in actually getting what you want to display.
Unfortunately I can’t help with your question, but just wanted to mention, I had the same issue with EDD summing the values today and couldnt find where to set it to an average.
I would suggest add a calculated field and divide the value by the number of ops.
I’ve found EDD to be extremely basic and it’s been frustrating trying to get out some simple graphical views, almost everything I’ve struggled with in EDD has meant going back and rewriting the BAQ…
Its a pretty ugly BAQ because I’ve been attempting so many things (work-arounds)… changing ints to strings, etc. to see if EDD would allow me to then use them as Dimensions instead of Measures.
a lot of trial-and-error going on.
I wasn’t sure if I needed to put LaborDtl in a subquery. I tried that briefly but revert back to a single query since it didn’t seem to make a difference.
Apologies I’m probably not going to be much help. I’ve had a quick play and initially struggled as your pulling from tables we don’t have in our system. When I thought I had figured it out I then couldn’t get the BAQ syntax right… so tried chatGPT
Heres what chatGPT suggested… how to turn it into a BAQ is beyond me right now though.
WITH LaborHrsCount AS (
SELECT
JobOper.JobID,
COUNT(LaborDtl.LaborHrs) AS LaborHrsCount
FROM
JobOper
LEFT JOIN
LaborDtl ON JobOper.JobID = LaborDtl.JobID
GROUP BY
JobOper.JobID
)
SELECT
JobOper.JobID,
SUM(JobOper.EstProdHours) / LaborHrsCount AS Result
FROM
JobOper
LEFT JOIN
LaborHrsCount ON JobOper.JobID = LaborHrsCount.JobID
GROUP BY
JobOper.JobID, LaborHrsCount.LaborHrsCount;
In this example, the LaborHrsCount CTE calculates the count of LaborDtl.LaborHrs for each JobID , and then the outer query uses this count to perform the division in the SELECT clause.
Appreciate the help. After some more trial & error this morning, I was able to get it to work. I had to convert “EstProdHours” to a string, and surprisingly, EDD allowed me to add it as a column in my chart.
Very finicky. I also had to make my LaborHrs a calculated field in my BAQ to populate a zero value in cases where there were no entries.
(case when (LaborDtl.LaborHrs) is null then 0 else LaborDtl.LaborHrs end)
Otherwise, it wouldn’t plot on the graph. I get the impression Epicor has abandoned EDD and is just pushing paid products like EDA and BI so those outside sources can do the heavy lifting.
EDA, Grow, PowerBI, etc. generally work on copies of data. EDD only works with BAQs. It is more for realtime information where the other “heavy lifting” is for analysis over longer periods. At least, that’s how I look at it.
