Custom Shop Load dashboard

Epicor ERP 10
1

I have seen mention on this group of users creating custom versions of the shop load report. I am wanting to create some sort of a dashboard to visualize load at a given resource group without having to actually run the report repeatedly as jobs are scheduled. The end goal would be to have some sort of bar graph that updates regularly with the load at each resource group and then has some type of conditional formatting that turns the bar red when the load exceeds capacity.

I have been able to pull a good part of the data that I need from the ResourceTimeUsed table, but have run into issues with properly parsing the data, as well as determining the corresponding capacity. I am going to continue working on this, but wanted to check with the group to see if anyone had already come up with a similar query (no sense in reinventing the wheel, if I don’t need to).

Thanks!
Jay

2

Jay,

I’ve done this, but it’s not without a little effort. Essentially to make use of that data (ResourceTimeUsed) in a dashboard you need to write a recursive query to iterate through the idiotic use of the tilde delimited values on the loadhours and loaddates columns (i use a stored procedure that runs daily). Jam those in a UD table, run the generate shop capacity process, then write an executive query to sum that all up and you can get load vs capacity by day by department by resource.

You can easily create a dashboard by simply looking at the JobOper/Opdtl table and get your start dates and your loads by resource/operation. The downside from that is you wont know which specific resource in the group, and all the hours are summed on the start date vs spread out like they would be in reality. It’s good for use in dispatch list applications though.

Pasted image1282×1026 62.9 KB

2 Likes
3

Thanks for the suggestion, Rob. I am hoping to come up with a solution to show the load on the actual date instead of showing it all on the start or due date, but I agree that the use of tilde delimited values for the load hours makes that a real PITA.
Jay

1 Like
4

that’s what the dashboard in the screen shot does.

5

Rob,
I am running into a problem with my ResourceTimeUsed table. Some of the jobs have accurate numbers and other jobs are missing data that shows in the Job Tracker. Some of this missing data is from the same date, different job. I am running 10.0. Ran the Generate Shop Capacity Process and nothing changed. Has anyone else experienced this problem?
Thanks,
Jen

6

where are you viewing this issue from?

7

When I run a query and compare what is in the joboper table against the resourcetimeused table for the exact same job and operation number
Jen

8

Rob
What stored procedure are you running? Would you be willing to send me the procedure and this dashboard because that is exactly what I am attempting to do. I am separating the dates now into separate columns and am realizing that some jobs have at least 20 load days in that field.
Thank you,
Jen

9

Does the problem correct itself by rescheduling the job? Try removing it from the schedule first tho. I’ve seen that thing get flaky from time to time in different versions… But generally its pretty solid. That might be a bit much to just throw out there, but essentially you build a cursor and and using a CTE, cross join the resourcetimeused table to a numbers table for both of those delimited columns. That just flattens the table. You’ll have to do some homework on building an executive query (there is some help there in the Epicor Ice Tool Guide).

here is a snippet of what you’re getting into on the SQL side…

with tblLoadDays as
(
SELECT JobNum, assemblyseq, oprseq, endtime, resourcegrpid, resourceid, startdate,
SUBSTRING(cast(loaddays as nvarchar(max)), n, CHARINDEX('~', cast(loaddays as nvarchar(max)) + '~', n) - n) AS loadday,
n + 1 - LEN(REPLACE(LEFT(cast(loaddays as nvarchar(max)), n), '~', '' )) AS index_id, row_number() over(order by jobnum, assemblyseq, oprseq) as rownum
FROM ERP.resourcetimeused as r
CROSS JOIN Numbers as nbr
WHERE (SUBSTRING('~' + cast(loaddays as nvarchar(max)), n, 1) = '~'
AND n < LEN(cast(loaddays as nvarchar(max))) + 1) and whatif = 0 and cast(loaddays as nvarchar(max)) <> '' and cast(loadhours as nvarchar(max)) <> ''
),
10

Rob,

Any chance you’d be willing to share your baq and/or dashboard?

Thanks!

Rory

11

I am trying to create the same dashboard without success as the shop load hasn’t been calculating the way we are expecting it to. Rob is it possible for you to share your BAQ?

12

Hello, was anyone successful getting the dates/hours?

13

– Codes and Baq (ver 10.1.500.45)
– Must Change CustID that you really have before testing.
– Enjoy~

     With [CTE] AS 
        (select 
        	(1) as [Calculated_n]
        from Erp.Customer as Customer
         where (Customer.CustID = 'C00015')
        UNION ALL
        select 
        	(CTE.Calculated_n + 1) as [Calculated_nn]
        from  CTE  as CTE
         where (CTE.Calculated_n + 1) < 100)
         ,[CTE2] AS 
        (select 
        	(1) as [Calculated_n]
        from Erp.Customer as Customer1
         where (Customer1.CustID = 'C00015')
        UNION ALL
        select 
        	(CTE2.Calculated_n + 1) as [Calculated_nn]
        from  CTE2  as CTE2
         where (CTE2.Calculated_n + 1) < 100)

        select 
        	[CROSS].[ResourceTimeUsed_JobNum] as [ResourceTimeUsed_JobNum],
        	[CROSS].[ResourceTimeUsed_AssemblySeq] as [ResourceTimeUsed_AssemblySeq],
        	[CROSS].[ResourceTimeUsed_OprSeq] as [ResourceTimeUsed_OprSeq],
        	[CROSS].[ResourceTimeUsed_ResourceGrpID] as [ResourceTimeUsed_ResourceGrpID],
        	[CROSS].[ResourceTimeUsed_ResourceID] as [ResourceTimeUsed_ResourceID],
        	[CROSS].[ResourceTimeUsed_StartDate] as [ResourceTimeUsed_StartDate],
        	[CROSS].[ResourceTimeUsed_EndDate] as [ResourceTimeUsed_EndDate],
        	[CROSS].[Calculated_LoadDay] as [Calculated_LoadDay],
        	[CROSS2].[Calculated_LoadHour] as [Calculated_LoadHour],
        	[CROSS].[ResourceTimeUsed_LoadDays] as [ResourceTimeUsed_LoadDays],
        	[CROSS2].[ResourceTimeUsed1_LoadHours] as [ResourceTimeUsed1_LoadHours],
        	[CROSS].[Calculated_index_id] as [Calculated_index_id]
        from  (select 
        	[ResourceTimeUsed].[JobNum] as [ResourceTimeUsed_JobNum],
        	[ResourceTimeUsed].[AssemblySeq] as [ResourceTimeUsed_AssemblySeq],
        	[ResourceTimeUsed].[OprSeq] as [ResourceTimeUsed_OprSeq],
        	[ResourceTimeUsed].[ResourceGrpID] as [ResourceTimeUsed_ResourceGrpID],
        	[ResourceTimeUsed].[ResourceID] as [ResourceTimeUsed_ResourceID],
        	[ResourceTimeUsed].[StartDate] as [ResourceTimeUsed_StartDate],
        	[ResourceTimeUsed].[EndDate] as [ResourceTimeUsed_EndDate],
        	(SUBSTRING(cast(ResourceTimeUsed.LoadDays as nvarchar(max)), CTE_TOP.Calculated_n, CHARINDEX('~', cast(ResourceTimeUsed.LoadDays as nvarchar(max)) + '~', CTE_TOP.Calculated_n) - CTE_TOP.Calculated_n)) as [Calculated_LoadDay],
        	(CTE_TOP.Calculated_n + 1 - LEN(REPLACE(LEFT(cast(ResourceTimeUsed.LoadDays as nvarchar(max)), CTE_TOP.Calculated_n), '~', '' ))) as [Calculated_index_id],
        	(row_number() over(order by ResourceTimeUsed.JobNum, ResourceTimeUsed.AssemblySeq, ResourceTimeUsed.OprSeq)) as [Calculated_rownum],
        	(SUBSTRING('~' + cast(ResourceTimeUsed.LoadDays as nvarchar(max)), CTE_TOP.Calculated_n, 1)) as [Calculated_where_1],
        	(LEN(cast(ResourceTimeUsed.LoadDays as nvarchar(max))) + 1) as [Calculated_where_2],
        	(cast(ResourceTimeUsed.LoadDays as nvarchar(max))) as [Calculated_where_3],
        	(cast(ResourceTimeUsed.LoadHours as nvarchar(max))) as [Calculated_where_4],
        	[ResourceTimeUsed].[LoadDays] as [ResourceTimeUsed_LoadDays]
        from  (select 
        	[CTE1].[Calculated_n] as [Calculated_n]
        from  CTE  as CTE1)  as CTE_TOP
         cross join  Erp.ResourceTimeUsed as ResourceTimeUsed
         where ResourceTimeUsed.WhatIf = 0  and (SUBSTRING('~' + cast(ResourceTimeUsed.LoadDays as nvarchar(max)), CTE_TOP.Calculated_n, 1)) = ~  and (LEN(cast(ResourceTimeUsed.LoadDays as nvarchar(max))) + 1) > CTE_TOP.Calculated_n  and (cast(ResourceTimeUsed.LoadDays as nvarchar(max))) <>   and (cast(ResourceTimeUsed.LoadHours as nvarchar(max))) <>)  as CROSS
        inner join  (select 
        	[ResourceTimeUsed1].[JobNum] as [ResourceTimeUsed1_JobNum],
        	[ResourceTimeUsed1].[AssemblySeq] as [ResourceTimeUsed1_AssemblySeq],
        	[ResourceTimeUsed1].[OprSeq] as [ResourceTimeUsed1_OprSeq],
        	[ResourceTimeUsed1].[ResourceGrpID] as [ResourceTimeUsed1_ResourceGrpID],
        	[ResourceTimeUsed1].[ResourceID] as [ResourceTimeUsed1_ResourceID],
        	[ResourceTimeUsed1].[StartDate] as [ResourceTimeUsed1_StartDate],
        	[ResourceTimeUsed1].[EndDate] as [ResourceTimeUsed1_EndDate],
        	[ResourceTimeUsed1].[LoadHour] as [ResourceTimeUsed1_LoadHour],
        	(SUBSTRING(cast(ResourceTimeUsed1.LoadHours as nvarchar(max)), CTE_TOP2.Calculated_n, CHARINDEX('~', cast(ResourceTimeUsed1.LoadHours as nvarchar(max)) + '~', CTE_TOP2.Calculated_n) - CTE_TOP2.Calculated_n)) as [Calculated_LoadHour],
        	(CTE_TOP2.Calculated_n + 1 - LEN(REPLACE(LEFT(cast(ResourceTimeUsed1.LoadHours as nvarchar(max)), CTE_TOP2.Calculated_n), '~', '' ))) as [Calculated_index_id],
        	(row_number() over(order by ResourceTimeUsed1.JobNum, ResourceTimeUsed1.AssemblySeq, ResourceTimeUsed1.OprSeq)) as [Calculated_rownum],
        	(SUBSTRING('~' + cast(ResourceTimeUsed1.LoadHours as nvarchar(max)), CTE_TOP2.Calculated_n, 1)) as [Calculated_where_1],
        	(LEN(cast(ResourceTimeUsed1.LoadHours as nvarchar(max))) + 1) as [Calculated_where_2],
        	(cast(ResourceTimeUsed1.LoadHours as nvarchar(max))) as [Calculated_where_3],
        	(cast(ResourceTimeUsed1.LoadDays as nvarchar(max))) as [Calculated_where_4],
        	[ResourceTimeUsed1].[LoadHours] as [ResourceTimeUsed1_LoadHours]
        from  (select 
        	[CTE21].[Calculated_n] as [Calculated_n]
        from  CTE2  as CTE21)  as CTE_TOP2
         cross join  Erp.ResourceTimeUsed as ResourceTimeUsed1
         where ResourceTimeUsed1.WhatIf = 0  and (SUBSTRING('~' + cast(ResourceTimeUsed1.LoadHours as nvarchar(max)), CTE_TOP2.Calculated_n, 1)) = ~  and (LEN(cast(ResourceTimeUsed1.LoadHours as nvarchar(max))) + 1) > CTE_TOP2.Calculated_n  and (cast(ResourceTimeUsed1.LoadHours as nvarchar(max))) <>   and (cast(ResourceTimeUsed1.LoadDays as nvarchar(max))) <>)  as CROSS2 on 
        	CROSS.ResourceTimeUsed_JobNum = CROSS2.ResourceTimeUsed1_JobNum
        And
        	CROSS.ResourceTimeUsed_AssemblySeq = CROSS2.ResourceTimeUsed1_AssemblySeq
        And
        	CROSS.ResourceTimeUsed_OprSeq = CROSS2.ResourceTimeUsed1_OprSeq
        And
        	CROSS.ResourceTimeUsed_ResourceGrpID = CROSS2.ResourceTimeUsed1_ResourceGrpID
        And
        	CROSS.ResourceTimeUsed_ResourceID = CROSS2.ResourceTimeUsed1_ResourceID
        And
        	CROSS.ResourceTimeUsed_StartDate = CROSS2.ResourceTimeUsed1_StartDate
        And
        	CROSS.ResourceTimeUsed_EndDate = CROSS2.ResourceTimeUsed1_EndDate
        And
        	CROSS.Calculated_index_id = CROSS2.Calculated_index_id
         order by  CROSS.ResourceTimeUsed_JobNum ,  CROSS.ResourceTimeUsed_AssemblySeq ,  CROSS.ResourceTimeUsed_OprSeq ,  CROSS.ResourceTimeUsed_ResourceGrpID ,  CROSS.ResourceTimeUsed_ResourceID ,  CROSS.ResourceTimeUsed_StartDate ,  CROSS.ResourceTimeUsed_EndDate ,  CROSS.Calculated_LoadDay ,  CROSS.Calculated_index_id

yh_cte.baq (76.9 KB)

4 Likes
14

I know this is an old post but this BAQ is incredible. I am trying to understand how it works and it is stretching my SQL capabilities. Whoever wrote this must be a genius. It works as is in Epicor although you don’t need the test for Customer.CustID necessarily.

In researching parsing strings, seems that now SQL has a new function called ‘STRING_SPLIT’ which would make this easier but it’s probably not available yet in Epicor. Anyways, props to whoever wrote this.