I created a BAQ showing all hours clocked by employee (direct and indirect). When I added in a subquery to pull in Job prod code (and description) and if the job is complete or closed - then the report only shows direct clock ins and no longer shows indirect time.
Any idea how to resolve this? I’m assuming I linked something incorrectly - just not sure what. I’m pretty green with subqueries. Thanks in advance for the help!
Change your join type on Labor detail to Sub query. Click on the link between the two and update from Matching rows to All rows from Labor Detail.
When I do that then group or product group doesn’t show at all.
In addition my data parameter set up doesn’t work either.
Can you post your query
The Query phrase?
select
[LaborDtl].[Company] as [LaborDtl_Company],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[JOB_INFO].[JobHead_ProdCode] as [JobHead_ProdCode],
[JOB_INFO].[ProdGrup_Description] as [ProdGrup_Description],
[EmpBasic].[Name] as [EmpBasic_Name],
[EmpBasic].[JCDept] as [EmpBasic_JCDept],
[LaborDtl].[ExpenseCode] as [LaborDtl_ExpenseCode],
[EmpBasic].[ExpenseCode] as [EmpBasic_ExpenseCode],
[LaborDtl].[JCDept] as [LaborDtl_JCDept],
[JCDept].[Description] as [JCDept_Description],
[JCDept].[JCDept] as [JCDept_JCDept],
[LabExpCd].[Description] as [LabExpCd_Description],
[LabExpCd].[ExpenseCode] as [LabExpCd_ExpenseCode],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborHed].[ClockInDate] as [LaborHed_ClockInDate],
[LaborDtl].[ClockinTime] as [LaborDtl_ClockinTime],
[LaborDtl].[EarnedHrs] as [LaborDtl_EarnedHrs],
[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
[LaborDtl].[BurdenHrs] as [LaborDtl_BurdenHrs],
[LaborDtl].[IndirectCode] as [LaborDtl_IndirectCode],
[LaborDtl].[ResourceGrpID] as [LaborDtl_ResourceGrpID],
[LaborDtl].[ResourceID] as [LaborDtl_ResourceID],
[LaborDtl].[OpCode] as [LaborDtl_OpCode],
[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
[LaborDtl].[ScrapQty] as [LaborDtl_ScrapQty],
[LaborDtl].[DiscrepQty] as [LaborDtl_DiscrepQty],
[LaborDtl].[LaborNote] as [LaborDtl_LaborNote],
[JOB_INFO].[JobHead_JobClosed] as [JobHead_JobClosed],
[JOB_INFO].[JobHead_JobComplete] as [JobHead_JobComplete]
from Erp.LabExpCd as LabExpCd
inner join Erp.LaborDtl as LaborDtl on
LabExpCd.Company = LaborDtl.Company
and LabExpCd.ExpenseCode = LaborDtl.ExpenseCode
and ( LaborDtl.ClockInDate >= @BEGIN_DATE and LaborDtl.ClockInDate <= @END_DATE )
inner join Erp.LaborHed as LaborHed on
LaborDtl.Company = LaborHed.Company
and LaborDtl.LaborHedSeq = LaborHed.LaborHedSeq
inner join Erp.EmpBasic as EmpBasic on
LaborHed.Company = EmpBasic.Company
and LaborHed.EmployeeNum = EmpBasic.EmpID
left outer join Erp.JCDept as JCDept on
LaborDtl.Company = JCDept.Company
and LaborDtl.JCDept = JCDept.JCDept
inner join (select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[ProdCode] as [JobHead_ProdCode],
[PROD_CODE].[ProdGrup_Description] as [ProdGrup_Description],
[JobHead].[JobComplete] as [JobHead_JobComplete],
[JobHead].[JobClosed] as [JobHead_JobClosed]
from Erp.JobHead as JobHead
inner join (select
[ProdGrup].[ProdCode] as [ProdGrup_ProdCode],
[ProdGrup].[Description] as [ProdGrup_Description]
from Erp.ProdGrup as ProdGrup
group by [ProdGrup].[ProdCode],
[ProdGrup].[Description]) as PROD_CODE on
PROD_CODE.ProdGrup_ProdCode = JobHead.ProdCode
group by [JobHead].[JobNum],
[JobHead].[ProdCode],
[PROD_CODE].[ProdGrup_Description],
[JobHead].[JobComplete],
[JobHead].[JobClosed]) as JOB_INFO on
JOB_INFO.JobHead_JobNum = LaborDtl.JobNum
group by [LaborDtl].[Company],
[LaborDtl].[JobNum],
[JOB_INFO].[JobHead_ProdCode],
[JOB_INFO].[ProdGrup_Description],
[EmpBasic].[Name],
[EmpBasic].[JCDept],
[LaborDtl].[ExpenseCode],
[EmpBasic].[ExpenseCode],
[LaborDtl].[JCDept],
[JCDept].[Description],
[JCDept].[JCDept],
[LabExpCd].[Description],
[LabExpCd].[ExpenseCode],
[LaborDtl].[ClockInDate],
[LaborHed].[ClockInDate],
[LaborDtl].[ClockinTime],
[LaborDtl].[EarnedHrs],
[LaborDtl].[LaborHrs],
[LaborDtl].[BurdenHrs],
[LaborDtl].[IndirectCode],
[LaborDtl].[ResourceGrpID],
[LaborDtl].[ResourceID],
[LaborDtl].[OpCode],
[LaborDtl].[LaborQty],
[LaborDtl].[ScrapQty],
[LaborDtl].[DiscrepQty],
[LaborDtl].[LaborNote],
[JOB_INFO].[JobHead_JobClosed],
[JOB_INFO].[JobHead_JobComplete]
The issue is with your inner join on the sub-query. Try using a left outer join which should return all labor activity and not just the ones associated with a job (direct).
Adam
As Adam_Jones stated, your Join is currently set to all “Matching Rows from JOB_INFO and LaborDtl” (In SubQuery1) ~ set the Join to “All rows from LaborDtl” and that should pull in your Indirect Labor as well.
Currently it only displays records where matching rows exist on both tables, and there will never be Indirect Labor associated with Job Labor records.
I have changed the join to “all rows from LaborDtl” and that seems to pull all the data in (THANK YOU!!)- however now my date parameter isn’t working (filter box pops up but doesn’t actually filter) and pulls in all data (until it reaches the line max). I have the data parameter on “Erp.LaborDtl” - is there something else I should be doing here?
I have also lost some of my data from subqueries (group number, group code description, job part number ,etc).
Example below of the data. I pulled for 1/31/20-2/7/20. As you see there is data coming from outside the parameter and not all fields (for the direct job lines) are populated with information (group number, description, etc).
Sometime making adjustments to query joins and criteria can play a little havoc inside the BAQ. After saving, close and reopen the BAQ; check all your filters and joins again - if all seem to be correct, and still getting the same results - try removing and re-adding your date parameters. Without having the full BAQ to analyze and look at, it’s a little difficult to troubleshoot.
Not sure if you’re on-site or cloud hosted; if on-site, I typically try to reproduce the query in SQL Server Management Studio, as it’ll give me much more meaningful feedback as to what issues my query/inner-query(ies) might be having.
You can also try creating you inner queries independently, to see what results they return before joining into the upper level queries. More than likely it has to do with groups or joins - but verify the Date parameters first (iron out as to why records are returning outside of the Date Ranges).
It’s easier to diagnose when you have more limited records against which to analyze.
Sorry I cannot provide more direct guidance at this moment - narrowing down issues in three-layer BAQ’s can be a bit challenging - and it usually ends up being something rather minor which doesn’t jump out immediately.
Thanks! I deleted the date parameter and all data showed - including the subquery information (job part number, description, etc).
Adding the date parameter back onto the LaborDtl (using Clock in Date) and I was getting the same error and not displaying all information.
I was able to move the date parameter onto LaborHed (using Clock in Date) and it works. Not sure why LaborDtl wouldn’t work - but I’m getting the accurate data now.
Thanks all for you help and helping to narrow down these issues!
It would seem intuitive that with LaborDtl being the table all the other tables are linking to, that setting the date parameters there would provide the correct filtered results… however it isn’t the first time I’ve encountered such counter-intuitive results. Happy to hear the BAQ is now working correctly! You’ll be an Expert BAQ Designer in no-time! Welcome to the wonderful world of BAQ troubleshooting. 
