Job Traveller Report

Hello experts,

We have one customized job traveler report and it worked perfectly on every jobs except one. It got stuck in task agent for hours.

Does anybody know how i can investigate it further?

Does the job print okay when you use the Production Detail Report?

Was the job details from a BOM, from a quote, and/or manually edited? Maybe you have a circular reference.

You can check the ExecutionLogStorage Table in your ReportServer database to see a breakdown of TimeDataRetrieval, TimeProcessing, TimeRendering, and some other useful information when the report ran. The Parameters table has the GUID in it.

My bet with it being a Job traveler, one of your subreports is hosing the runtime, maybe with a bad link.

I would also suggestion looking at the temp tables that get generated for that one job. Looking at that would give you a pretty good indication of a bad link.

Can you reproduce it if you preview or print it again? What if you use the Standard report style?
Assuming it is an SSRS report, typical things like Sync Dataset comes to mind. Everything can seem to be fine until larger jobs with more material sequences than usual for example, began to show up and blew up the report.

Production Detail report works. I am not sure if the job is edited anytime.

LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.AssemblySeq = T4.AssemblySeq AND T3.OprSeq = T4.OprSeq
LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq AND T4.OpDtlSeq = T5.OpDtlSeq
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T6 ON T1.Company = T6.Company AND T1.JobNum = T6.JobNum
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T7 ON T6.Company = T7.Company AND T6.OrderNum = T7.OrderNum
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T8 ON T7.Company = T8.Company AND T7.CustNum = T8.CustNum
LEFT OUTER JOIN OPMaster_" + Parameters!TableGuid.Value + " T9 ON T3.Company = T9.Company AND T3.OpCode = T9.OpCode
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T10 ON T3.Company = T10.Company AND T3.JobNum = T10.JobNum and T3.AssemblySeq = T10.AssemblySeq
LEFT OUTER JOIN PartBin_" + Parameters!TableGuid.Value + " T11 ON T10.Company = T11.Company AND T10.PartNum = T11.PartNum
LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " T12 ON T10.Company = T12.Company AND T10.JobNum = T12.JobNum AND T10.AssemblySeq = T12.AssemblySeq AND T10.MtlSeq = T12.JobSeq LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T13 ON T6.Company = T13.Company AND T6.OrderNum = T13.OrderNum AND T6.OrderLine = T13.OrderLine AND T6.OrderRelNum = T13.OrderRelNum "

can you check if this joins looks good?

You added 8 tables to the JobTrvl RDD?

Because the query expression for my JobHead dataset in Traverl.RDL is:

Query Expression
="SELECT T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,T1.Calc_StockQty, T2.AssemblySeq,T2.BomSequence,T2.Company,T2.[Description],T2.DrawNum,T2.IUM as JobAsmbl_IUM,T2.JobNum,T2.OverRunQty,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.RevisionNum,T2.Calc_BCAsmSeq,T2.Calc_BCJobNum,T2.Calc_BCPartNum,T2.Calc_BCRevNum,T2.Calc_comment, T3.CommentText,T3.Instructions,T3.DaysOut,T3.DueDate as JobOper_DueDate,T3.EstProdHours,T3.EstSetHours,T3.Machines,T3.OpCode,T3.OpDesc,T3.OprSeq,T3.PrimaryProdOpDtl,T3.PrimarySetupOpDtl,T3.ProdStandard,T3.RunQty,T3.StartDate as JobOper_StartDate,T3.StdFormat,T3.Calc_BCOpCode,T3.Calc_BCOprSeq,T3.Calc_DispStatus,T3.Calc_OPText,T3.Calc_OPType,T3.Calc_PurPoint,T3.Calc_VendorId,T3.Calc_VendorName,T3.OpCode_OpDesc, T4.CapabilityID,T4.ConcurrentCapacity,T4.DailyProdRate,T4.OpDtlSeq,T4.ProdCrewSize,T4.ResourceGrpID,T4.ResourceID,T4.SetUpCrewSize,T4.SetupOrProd,T4.Calc_BCCapabilityID,T4.Calc_BCResGrpID,T4.Calc_BCResourceID,T4.Calc_CapbltyDesc,T4.Calc_ResDesc,T4.Calc_ResGrpDesc,T4.Calc_SchedResDesc,T4.Calc_SchedResGrpDesc,T4.Calc_SchedResGrpID,T4.Calc_SchedResID, T5.ResourceGrpID as ResourceTimeUsed_ResourceGrpID,T5.ResourceID as ResourceTimeUsed_ResourceID,T5.WhatIf
 FROM JobHead_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
 LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3
  ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.AssemblySeq = T3.AssemblySeq
 LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4
  ON T3.Company = T4.Company AND T3.JobNum = T4.JobNum AND T3.AssemblySeq = T4.AssemblySeq AND T3.OprSeq = T4.OprSeq
 LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T5
  ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq AND T4.OpDtlSeq = T5.OpDtlSeq"

Only 5 tables are listed in the Epicor original RDL. You have 13.

Did you make the relationships listed in your query, in the RDD?

That is customized one. It works on all jobs except one which is causing issues.

Yes I made those relation in RDD as well.

Not sure what to tell you. The epicor supplied OrdAck report would fail on us for very big orders (200+ lines). I had to remove a subreport (MiscCharges I think), in order for it to print.

The JobTraveler report is very heavy in the number of sub-reports it uses. If there are any that you don’t use try removing them.

Do you have access to the SQL server? If so check the temp tables (the ones with the GUID of the report) in report DB.