I am looking to add the weight to the materials on the Job Traveler. I am trying to create a relationship from the Part Material List to the Part Master table and use the gross weight. How ever since the material is a sub report on the Job Traveler report I am not getting the correct relationships to show up. Has anyone added the weights on the materials sub report? Can anyone assist with getting that relationship setup?
Thanks for your thoughts!
I’m going off the top of my head. I think the subreport still uses the same data set. As long as you get the data in the data definition, then it should be in the tables. In the subreport, you’ll have to join to the Part table that is generated (meaning you have to modify the SQL statement in the subreport).
To make sure you have the right data, generate a report and archive it for a day. Look in the system monitor for the report filename/ID (it’ll be 32 characters). Then go in to SQL and look for for a Part_<reportID> table. That will have your part data.
I have done that and I cannot seem to get the subreport to run correctly when I join the following. Below is the SQL from the dynamic dataset…
="SELECT T1.JobNum,T1.Calc_MultiJobParts, T2.AssemblySeq,T2.BackFlush,T2.Company,T2.[Description],T2.IssuedComplete,T2.IssuedQty,T2.IUM,T2.JobNum as JobMtl_JobNum,T2.MfgComment,T2.MtlSeq,T2.Ordered,T2.PartNum,T2.RelatedOperation,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCMtlSeq,T2.Calc_BCPartNum,T2.JobNum_PartDescription, T3.PartNum as TTJobPartMtl_PartNum, T2.Calc_InventoryQty, T2.Calc_InventoryUOM, T2.Calc_AttributeSetShortDescription, T2.RevisionNum, T4.GrossWeight
FROM JobHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN TTJobPartMtl_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.MtlSeq = T3.MtlSeq
LEFT OUTER JOIN Part_ " + Parameters!TableGuid.Value + " T4
ON T4.Company = T4.Company AND T3.PartNum = T4.PartNum"
I added that to the JobMtl Dataset, every time I run the updated Query, it fails. Any suggestions.
Normally I like my SQL to have a where clause, but since this is Epicor and it only puts the data into the tables needed for the report, you should be OK.
Take that SQL statement and run it in SQL Management Studio. Replace the " + Parameters!TableGuid.Value + " with the report filename (GUID) that is archived. See if the error is in the SQL statement. Management studio will give you good feedback if the issue is with the SQL statement. If you get results back, then the issue is with the report.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.