BAQ to Capture MTD & YTD

Epicor ERP 10
,
1

I am working on a complicated BAQ which the end result needs to provide a YTD and MTD total for scrap entries from the labor dtl records. My apologies in advance for the long winded description

on the labor detail records we have a customization that allows the floor to report multiple quantities and scrap reason for one labor dtl record. We are using the Number, ShortChar and Character fields to capture the information. They can complete the operation and report up to 9 different scrap reasons and quantities. The Scrap reason code ties to a “DeptCharged” for the scrap in my BAQ and I want to report MTD and YTD totals grouped by DeptCharged

I’ve been able to get either a YTD or a MTD by DeptCharged, but not both.

Subqueries are joined to the Top by UnionAll

At the top level I sum the total scrap cost by DeptCharged. The value of the scrap is captured and placed in Character 10 based on the operation completed. Since this is a NavChar field I had to cast it to a decimal to be able to sum it.

*** sum (Dtl10.LaborDtl_Number10 *(Cast (Dtl10.LaborDtl_Character10 as money))) ***

So this will get me the YTD total. And this is all working great and I have verified the results are valid.

My first attempt to get the MTD total I created a calculated PayMonth field on each of the subqueries and then a RptMnth calculated field on the top level. Then wrote a statement to sum MTD if RptMnth and PayMonth were =. This didn’t work
image

image
image817Ă—228 49.9 KB

Next I tried adding another set of subqueries that would pull just the labor dtl from the month and join that to the top level. I quickly realized this wouldn’t work either or maybe I was doing it wrong, but I was getting all kinds of errors and basically made a mess of my very long BAQ.

In the end I want to create a BAQ Report that will generate on the first of the month to report for previous month YTD and MTD totals.

Any suggestions or direction would be appreciated!
Val Feldt

2

If you want a running sum, you can use windowing functions to add up the numbers in a column.

First get the part you want to separate by (for month, you might actually want year and month… but the example still works)

image
image953Ă—475 24.2 KB

The to get a running sum, you make a formula like this.

sum({what you are adding up}) over (partition by {separator} order by {usually a date})

image
image842Ă—420 24.3 KB

This will give you a running sum in your grid.

1 Like
3

I would use CTE subqueries.

Make sure the subqueries are above the “top” query in the query list.

image

4

Christopher
for clarification, are you stating that my subqueries need to be CTE and be above the TopLevel?

image
image695Ă—541 55.1 KB

5

Thanks, I tried this but I’m not having any success - I’m sure I’m not doing something right. I used your example using the PayYear on the subqueries. That didn’t work so I put the PayYear on the Top level and wrote the same sum statement using the PayYear from the top level. I’m getting errors with group aggregate.

6

Scrap.baq (106.6 KB)

Attached is a concept BAQ. It is not fully fleshed out, and it isn’t technically doing year to date, but maybe it can give you a nudge in the right direction.

I have found that if you are using calculated fields, all the other fields need to be set to Group By. Most of the time.

1 Like
7

Thank you so much! that absolutely put me in the right direction. I had never used a CTE query before. I knew they existing but didn’t exactly know how/when to use them.

thank you… My report is working beautifully and the knowledge will help me with so much more.
have a great day
val