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
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!