Labor Records by Week for Week Total Labor Hours

Epicor ERP 10
1

Hey all,

It was requested of me to create a dashboard for an employee’s weekly labor hours. It could be for a few weeks to a number of years. I am able to use DATEPART to get the week number, day of week, etc but for the life of me, I cannot figure out how to get the labor hours for each Sunday to Saturday week range… Any help would be wonderful and appreciated.

Thanks,
Chris

2

This post may help. (Searching the group will surface others).

3

You could display all of the LaborDtl records with the week number value (and year), and then in the dashboard use the group by and summaries.

4

Hi @SimpsonGranco ,

I don’t think you can get this in Epicor’s BAQ.
I have done this previously however through External BAQ.
Epicor’s BAQ is very limited and not very flexible (compared to raw SQL).

5

This is simply not true. Anything I have written by hand I’ve been able to convert to a BAQ. There may be 1% of tasks I was unable to do with a BAQ.

To @SimpsonGranco if you are getting the week number you can simply group on year and week number that is your saturday to sunday assuming that is how your localization is setup. We do exactly what you are talking about for a 2 week payroll export to our processor. It summarizes the 2 week period, auto calculates the OT and any differentials, all in a BAQ

2 Likes
6

Hi @jgiese.wci ,
What would be the best approach for pivoting in BAQ?
Converting values in a column to separate columns.
Something like this:

Considering the fact that values are dynamic and not known beforehand.

7

Now to figure out how to group by those two columns… Any examples?

8

Your original post indicates you are using 9.05.702A… true?

9

A solution without using a PIVOT is possible too.

Simply define your buckets with conditional expression columns. (pseudocode)

(Case when weekOfLaborEntry = currentWeek then laborhours else 0.00)
(Case when weekOfLaborEntry = currentWeek - 1 then laborhours else 0.00)
...

Now just group and subtotal.

10

Negative. 10.2.400.7

11

I like to use buckets too but… there are a couple questions I ask myself before I start down that road:
Do I want to select labor between start/end dates (start is always a Sunday)?
Can I use a static number of buckets? e.g. 8 buckets going back from end date and then 1 more bucket to sum any labor before that start date?

1 Like
12

This is usually the approach I take. I generally will use UBAQ Post GetList for some additional fancy if I need to.

1 Like