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)
Now that I think about it… I probably got the idea from this post by Jose?
Thanks for sharing Randy ~
This is a nifty summary view of load as well as some good query techniques to review. Here’s a screenshot of some of my data below, in case anyone’s interested in seeing some output. I’m going to review this and Nate’s with some planning personnel to see where to head next too.
Nancy
Hopefully your teams likes it as well @Nancy_Hoyt, I’m sure you know you can set the LoadDays subquery as the top to get the job details. If you have questions let me know or if you make changes please share.
Thank you for sharing this, life saver.
