Good morning,
I have a really ugly BAQ that pulls data from the Resource Time Used table, rolls it up by resource group, job, assembly, and operation, then spits out the total load, and earned hours by resource group. The BAQ works well, a bit slow but it seems to have mostly the correct data. It only takes 400-600ms to run in BAQ designer and it returns about 500 rows. When I run the same BAQ in my dashboard (not customized), epicor freezes up for 30 seconds or so, then returns “Ready” with 0 results. There are no filters at the dashboard level, everything is done in the BAQ.
This was workign in the dashboard until I did two things. First, I filtered the top level query to show TotalLoad > 0. Next, I added the EH per Load calculation that literally just divides the two.
In the BAQ this is still working perfectly, but now the dashboard just returns 0 after a bit of freezing.
I cleared client cache - no change.
I restarted epicor - no change.
I rearranged the criteria in the BAQ a few different ways and always just made it worse.
Here is the BAQ, you may have to alter the resource group filter on one of the subqueries to match your resource groups.
RTU_LoadTotalsWithEstEH1.baq (95.1 KB)
There is nothing special about the dashboard. When I add the query to it, I add group by and summary. I group by ResourceGrpID, then WhatIf. This shows me a difference of load between regular and what-if schedules by resource group. In the dashboard, I also apply a sum to Earned Hours, TotalLoad, and EHPerLoad.
If you’re willing to take a look at the BAQ, please let me know if you see anything that might cause this kind of issue. If you can’t load the BAQ, here is the SQL:
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
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],
(SubQuery4.Calculated_EarnedHours/ TotalLoad) as [Calculated_EHPerLoad]
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]
having (sum(cast(SubQuery4.Calculated_LoadHours as decimal (5,2)))) > 0
order by SubQuery4.ResourceTimeUsed_WhatIf, SubQuery4.JobHead_PartNum, SubQuery4.JobAsmbl_PartNum, SubQuery4.JobOper_OprSeq
Thank you for your time!
Nate
