Mass Issue to Mfg Inventory Movement Report - add finished part no?

Epicor ERP 10
,
1

Hi all, I have been asked to add the finished part number to the Mass Issue to Mfg Movement Report. I’m not really understanding how this report data definition works - usually I’m used to seeing multiple relationships listed, however this has none.

So the only thing I could think to do was to create a relationship of parent (PartTran) to child (JobHead) so I can grab the PartNum from JobHead Table.

Here is the query for the dataset PartTran I have amended:
=“SELECT T2.PartNum as FinishedPart,T2.PartDescription as FinishedPartDesc, T1.AdditionNum,T1.AsOfSeq,T1.AssemblySeq2,T1.BinNum,T1.BinNum2,T1.BinType,T1.Calc_Bin2Description,T1.Calc_BinDescription,T1.Calc_EntryPerson,T1.Calc_InvoiceDate,T1.Calc_ReasonDescription,T1.Calc_WareHouse2Desc,T1.Calc_Weight,T1.DisposalNum,T1.EntryPerson,T1.InvAdjSrc,T1.JobNum,T1.JobSeq,T1.LbrUnitCost,T1.LegalNumber,T1.LoanFlag,T1.LotNum,T1.MscNum,T1.MtlUnitCost,T1.OrderRelNum,T1.PartDescription,T1.PartNum,T1.Plant,T1.Plant2,T1.POLine,T1.PONum,T1.POReceiptQty,T1.PurPoint,T1.TranDate,T1.TranReference,T1.TranType,T1.UM,T1.WareHouseCode,T1.ActTranQty,T1.AsOfDate,T1.AssemblySeq,T1.AssetNum,T1.BurUnitCost,T1.Calc_DocNum,T1.Calc_InvoiceNum,T1.Calc_LineNum,T1.Calc_PrintedBy,T1.Calc_SNList,T1.Calc_VendorAddrList,T1.Calc_VendorID,T1.Calc_WareHouseDesc,T1.Company,T1.EmpID,T1.ExtCost,T1.InvAdjReason,T1.JobNum2,T1.JobSeq2,T1.LotNum2,T1.OrderLine,T1.OrderNum,T1.PackLine,T1.PackNum,T1.PackSlip,T1.PBInvNum,T1.PORelNum,T1.POUnitCost,T1.ProjProcessed,T1.SubUnitCost,T1.SysDate,T1.SysTime,T1.TranDocTypeID,T1.TranNum,T1.TranQty,T1.WareHouse2,T1.RptLanguageID
FROM PartTran_” + Parameters!TableGuid.Value + " T1

LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum"

Trying to create a report using this causes an error “Query execution failed for dataset ‘PartTran’. —> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword ‘JOIN’.”
If I make it just a LEFT JOIN, then the error states that all T1 fields could not be bound.

Any advice please on where I might be going wrong? Thank you!

2

I don’t seem to have that report here. What do you use for data source? Is it a BAQ you created or is it a copy of a system one ?
If you want to add new tables/links/fields in an SSRS report, you add them in your RDD first in Epicor and then in SSRS. What you sent there seems to be from SSRS. Did you add the table and links in the RDD first?

3

Hi, it’s a system one called MassIssInvMv that I have done a copy of to customise. I did add the relationship in the RDD first, yes!