Hello, I am trying to figure out the Shop Load Report and how it gets it’s data. In the report data definition, there are some calculated fields and I am trying to find out where they come from. Can this be done? Is there any way to find out how this report is running? We are digging into the scheduling now and this is one of the pieces and I am running into a dead end. Any help?
thanks
Check out ResourceTimeUsed table.
Below are the fields.
| [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] |
Some of the fields are tilde ~ delimited.
Here’s how you deal with them. (Example is another table)
Salespeople and commission rates are held in the InvcHead table
but if there are multiple reps the IDs are all in 1 field split with a ~.
To extract them use 5 calculated fields containing these calculations:
([Ice].entry(1,OrderHed.SalesRepList ,’~’ ))
([Ice].entry(2,OrderHed.SalesRepList ,’~’ ))
([Ice].entry(3,OrderHed.SalesRepList ,’~’ ))
([Ice].entry(4,OrderHed.SalesRepList ,’~’ ))
([Ice].entry(5,OrderHed.SalesRepList ,’~’ ))
SOBYREP
(case when [Ice].num_entries(OrderHed.SalesRepList , ‘~’) = 1 then ‘’ else ‘*’ end)
Bruce
Thanks - that helps! That table looks like it is the missing piece! I will have to figure out the parsing but it looks like the data is in there.
OK question on the ‘LoadDays’ column. It appears as a large integer (8438~8439), so does this translate to an actual date? The data falls into the first two columns of the report.
OK it looks like the answer is this:
From this thread: Shop Load Report - Load Hours - ERP 10 - Epicor User Help Forum (epiusers.help)
