I have a custom Job Traveler that I have added the PartAlloc table to in order to access LotNum and AllocatedQty. I have added the table and created a relationship through JobMtl:
Relation: JobMtltoPartAlloc
Description: JobMtltoPartAlloc
Parent: JobMtl
Key:
Child: PartAlloc
Relation Type: Output
These are the fields for the relationship:
Company = Company
JobNum = JobNum
AssemblySeq = AssemblySeq
MtlSeq = MtlSeq
I have added it to my SSRS query:
="SELECT T4.LotNum, T4.AllocatedQty, 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
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 PartAlloc_" + Parameters!TableGuid.Value + " T4
ON T2.Company = T4.Company AND T2.JobNum = T4.JobNum AND T2.AssemblySeq = T4.AssemblySeq"
And finally added to my SSRS report
However, I don’t get any data. I created a BAQ using the exact same tables and relationships to ensure that there is actually data and there is. I’m not sure where to go from here?
