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.
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:
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:
From there I would go into the query on the ssrs report and change the following:
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)
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 
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.
It took our contact a year and a half to develop the custom load report I snipped earlier. Definitely not a simple task.
You need the SDK from Epicor to have access to the RDD calculated field formulas. 
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.)
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.
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.
