Adding additional field to JobTraveler Duplicate from Customer Table

Kinetic ERP
, , , ,
1

Hello dear epiusers!

I’m quite new to ssrs and MS builder, but i managed to make job-traveler duplicate, added extra tables through RDD, unchecked exclusions, relation type for added tables - output etc.

image
image933×229 8.98 KB

(added tables)
Theses are relations as I understand lead to my Customer table fields
JobHead - JobAsmbl - Part - OrderDtl - Customer
image
image1067×592 34 KB

(relations table)
At first I tried to put everything with separate datasets, added dsCustomer to my duplicated report
image
image1058×542 24.7 KB

(added dsCustomer, dsOrderDtl, dsPart)
This would be ok, but when I’m trying to print two jobs at a time - in second pdf i get same Customer name as in the first one.
I tried to make through lookup but with no luck :slight_smile:
I looked how this whole structure is build and as I understand - fields from other tables are binded inside JobHead dataset (but maybe I’m wrong here)
So I tried to experiment and add one field from Part table through main expression in JobHead:
Here is how expression query looks like:

="SELECT T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,T1.Calc_StockQty, T1.CustName, T1.Calc_custID, T2.KitNumDate_c,T2.KitNumBatch_c,T2.KitNumSeq_c,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, T2.Calc_AttributeSetShortDescription, 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, T6.NetWeight  FROM JobHead_" + Parameters!TableGuid.Value + " T1 INNER 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 Part_" + Parameters!TableGuid.Value + " T6 ON T2.Company = T6.Company AND T2.PartNum = T6.PartNum"

I added additional fields in fields tab:

image
image1079×711 24.6 KB

(label fields NetWeight)
I have no errors, tho I don’t see data in the form either. It’s Just empty field. Checked with BAQ - there is some data inside these jobs, so it must show something.
Tried to make T6.NetWeight as Part_NetWeight, same result.
Rechecked everything 10 times already.
I know something is missing in my whole process, but can’t see where. Please give me some clues :slight_smile:
Also - is my thinking is right - to bind Customer through this relation - JobHead>JobAsmbl>Part>OrderDtl>Customer, using CustNum field?

Like this:

="SELECT 
T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,T1.Calc_StockQty, T1.CustName, T1.Calc_custID, 

T2.KitNumDate_c,T2.KitNumBatch_c,T2.KitNumSeq_c,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, T2.Calc_AttributeSetShortDescription, 

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, 

T6.NetWeight as Part_NetWeight, 

T8.Name as CustomerName 

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 

LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T6 
ON T2.Company = T6.Company 
AND T2.PartNum = T6.PartNum 

LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T7 
ON T6.Company = T7.Company 
AND T6.PartNum = T7.PartNUm 

LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T8 
ON T7.Company = T8.Company 
AND T7.CustNum = T8.CustNum"
2

Just a thought, but perhaps you may want to look at the jobprod table.

Perhaps you want to look at the JobProd table to tie back to the SO and the releases

3

Hello Hally,
I overlooked this table, will need to check it again.

1 Like
4

So, I tried to add further data and don’t know how but it works with T8.Name as CustomerName and LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T8 ON T.7Company = T8.Company AND T7.CustNum AND T8.CustNUm

But doesnt work with Part NetWeight.

="SELECT 
T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,T1.Calc_StockQty, T1.CustName, T1.CreatedBy, T1.Calc_custID, 

T2.KitNumDate_c,T2.KitNumBatch_c,T2.KitNumSeq_c,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, T2.Calc_AttributeSetShortDescription, 

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, 

T6.NetWeight as PartNetWeight, 

T8.Name as CustomerName, T8.Address1 as CustomerAddress1, T8.City as CustomerCity, T8.Zip as CustomerZip, T8.Country as CustomerCountry 

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 

LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T6 
ON T2.Company = T6.Company 
AND T2.PartNum = T6.PartNum 

LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T7 
ON T6.Company = T7.Company 
AND T6.PartNum = T7.PartNUm 

LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T8 
ON T7.Company = T8.Company 
AND T7.CustNum = T8.CustNum"
5

A bit confused here are you after the net weight of the job assembly part or the net weight of the finished part?

I would ditch the Orderdtl all together
Join the part with the Jobhead
Join the orderhed with the job prod
job prrod with the job head
and the customer with the order hed

That’s if your after the finished goods net weight

6

Sorry, I wrote it a bit confusing :slight_smile:
I’ll try to do it your way to see if I can bind it properly. Job NetWeight would be more logical to have, but sometimes it’s necessary to have both (if we ship assembly parts and not full job).
These two tables was unknown for me, usually I’m looking at epicors field help and think its what i need, but obvious its not always the case.
Thank you again.

1 Like
7

It took me a while, forgot to uncheck Company exclusion in RDD, but whole process goes like this:
Added “JobProd” table to RDD

image
image1039×397 104 KB

Added relations
image
image1039×369 28.1 KB

Edited exclusions
image
image1039×348 74.7 KB

Then in SSRS rdl file Traveler.rdl edited main JobHead dataset. Not so sure about how I did that T9.OrderNum as JobProdOrderNum, maybe its enough to sau T9.OrderNum, will check later.
image
image1039×455 407 KB

Plus added Fields in Fields Tab. Pressed refresh fields.
image
As we can see - we have new fields inside JobHead dataset
image
image894×450 116 KB

In main design field added these few rows and these two fields (I already made Grouping before, JobNum as parent and PartNum as child)
image
image1039×238 75.4 KB

Ini EPICOR pushed for new report:
image
image875×334 51.8 KB

So far so good. I can see rezults. Tommorow will try to do weight part

1 Like