Hello,
I am trying to create a BAQ that will display the resources on a job. I had this working fine until we switched to capability scheduling, and cannot figure out the proper links with the tables to have the resources of the operations display.
Has anybody been able to successfully display resources in a BAQ (job specific) that were scheduled using capabilities, and if so can you please share your table links?
I think you could get away with just JobHead > JobAsmbl > JobOper > JobOpDtl.
Display the Job, Asm, OpSeq, Capability, Resource Group, and Resource ID from the OpDtl table.
Something like this:
JobResources.baq (24.7 KB)
select
[JobOpDtl].[JobNum] as [JobOpDtl_JobNum],
[JobOpDtl].[AssemblySeq] as [JobOpDtl_AssemblySeq],
[JobOpDtl].[OprSeq] as [JobOpDtl_OprSeq],
[JobOpDtl].[CapabilityID] as [JobOpDtl_CapabilityID],
[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
[JobOpDtl].[ResourceID] as [JobOpDtl_ResourceID]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
where (JobHead.JobClosed = false)
If you need a bit more information about what is scheduled for a job, you can use a query that links to the ResourceTimeUsed table. I use this when digging into details:
jobschedule.baq (36.5 KB)
I hope this helps!
Good Luck!
Thanks for the reply Nate!
Unfortunately the JobResources.baq won’t give me the actual name of the resource and when I try linking the resource and selecting description I only get names for resources scheduled outside of capability scheduling (red blanked out fields).
Once I figure out how I can take your other .baq file and upload it, I will see if that gets me what I need.
Yes. The Job tables can only show you the capability, not the underlying resource that gets scheduled by the scheduling engine. After your job has been scheduled, the resource information can be pulled from the ResourceTimeUsed Table.
Note, that the RTU table is a real pain to work with. Notably, it contains both WhatIf and Actual load information. So make sure to filter by WI = false to get your real time info. The load data is stored in concatenated cells. There is some trickery to pull this apart and get useful information from it.
You can import BAQs by going to your BAQ Designer > Actions > Import BAQ. That last BAQ I posted doesn’t actually pull the resource like I thought.
Here is a BAQ that pulls the resource from the RTU table. This uses some of the magic folks on here pointed me to.
RTU_LoadTotals.baq (71.8 KB)
Thanks again,
That is what I thought, but am still unable to import. I think it may have to do with the version the BAQ is in?
Would you possibly be able to paste the detail like you did for the first BAQ?
Also just to note, we’re on 10.2.700 so not sure why it says 4.2.200
Ohhh that dang thing! Let’s not get started on versioning… The bane of my… ok here it is…
This BAQ likes to time out if you give it too wide of a range. So, SubQuery4 filters it by a set of resource groups that I care about. You will want to change this. There are also start/end date parameters that filter for the actual date the load is scheduled for. Play it safe and choose only a week or two to start before opening it up to a larger range.
Good luck! Rebuilding it from this will be no small task!
select
[SubQuery4].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
[SubQuery4].[Resource_ResourceGrpID] as [Resource_ResourceGrpID],
[SubQuery4].[Calculated_LoadMonth] as [Calculated_LoadMonth],
[SubQuery4].[JobHead_JobNum] as [JobHead_JobNum],
[SubQuery4].[JobHead_PartNum] as [JobHead_PartNum],
[SubQuery4].[JobHead_ProdQty] as [JobHead_ProdQty],
[SubQuery4].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery4].[JobOper_OprSeq] as [JobOper_OprSeq],
[SubQuery4].[JobOper_RunQty] as [JobOper_RunQty],
[SubQuery4].[JobOper_ProdStandard] as [JobOper_ProdStandard],
[SubQuery4].[Calculated_EarnedHours] as [Calculated_EarnedHours],
(sum(cast(SubQuery4.Calculated_LoadHours as decimal (5,2)))) as [Calculated_TotalLoad]
from (select
[SubQuery1].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
[SubQuery1].[Resource_ResourceGrpID] as [Resource_ResourceGrpID],
(datepart(month, SubQuery1.ResourceTimeUsed_LoadDate)) as [Calculated_LoadMonth],
[SubQuery1].[JobHead_JobNum] as [JobHead_JobNum],
[SubQuery1].[JobHead_PartNum] as [JobHead_PartNum],
[SubQuery1].[JobHead_ProdQty] as [JobHead_ProdQty],
[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
[SubQuery1].[JobOper_OprSeq] as [JobOper_OprSeq],
[SubQuery1].[JobOper_RunQty] as [JobOper_RunQty],
[SubQuery1].[JobOper_ProdStandard] as [JobOper_ProdStandard],
((SubQuery1.JobOper_RunQty * SubQuery1.JobOper_ProdStandard)/60) as [Calculated_EarnedHours],
(SubQuery1.Calculated_DayLoadHours) as [Calculated_LoadHours]
from (select distinct
[Resource].[ResourceGrpID] as [Resource_ResourceGrpID],
[ResourceGroup].[Description] as [ResourceGroup_Description],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
([Ice].entry(RowCount.Calculated_RowNum, RTU.ResourceTimeUsed_LoadHours,'~')) as [Calculated_DayLoadHours],
[RowCount].[Calculated_RowNum] as [Calculated_RowNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[RTU].[ResourceTimeUsed_LoadDate] as [ResourceTimeUsed_LoadDate],
[RTU].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
[JobOper].[RunQty] as [JobOper_RunQty],
[JobOper].[ProdStandard] as [JobOper_ProdStandard],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobOper].[OprSeq] as [JobOper_OprSeq]
from (select
[ResourceTimeUsed].[Company] as [ResourceTimeUsed_Company],
[ResourceTimeUsed].[JobNum] as [ResourceTimeUsed_JobNum],
[ResourceTimeUsed].[AssemblySeq] as [ResourceTimeUsed_AssemblySeq],
[ResourceTimeUsed].[OprSeq] as [ResourceTimeUsed_OprSeq],
[ResourceTimeUsed].[OpDtlSeq] as [ResourceTimeUsed_OpDtlSeq],
[ResourceTimeUsed].[WhatIf] as [ResourceTimeUsed_WhatIf],
[ResourceTimeUsed].[AllocNum] as [ResourceTimeUsed_AllocNum],
[ResourceTimeUsed].[ResourceGrpID] as [ResourceTimeUsed_ResourceGrpID],
[ResourceTimeUsed].[ResourceID] as [ResourceTimeUsed_ResourceID],
[ResourceTimeUsed].[StartDate] as [ResourceTimeUsed_StartDate],
[ResourceTimeUsed].[StartTime] as [ResourceTimeUsed_StartTime],
[ResourceTimeUsed].[EndDate] as [ResourceTimeUsed_EndDate],
[ResourceTimeUsed].[EndTime] as [ResourceTimeUsed_EndTime],
[ResourceTimeUsed].[Lock] as [ResourceTimeUsed_Lock],
[ResourceTimeUsed].[LoadDate] as [ResourceTimeUsed_LoadDate],
[ResourceTimeUsed].[LoadHour] as [ResourceTimeUsed_LoadHour],
[ResourceTimeUsed].[EstHours] as [ResourceTimeUsed_EstHours],
[ResourceTimeUsed].[ActualHours] as [ResourceTimeUsed_ActualHours],
[ResourceTimeUsed].[LoadDays] as [ResourceTimeUsed_LoadDays],
[ResourceTimeUsed].[LoadHours] as [ResourceTimeUsed_LoadHours],
[ResourceTimeUsed].[RestoreFlag] as [ResourceTimeUsed_RestoreFlag],
[ResourceTimeUsed].[SysDate] as [ResourceTimeUsed_SysDate],
[ResourceTimeUsed].[SysTime] as [ResourceTimeUsed_SysTime],
[ResourceTimeUsed].[JobEngineered] as [ResourceTimeUsed_JobEngineered],
[ResourceTimeUsed].[DailyProdQty] as [ResourceTimeUsed_DailyProdQty],
[ResourceTimeUsed].[SysUser] as [ResourceTimeUsed_SysUser],
[ResourceTimeUsed].[SysRowID] as [ResourceTimeUsed_SysRowID]
from Erp.ResourceTimeUsed as ResourceTimeUsed
where (ResourceTimeUsed.LoadDate >= @StartDate and ResourceTimeUsed.LoadDate <= @EndDate)) as RTU
inner join Erp.Resource as Resource on
Resource.Company = RTU.ResourceTimeUsed_Company
and Resource.ResourceID = RTU.ResourceTimeUsed_ResourceID
inner join Erp.ResourceGroup as ResourceGroup on
Resource.Company = ResourceGroup.Company
and Resource.ResourceGrpID = ResourceGroup.ResourceGrpID
inner join (select
(ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)) as [Calculated_RowNum]
from Ice.ZDataField as ZDataField) as RowCount on
RowCount.Calculated_RowNum <= [Ice].num_entries(RTU.ResourceTimeUsed_LoadDays,'~')
inner join Erp.JobOper as JobOper on
JobOper.Company = RTU.ResourceTimeUsed_Company
and JobOper.JobNum = RTU.ResourceTimeUsed_JobNum
and JobOper.AssemblySeq = RTU.ResourceTimeUsed_AssemblySeq
and JobOper.OprSeq = RTU.ResourceTimeUsed_OprSeq
inner join Erp.JobAsmbl as JobAsmbl on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobProd as JobProd on
JobProd.Company = JobAsmbl.Company
and JobProd.JobNum = JobAsmbl.JobNum
inner join Erp.JobHead as JobHead on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
and ( JobHead.JobClosed = false )) as SubQuery1
where (SubQuery1.Resource_ResourceGrpID in ('2B', '4A', 'MCU'))) as SubQuery4
group by [SubQuery4].[ResourceTimeUsed_WhatIf],
[SubQuery4].[Resource_ResourceGrpID],
[SubQuery4].[Calculated_LoadMonth],
[SubQuery4].[JobHead_JobNum],
[SubQuery4].[JobHead_PartNum],
[SubQuery4].[JobHead_ProdQty],
[SubQuery4].[JobAsmbl_PartNum],
[SubQuery4].[JobOper_OprSeq],
[SubQuery4].[JobOper_RunQty],
[SubQuery4].[JobOper_ProdStandard],
[SubQuery4].[Calculated_EarnedHours]
order by SubQuery4.ResourceTimeUsed_WhatIf, SubQuery4.JobHead_PartNum, SubQuery4.JobAsmbl_PartNum, SubQuery4.JobOper_OprSeq
Thanks a lot Nate! I will see how it goes; this may be a good weekend project 
I will for sure report back with any other questions or to confirm it worked!
Okay so I am close but must be missing something simple on the joins. I have duplicate data and am not sure where it’s coming from. All of the information pulling in appears correct; just have the duplicate rows.
Any thoughts?
I think I got it!
Thanks again for your help!