BAQ with PIVOT using conditional Aggregate

Epicor ERP 10
1

I’ve built a BAQ that shows me hours per employee per week. Works fine for starters.

BAQ1
BAQ1927×664 228 KB

I’m asked to just show the Overtime hours. per week per person.
I can see that the PIVOT Aggregated Formula is where where each sum is calculated.

BAQ2
BAQ2927×664 309 KB

So, I try to use the condition “case” in the Pivot Aggregated Expression Editor.

BAQ3
BAQ31119×659 296 KB

But if I put ANYTHING in there that mimics what I’d use in a calculated field the syntax is rejected on the first item.
(case when… will error on ‘(’
case when… will error on ‘case’

What I’m trying to use is:
(case when Sum(LaborHed_PayHours) > 40 then Sum(LaborHed_PayHours) -40 else 0 end)

Can anyone explain what I should be doing in the Expression Editor?
Or maybe I have to go at this with an entirely different approach?

Any help please?
Ben

10.1.500.26

Here is the working query phrase from the first screen shot:

[SubQuery1].[EmpBasic_Name] as [EmpBasic_Name],
[SubQuery1].[1] as [SubQuery1_1],
[SubQuery1].[2] as [SubQuery1_2],
[SubQuery1].[3] as [SubQuery1_3],
[SubQuery1].[4] as [SubQuery1_4],
[SubQuery1].[5] as [SubQuery1_5],
[SubQuery1].[6] as [SubQuery1_6],
[SubQuery1].[7] as [SubQuery1_7],
[SubQuery1].[8] as [SubQuery1_8],
[SubQuery1].[9] as [SubQuery1_9],
[SubQuery1].[10] as [SubQuery1_10],
[SubQuery1].[11] as [SubQuery1_11],
[SubQuery1].[12] as [SubQuery1_12]
from (select
[EmpBasic].[Name] as [EmpBasic_Name],
(DATEPART(WEEK, LaborHed.ClockInDate)) as [Calculated_Week],
[LaborHed].[PayHours] as [LaborHed_PayHours]
from Erp.LaborHed as LaborHed
inner join Erp.EmpBasic as EmpBasic on
LaborHed.Company = EmpBasic.Company
And
LaborHed.EmployeeNum = EmpBasic.EmpID
and ( EmpBasic.OTCalc_c = True )

where (LaborHed.ClockInDate > ‘01/01/2018’ and LaborHed.OTEX_c = False and LaborHed.ClockInDate < ‘12/31/2018’)
group by [EmpBasic].[Name],
(DATEPART(WEEK, LaborHed.ClockInDate)),
[LaborHed].[PayHours]) SubQuery1_src
PIVOT
(
Sum(LaborHed_PayHours)
FOR Calculated_Week in ([1],[ 10],[ 11],[ 12],[ 2],[ 3],[ 4],[ 5],[ 6],[ 7],[ 8],[ 9]) )
as SubQuery1

2

Ben,

Calculate your hours, then calculate the hours - 40 in a different field. In the SubQuery tab (below), enter your selection criteria for the > 40 hours based on the total hours calculated field.

Mark W.

3 Likes
3

Mark,
Thanks for taking the time to look at this.

My issue is that I’m not calculating any hours in the subquery.

Sub1
Sub1.jpg876×360 116 KB

I’m referencing the employee and the PayHours record and a Calculated Field is finding the week number:

Sub2
Sub2.jpg920×399 112 KB

The Pivot aggregate is summing the hours by week number. That’s why I was trying to apply a condition to the top level.

I am trying to add a calculated field in the subquery, like you suggest, but evaluating each record as greater than or less than 8 hours. I’m dealing with individual time records in the subquery. That is not giving me the results I need.

Sub3
Sub3.jpg1407×660 259 KB

Sub4
Sub4.jpg876×664 188 KB

I’m no longer showing one line per employee.
Ben

4

Sorry…
Here are the results after I switch the PIVOT aggregate from the Sum(PayHours) to the Sum(MoreThan8).

Sub5
Sub5.jpg876×664 186 KB

The individual number are making the 8 hour evaluation. But the sum per employee per week is not working.

Ben

5

Hey Ben,

What would be helpful is starting with the end in mind, can you mock up in Excel what you/they want and post that? What time frame can the user choose?

Mark W.

6

Mark,
I wish I had the time at the moment to mock something up. I don’t .
But if you look at the first screen shot you’ll see that I show the weekly total hours per employee. I need essentially the same with only overtime hours per week per employee.
Example my hours are W1 is 40.5, W2 is 39 and W3 is 44.
I want to show for me W1 = .5, W2 = 0, W3 = 4, etc.
That for each employee .
Thanks so much for looking into this,
Ben

7

Mock up

Excel1
Excel1.jpg1239×770 359 KB

8

Do you always want 6 weeks from the start date?

9

No. I will show the whole year once I get the data to show correctly. I’m just trying to be concise, here. It is very easy to add to the list of weeks in the PIVOT section.
They want a weekly and YTD total that dashbaord will sum for me.
Next year the grid will start over.
Ben

10

Is anybody else good with PIVOT aggregate functions?

Thanks for taking time to look at this Mark!

Ben

11

OK Ben, haven’t forgotten you. I think the secret is to break this into three queries. The lowest one is your labor detail: Put your date selection here maybe using a parameter.

image
image.png920×600 26.5 KB

Create a new inner-subquery and this is where you will pivot on the payroll week. You’ll have to enter 1-52 in your IN statement. (I just did a few for this example).

image
image.png1920×1040 41.8 KB

Now create your top-level query and pull in the laborsummary sub-query.

Here you’ll create a calculated field for the overtime for each week.

image
image.png1920×1080 84.8 KB

This should then show only the hours over 40 for each employee over each week. It’s rather lengthy to set up but once you do it, you’ll be all set. You could also break it into quarters if you like.

Hope that gets you started,

Mark

1 Like
12

Oh, the resulting SQL would look something like:

select
[EmpBasic].[Name] as [EmpBasic_Name],
((case when LaborSummary.Labor_1 <= 40 then 0 else LaborSummary.Labor_1 - 40 end)) as [Calculated_WK1],
…
((case when LaborSummary.Labor_52 <= 40 then 0 else LaborSummary.Labor_52 - 40 end)) as [Calculated_WK52],
from Erp.EmpBasic as EmpBasic
inner join (select
[Labor].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
[Labor].[1] as [Labor_1],
…
[Labor].[52] as [Labor_52]
from (select
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
(DATEPART(ISO_WEEK, LaborDtl.PayrollDate)) as [Calculated_LaborWeek],
[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs]
from Erp.LaborDtl as LaborDtl) Labor_src
pivot
(sum (LaborDtl_LaborHrs)
for Calculated_LaborWeek in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10], … , [51],[52]) )
as Labor) as LaborSummary on
EmpBasic.EmpID = LaborSummary.LaborDtl_EmployeeNum

13

Mark,
Thanks for digging in some more. I just got back from vacation, and it is a pleasant sight to see more work from you on my issue. As I was on the chairlift between snowboard runs, I think I was edging towards the same conclusion. It will take three queries.
I have not constructed the revised BAQ based on your direction yet. But I feel you have shown me the correct way. Once I wade through my inbox and get to building this, I’ll mark it as the solution.
Thanks again!
Ben

14

You’re two-thirds there. Make your current top level a inner-subquery and create a new Top Level and create the new Calculated Fields and you’re the hero.

Mark W.

15

Hi Ben,

Could you shed some light on how you calculated the week from dates? I am trying to do something similar with order date and group them by weeks. I am struggling to create weeks like you have in calculated_week field. Also, is that a dynamic field like if I am trying to look at past 12 months, would the week one adjust to one week later if I run the report next week?

Any help would be greatly appreciated.

Thanks,
Prakrit

16

If you are in a baq you can use SQl. It’s just the week number of the year. 1-53 (an extra week in there because of the changeover by year I think, test it out to see)

image

2 Likes
17

Brandon,

That worked perfectly. Thank you.

1 Like