Priority Dispatch Report - JobMtl

Epicor ERP 10
, ,
1

I am unable to include JobMtl PartNum and Description fields in my Priority Dispatch custom report. They are already included in the Report Data Definition and in the Report Builder as JobMtl_PartNum and JobMtl_Description.

From the Report Builder, I added them to another field and saved but nothing shows up in Priority Dispatch Print Preview.
Help please? Thanks.

2

Can you post your SSRS Dataset query? Can you confirm that those fields are in the dataset referenced by your tablix?

Can you also post the expression of the field you added onto the report?

3

Thanks Chance. Below is the JCR65_ScheduledResources dataset that includes the JobMtl PartNum and Description fields.

The field I added just includes PartNum for now:

=Fields!JobMtl_PartNum.Value

“SELECT CAST( T1.AssemblySeq as decimal ) as AssemblySeq,T1.[Description],T1.DueDate,T1.EstProdHours,T1.EstSetHours,T1.JobNum,T1.LaborEntryMethod,CAST( T1.Machines as decimal ) as Machines,T1.OpCode,T1.OprQty,CAST( T1.OprSeq as decimal ) as OprSeq,T1.PartNum,T1.ProdLoadHrs,CAST( T1.RegionCode as decimal ) as RegionCode,T1.SchedCode,T1.SetupLoadHrs,T1.StartDate, T2.ResourceGrpID,T2.ResourceID,T2.Calc_JCDeptDesc,T2.Calc_NexResGrp,T2.Calc_PrimaryProd,T2.Calc_PrimarySetup,T2.Calc_ResDesc,T2.Calc_ResGrpJCDept,T2.Calc_RGDesc, T3.AssemblySeq as JobMtl_AssemblySeq,T3.BackFlush,T3.BaseRequiredQty,T3.BuyIt,T3.Company,T3.[Description] as JobMtl_Description,T3.Direct,T3.IssuedQty,T3.JobNum as JobMtl_JobNum,CAST( T3.MtlSeq as decimal ) as MtlSeq,T3.PartNum as JobMtl_PartNum,T3.ReqDate,T3.RequiredQty,T3.WarehouseCode,T3.Calc_MUOM,T3.Calc_RelOpr,T3.Calc_UOM,T3.Calc_OnHandQty,T3.Calc_AllocatedQty,T3.Calc_MtlAllocatedQty, T4.DueDate as PORel_DueDate,CAST( T4.PONum as decimal ) as PONum,CAST( T4.PORelNum as decimal ) as PORelNum,T4.PromiseDt,T4.ReceivedQty,T4.RelQty, T2.NextResourceID
FROM JCR65_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN ScheduledResources_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Calc_Company AND T1.AssemblySeq = T2.AssemblySeq AND T1.JobNum = T2.JobNum AND T1.OprSeq = T2.OprSeq
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T3
ON T2.Calc_Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq AND T2.OprSeq = T3.Calc_RelOpr
LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T4
ON T3.AssemblySeq = T4.AssemblySeq AND T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.MtlSeq = T4.JobSeq"

4

Are you On Prem? could you take a look at the tables that get generated in SSMS and verify that there is data in the Temp Tables?

5

I was wondering if you really wanted to link JobMtl to ScheduledResources, or if your top level is JCR65 and that is where the Job, Assembly, OprSeq always has a “match”.
I think this is also where I’d spin up a BAQ and duplicate the design - just as a matter of confirming the data pops in as expected.

6

Michael,
In the standard PrioDisp Data Definition, there’s a Report Relationships JobMtlPORel and ScheduledResources2JobMtl.
JobMtl is in Data Sources.

So the Job PartNum and Description data show up in the report but the JobMtl PartNum does not.

7

I am in SSMS but have no idea where the Temp Tables for this Priority Dispatch / SSRS report are located.

8

You should have two databases. One that contains the ERP tables that you would use if you were to query the tables DIRECTLY and another database named “exactly the same”_Reports. That database will contain the temp tables that get created when you run a report.
image

  1. Run your report again with the archive period set to 1 day. This will make sure Epicor doesn’t wipe the temp tables before you go look at them.
  2. You will also need to go into the System Monitor to get your GUID that you ran for your report. This is the second piece to find the exact temp table that was created for your report. System Monitor > Reports. The column is alllllll the way over to the right called File Name. This contains the GUID.
    image
  3. Once inside the reports db in SSMS. Look for the table you are looking for with your GUID trailing it after a underscore
    image
9

Just curious, how you are trying to display the mtl part/desc in your custom report?

In the standard report I usually select the option “Material Status” to see the mtl partnum/desc.
Ref screenshot from the Educational database

image
image739×202 46.1 KB

10

Bruce, this is what I was looking for. In the custom report, I removed most of the fields we don’t need but the 2 we want Mtl part & desc I couldn’t get them display due to that option.

Now I just need to figure out how to get that option selected by default. Thanks Bruce!

11

Thanks Chance. I’ll try this when I get a chance. I’m new to Epicor so had no idea.

12

You can try to set it in a form customization - form load (ReportParam epidataview)
Then set the customization on each menu via menu maintenance.
Ref screenshot & there should be several other examples if you search for “ReportParam”

image
image878×446 138 KB

13

Thanks Bruce. I got that option set in a customization and along with it, I also set the custom report style as the default.