Shop Load Report Customization - Help Needed

I created a report style which is a copy of the original report style and modified it to have a where statement that would filter out unfirm jobs. The issue I currently have is that when the end user selects “Summary Only” the report does not return anything.


Code Snip:

Has anyone run into this problem before or know of a way around it?

Thanks for the help!

We had a customization written that separates unfirm jobs from the load, and also breaks the released load into “available” and “pending” hours.

Reach out to Com-Tec in Rochester, NY. They should have some code that meets your needs, how you go about getting that from them is up to you. It does work, though, and allows us to prepare data as shown below. You can use my name if you like to try to identify the custom code that they wrote for us.

1 Like


Hey, I’ve rebuilt my custom data set to make job head the primary table for this data to trickle down from and I have added a statement to the criteria for the table. This allows the summary and nonsummary versions of this report to run. The issue is that I am still getting unfirm jobs on this report. Any ideas?

Not sure I’m understanding why adding the JobFirm criteria in your SQL statement is not removing the unfirm jobs from the report? could you show your latest query?

Could you apply the criteria at the RDD level? This working might also depend on the Relationship type that you choose


How do I go about filtering in the RDD? I believe I do it here, but I’m not seeing how to say that JobFirm =1.

As for the query:

="SELECT T1.[Description],T1.JCDept,T1.ResourceGrpID,T1.Calc_WrkCtrCap1,T1.Calc_WrkCtrCap2,T1.Calc_WrkCtrCap3,T1.Calc_WrkCtrCap4,T1.Calc_WrkCtrCap5,T1.Calc_WrkCtrCap6,T1.Calc_WrkCtrCap7,T1.Calc_WCLoadHrs1,T1.Calc_WCLoadHrs2,T1.Calc_WCLoadHrs3,T1.Calc_WCLoadHrs4,T1.Calc_WCLoadHrs5,T1.Calc_WCLoadHrs6,T1.Calc_WCLoadHrs7,T1.JCDept_Description, T2.AssemblySeq,T2.JobNum,T2.OpCode,T2.OprSeq,T2.StartDate,T2.Calc_LoadHrs1,T2.Calc_LoadHrs2,T2.Calc_LoadHrs3,T2.Calc_LoadHrs4,T2.Calc_LoadHrs5,T2.Calc_LoadHrs6,T2.Calc_LoadHrs7,T2.Calc_PartNum,T2.Calc_ResourceGrpID
, T1.[Company], T1.[ExternalMES], T1.[JDFDevice], T1.[JDFOperation], T1.[MESQueue], T1.[URL], T2.[ActualEndDate], T2.[ActualEndHour], T2.[ActualStartDate], T2.[ActualStartHour], T2.[AttributeSetID], T2.[AutoSpcEnable], T2.[AutoSpcPeriod], T2.[AutoSpcSubgroup], T2.[ContractID], T2.[CurrentPkgCode], T2.[EstStdDescription], T2.[EstStdType], T2.[ExpCycTm], T2.[ExpGood], T2.[ExpPctUp], T2.[ExternalMESLastSync], T2.[ExternalMESSyncRequired], T2.[FSJobStatus], T2.[GeneralPlanInfo], T2.[Instructions], T2.[IsContiguous], T2.[JDFOpCompleted], T2.[LastPCIDPrinted], T2.[LastPrintedDate], T2.[MaterialColorRating], T2.[MiscInfo1], T2.[MiscInfo2], T2.[MobileOperation], T2.[NonProdLimit], T2.[PartQualEnable], T2.[PctReg], T2.[PlanningNumberOfPieces], T2.[PlanningRunQty], T2.[PrinterID], T2.[ProdUOM], T2.[QtyPerCycle], T2.[RemovedfromPlan], T2.[RequestMove], T2.[ReWork], T2.[SetupMaterial], T2.[SetupURL], T3.[JobFirm]
 FROM ResourceGroup_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T2
  ON T1.ResourceGrpID = T2.Calc_ResourceGrpID
 LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3 ON  T3.[Company] = T2.[Company] AND T3.[JobNum] = T2.[JobNum]
where T3.[JobFirm] = 1

I’ve noticed that this query doesn’t represent what the RDD has in it and I believe that I just need to reassemble this to match what I’ve built in the RDD:


@cfinley, @ckrusen

I have a few questions. I’ve run into an issue where I’m not quite sure what’s going on between the RDD and the report its self.

Let’s start with the original SSRS and RDD for ShopLoad. The RDD there has ResourceGroup as the primary table linked to JobOper by ResourceGrpID and Calc_ResourcegrpID. It also has company free floating as a table.

If I make a duplicate of that RDD and add in JobHead I would make the following changes:

  1. delete the ResourceGroup2JobOper relationship
  2. Create new relationship between JobHead and JobOper with JobHead as the parent, and it an output, and link it on company and jobnum
  3. Create new relationship between JobOper and ResourceGroup with JobOper as the parrent, def only, and link on company and resourcegrpid
  4. save and link it to my duplicate of the original ShopLoad SSRS report.
  5. create a table criteria for jobhead on JobFirm true (this should filter my report from top to bottom to remove all unfirm jobs and their time from my report)

From there I would go into the query on the ssrs report and change the following:

  1. Make JobHead table 1
  2. make resourcegroup table 3
  3. change all table assignments to reflect these changes
  4. change all links in the ssrs to reflect this

When all changes are made and reupload to epicor the ssrs should now reflect what is in the rdd and should work perfectly in my mind. Issue is that I am either missing a step or epicor doesnt like drastic changes to SSRS queries, and I receive a generic error message stating that there is a problem with the report.

Is there a better way of accomplishing this than the steps I have listed?

To fix your criteria, you would want to make sure you tick the IsConstant box. Then put the number 1 to signify its equal to true

If I were developing it myself, and I thought I would have to delete something out of the standard RDD to get it to work, i would do a BAQ Report instead. It will save you more time in the end and the head ache.

Epicor is so finicky with its RDD’s (behind the scenes calculations, criteria that cant be removed)

1 Like


Im getting to a point where that might be the best option. Any advice on how to figure out how they calculate things behind the scenes?

If I knew that, I would be everybody’s best friend :rofl:

Don’t believe i’ve ever tried to recreate this in the past. But from looking at the main screen i would suggest going the RDD BAQ route. This will give you good flexibility with your parameters since youll have to reverse engineering the “First Bucket is Inifinite” OR “Units” parameters (RDD’s are better when you make them from scratch). Best of luck to you.

Might be worth getting a second opinion on this. This report doesn’t immediately look fun to have to recreate.

1 Like

It took our contact a year and a half to develop the custom load report I snipped earlier. Definitely not a simple task.

1 Like

You need the SDK from Epicor to have access to the RDD calculated field formulas. :frowning:

Have you tried Epicor support? I know they used to have answerbooks on some of the RDD based reports but not sure they still maintain those.

In the meantime, here are some (really) old notes I had on shop load report,

Table references:

JobOper (Operation information for Jobs).

JobOpdtl (Defines the Resource Groups/Resources/and or Capabilities required for the JobOper).

ShopLoad (Summarizes resource group load hours by day).

LaborDtl (Manufacturing labor transactions that reduce the load calculations, a subset to the LaborHed file).

ShopCap (records exist when ResourceGroup capacity is different from defined ResourceGroup.Capacity).

ResourceGroup (was WorkCenter in versions V6.10 and prior)

Resource (production Resources)

ResourceTimeUsed (time that a resource is scheduled, allocated to a JobOpMac).

ResourceTimeUsedSub (child to ResourceTimeUsed used to track concurrent capacity of a resource).

ResourceCal (Calendar “exceptions” for a specific Resource, which is using a ResourceGroup calendar).

ProdCal (valid days for production).

Calculate the time periods for 7 columns : per Cutoff Date, Unit and Interval and “First bucket is infinite?”.

Each column has a Begin Date and End Date.

1st column Begin Date = Cutoff Date - the time interval selected. (No Cutoff Date if “infinite” option is chosen)

1st column End Date = Cutoff Date.

Subsequent column Begin Date is equal to the End Date of the previous column plus 1 day;

Subsequent columns End Date = cutoff date plus the time interval.

Read the Resource Group file by Dept/Resource Group for the Resource Group selected.

Read each JobOper record for the Resource Group

Skip closed jobs and completed operations

Skip if the scheduled Start Date is greater than the end date of the last column

Skip if there are no setup and no production load hours

Sort selected operations by Start Date and Start Hour.

Calculate Capacity : For each new resource group, the capacity is calculated for each time slot according to the number of production days that fall between the Begin Date and End Date, (based on the production calendar), times the Hours per Machine, times the Number of Machines for that resource group.

Capacity = Days * Hrs/Day/Machine * # of Machines

Example: The capacity for a resource group with 3 machines that each work 10 hours per day in 7 days, where Saturday and Sunday are not on the production calendar is 5 * 10 * 3, or 150 hours.

If the first bucket begins date is earlier than the current date, use only the production days from today forward for calculating capacity. Capacity does not exist for days already passed.

Calculate Load: Spread the load hours for each operation into the appropriate time slots, by working backward from the operation Due Date/Due Hour a day at a time, until all load hours have been applied. Production hours are applied first, then setup hours (moving backwards in time, this fits with the logical progression of the job. The assumption is that setup must be done before production.

The calculations for the load hours depend on the Load Relieved By setting in the Job Management module configuration.

  • First determine what time slot the operation due date falls into (the first End Date >= JobOper.DueDate)

  • Calculate the production load hours. (If the operation is NOT subcontract.)

  • If relieving load by quantity:
    Production Load = Estimated Hours per Piece * Qty Remaining
    Production load equals zero if the quantity completed to date exceeds the original planned quantity.
  • If relieving load by hours:
    Production load = Estimated Production Hours - Actual Hours to-date
    Production load equals zero if the hours to date exceeds the original estimate.
  • If the operation is marked as Production Complete, the load hours are always zero.
  • If the operation is scheduled to run on more than one machine, the load is divided by the number of machines.
  • If the operation is subcontract, the load = Days Out for the operation x Hours per Machine for the resource group.
  • Apply the production hours backwards, beginning with the operation due date and due hour. The first day applied is a special case, since the operation Due Time probably does not fall exactly at the end of the work day, so apply only the Due Hour as the maximum number of hours for the first day filled.

  • Calculate the setup load hours. Setup is always 0 if production has started, and for a subcontract operation.

  • If relieving load by quantity :
    Setup Load = Setup Standard - (Setup Standard * Setup Percent Complete)
  • If relieving load by hours:
    Setup load = Setup Standard - Setup hours to date
  • Setup Load hours are zero if the operation is marked as Setup Complete or the setup hours to date exceed the estimate for setup.
  • Apply the setup hours backwards, beginning where you left off with production.

Calculate Remaining : The Remaining Hours column is totaled according to the following equation: Remaining hours = Capacity - Total load

Print operation information and either setup or production load hours, or the sum of the two. Accumulate totals for each resource group and department.

At the end of each resource group, print the Capacity and Remaining totals also; likewise for each department. The Remaining hours are Capacity - Total Load for each bucket.