Weekly to Bi-Weekly Payroll BAQ Issue

Epicor ERP 10
, ,
1

Hi Everyone,

We recently moved from weekly pay to bi-weekly pay and only use Epicor to provide a total for pay hours which are then exported, then imported into a separate payroll application.

Our weekly payroll BAQ was built by joining LaborHed, EmpBasic and JCDept tables together. We use a calculated field to produce the week number, then use additional calculated fields for each day of the week to return the LaborHed.PayHours as follows " when datename(dw, LaborHed.PayrollDate) = ‘Monday’ ", etc. A simple sum of the calculated fields for each day of the week provides us with a total. We then use an Over Time calculated field to return total overtime by subtracting 44 hours when the total hours for the week exceed 44.

Mockup of data returned for weekly payroll BAQ is as follow:

WeeklyExample
WeeklyExample1535×141 47.2 KB

Now, I’m trying to build the same BAQ for BiWeekly pay and am running into an issue.

I have the following Queries.

Query 1 = Type: InnerSubQuery - BAQ is a duplicate of my weekly pay BAQ and is being used to display payroll Week 1’s Hours.

Query 2 = Type: InnerSubQuery - BAQ is a duplicate of my weekly pay BAQ, however, one calculated field was added that decreases the WeekNum by 1. Query is being used to display Week 2’s Hours.

Query 3 = Type: TopLevel - Query 1 and Query 2 are full join on fields Company, EmployeeNum, Calc_YearNum and Calc_Weeknum (For Calc_weeknum, Query 1 is using the actual weeknum based on LaborHed.PayrollDate, Query 2 is using the same, then is decreased by 1, so that Week 2’s hours are displayed)

When executing, Query 1’s data is displaying correctly, however, Query 2, is only displaying as many rows as Query 1 has, resulting in missing rows most of the time)

Any suggestions on how I can ensure all rows from Query 1 and 2 are being displayed.

Mock up of the data I’m looking to return on the bi-weekly payroll BAQ is as follows:

BiWeeklyExample
BiWeeklyExample2553×60 35.9 KB

Thanks

2

InnerSubQueries… using open joins at the TopLevel?

Wondering if users enter parameter(s) when running the BAQ?

And… it might be easier for someone to help if you re able to post a copy of your BAQ?

3

Thanks @bordway

I’ve attached a copy of the BAQ.

Users do not enter parameters when running the BAQ. I will be turning this into a dashboard with options for specifying a date range for payroll or possibly a pay period

PayrollBiWk.baq (51.7 KB)

4

I looked at the BAQ and the problem seems to be in the criteria of weeknum joins. Any employee who did not work in week one but worked in week 2 is not going to show up in your baq (That would be tough on payroll!). I would guess you need some sort of a criteria in each of the subqueries.

one possibility could be Creating parameters and passing them to both sub-queries for start and end date on that two-week cycle or just one date. Since you are setting this up as a dashboard that could be a potential solution with no weeknum criteria.

5

Thanks @prakritnepal

I’ll give parameters a try.