I’m making a BAQ to see the real shop load, scheduling block data. I did find the Data Base called “ResourceTimeUsed” I believe it contains the data I’m after, but need help on how to translate some data. In the “LoadDays” and “LoadHours” fields it appears to be what I’m after but they are in an uncomfortable format. Each field seems to have multiple data points that I want. The LoadHours seems to show the load hours that are broke into each day due to capacity. The LoadDays seems to list the different days that are loaded, but they look like a day counter starting from where? Is there a way to break these down into individual pieces and a way to actually see a “normal” calendar day? LoadDays look like 8372-8375-8376 and the LoadHours look like 3.4-10-8.6
Hello, did you ever figure this out? I am trying to do the same thing.
Yes…kinda
I found a table “ResourceTime Used”
It records the “Load Date”
I too am trying to figure out what the numbers in LoadDays represents. Has anyone figured it out. I checked all the refence guides and forum posts related to this and no one seems to know. It looks like the number of days since 1/1/1999. Can anyone confirm this?
I would really like to see each of the days that are scheduled in the ResourceTimeUsed table as individual days. Right now if an operation takes 4 days on one resource, I only get one row in the table with a start and end date, along with the LoadDays field. I would like to convert load days to actual days, so that when I query the ResourceTimeUsed table I can see each of the days that a resource is scheduled. I think I could do this with some fancy subquerying. Is there something built-in that does this already?
Thanks!
RTU_Daily.baq (53.1 KB)
Yes, Load Days is number of days since 1/1/1999. The attached BAQ contains an example of the ResourceTimeUsed table flattened out to daily load. I’ve used this for a bunch of different scheduling dashboards.
*The “RowCount” subquery uses the ZDataField table just to get a static list of rows 1-1000 something. Only used ZDataField because I know will have a consistent number of rows over time. I use this to breakout each entry in LoadDays into a separate row.
@zwilli526 This is awesome! Nice magic with the zdatafield table. I have never seen that before. What the heck is it?
Specifically, what are these Ice parts?
[Ice].entry(RowCount.Calculated_RowNum, RTU.ResourceTimeUsed_LoadHours,'~')
[Ice].num_entries(RTU.ResourceTimeUsed_LoadDays,'~')
It looks like the days aren’t showing correctly. I thought they were at first, but I can see now that the load days aren’t lining up with the operation finish dates for ops that span multiple days. I am trying to figure it out. This looks like exactly what I wanted if I could just get the days to show correctly.
EDIT: I think I see the problem. The BAQ you sent only shows days with load from the ResourceTimeUsed table. This is great, and it does show the right days, contrary to what I wrote above. What it doesn’t show are days where the job is out of the shop (subcontractor ops), or in an op with infinite capacity. It seems both of these cases leaves the RTU table empty since shop resources aren’t being used.
I think with some work this can do just what I needed.
Thank you!
Nate
I modified your BAQ a bit to include the scheduled start/end dates for operations that don’t create shop load. I think this will be very helpful going forward. Thanks again!
RTU_Daily_Nate.baq (59.9 KB)
Updated to include the actual assigned resource ID for all operations.
RTU_Daily_Nate.baq (63.1 KB)
HI Nate,
I got the following error when import the BAQ. Could you maybe update a new exported BAQ-file of this query? 
Hopefully it will prevent the error.
“Can’t import query definition from version latter than current: 4.1.200.0”
By the way, I can import Zack’s BAQ without error…
How did you make the addition to Zack’s BAQ?
And in the RTU-table I only see data with loaddays < 6 months, do you know when a Job will be visible in the RTU-table? Do we maybe have some programs running with an instruction to do something up to 6 months from today?
Thank you in advance!
Hi Stijn,
I haven’t been using this BAQ, but here is a new export if it works for you.
RTU_Daily_N.baq (63.3 KB)
Hi Nate,
Thanks! But unfortunately still not working… 
There is a trick that sometimes allows importing BAQs from “latter” versions
Someone else posted these steps before me but… I can’t find that original link.
Here is the general idea:
a.) Copy & rename your BAQ export with the extension “.zip”
b.) Open the “archive” with 7-Zip
c.) Find the the file BAQVersion in the archive, open with notepad, edit the version numbers to match your target system and save to the zip archive
d.) Remove .zip extension from the file name of the copy.
e.) Try importing that BAQ, it usually will work now
Nifty!
Yeah, I wish I could give credit to the original person but I can’t find that link again.
Being able to import to earlier versions has saved me countless hours.
e.g… I was able to successfully import your BAQ to an old 3.1.6 system
Sometimes you might need to make some adjustments after importing but many BAQs will “just work” in earlier versions.
Thanks for the feedback, I will try that one.
@NateS or @bordway do you have an idea why I can’t see more ‘loaddays’ than ± 6 months?
I do not see more load days than 6 months ahead, after that period there is still an occasional sporadic load on a day in April or a day in May, but with a much lower frequency than in the next 6 months.
Does it depends on a specific scheduling run? I already tried to run the “Generate Shop Capacity Process” with i.e. 730 days.
Check Site Maintenance > Detail > Planning. Is your finite Horizon set to 180 days? I set mine to 365 days. I am not sure what the ideal number is here, but I think it really just determines how far out the schedule will create load.
Hi Nate,
I uploaded the BAQ to check this out too. We run MRP with run finite scheduling checked and our planners schedule our jobs. We do not have any finite horizon set. Could this be the reason why this data is coming out ok? See below. Nate, do you recall exactly where you were heading with this data?
These load days that seem to have nothing to do with date are strange… then the tilda delimited hours. I’m sorry for lack of vision, I am not a planner but our planners complain plenty about Epicor behavior and anything that I can find to help them manage it better, the better!
Thanks,
Nancy
Hi Nancy,
I haven’t been using this just yet. We are in the process of getting out jobs on a schedule. Once that is done I will be looking back at this. My goal was to understand what days are loaded according to the schedule. This may not be the best place to find this data, but it is where I am starting. If I discover anything I’ll be sure to let you all know.
@bordway that’s an amazing trick and thanks for passing it on and to whomever figured it out originally!
I had to do a custom Shop Load report for our company as for various self-inflicted reasons our qty’s aren’t getting reported as they should. I used a slightly different method as the requirement for us was to have it summarized in 12 week buckets by Dept/Resource Group instead of detailed like Nate’s report.
Maybe this can help you, the load calculation we use:
(CASE
WHEN JobOper.SetupComplete = 0 THEN JobOper.EstSetHours
ELSE 0
END)
+
(( CASE
WHEN JobOper.QtyCompleted > 0 AND JobOper.RunQty > 0 THEN
(CASE
WHEN JobOper.QtyCompleted > JobOper.RunQty THEN 0
ELSE JobOper.EstProdHours - (JobOper.QtyCompleted / JobOper.RunQty) * JobOper.EstProdHours
END)
ELSE JobOper.EstProdHours
END))
SummarizedShopLoad.baq (129.7 KB)