Being that its a decimal field, it should at least have 0.00 in it. I’m not seeing that being sent either so you are more than likely right that it’s in the RDD. Besides making sure that it is unchecked in the exclusions table, is there something else I need to do there? This RDD currently is pulling the other fields (including UD fields) from the part table, but that was all set up in 10. This is the first time in Kinetic that I am trying to add another field that is on the same table already being used so I thought it was just unchecking the field and adding to the dataset in SSRS. Am I missing something?
In your RDL query, the Part table is pointing to the JobAsmbl table which will return data for the Assembly Part Number. I believe you want to return data for the materials on the job. Let me know if I am incorrect.
You are correct. It should be based on the JobMtl. In the RDD it looks like this:
I pasted in your code to my dataset definition, but still the field is not populating. I don’t get errors, just no value shown when it should at least show 0.00
You will have to replicate the same joins in the RDD for JobAsmbl to JobMtl.
1 Like
Is this a new UD field by chance? If not I’ve seen RDDs go dorky and not “take” changes and I need to rebuild it.
Yes it is a new UD field. So you’re saying start back with the original RDD and rebuild the entire dataset?
Have you verified the database as regenerated in UD Column Maintenance?
Well for testing I’d copy the stock one and just add the tables/relationships needed to add that field then assign the new RDD to the stock SSRS.
The field is there and can be used in customizations, etc. and can be pulled up in BAQs.
Then I’d do my suggestion of just copy the stock and do whatever you needed to do to add the field then test it against the standard SSRS to verify the dataset.
So I started fresh. Copied over the RDD and added the Part table. Included all columns. Created the relationship new.
Edited the dataset definition as well as I could:
="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,T
3.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.Company,
T6.PartNum,
T7.Company,
T7.PartNum,
T7.InnerTubeWall_c
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 JobMtl_" + Parameters!TableGuid.Value + " T6
ON T2.[Company] = T6.[Company] AND T2.[JobNum] = T6.[JobNum] AND T2.AssemblySeq = T6.AssemblySeq
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T7
ON T6.[Company] = T7.[Company] AND T6.[PartNum] = T7.[PartNum]
"
Added the field:
added a field to display on the report:
="ITW: " + First(Fields!InnerTubeWall_c.Value, “JobHead”)
and the result is:
I feel like it’s something small and stupid that I’m missing, but I can’t see it.
Did you verify by looking at the exported dataset to see if the data is getting out of Epicor?
If I set it to XML and archive it then use FileServerDownload, it once again shows in the top field definition:
<xs:element name=“InnerTubeWall_c” msdata:Caption=“” msprop:SystemCode=“Erp” msprop:DataTableID=“Part_UD” type=“xs:decimal” minOccurs=“0” />
but it is not listed in the ReportDataSet at the end.
Then it’s still not getting out of Epicor. Odd as I don’t see anything wrong with the process you outlined above. There was an older issue were the SysRowID field had to be included for it to export UDs but you say others on Part are being exported.
What version of Epicor are you on?
I checked our custom JobTrav RDD and it’s using JobPart to Part table instead of JobMtl. Not sure that matters though.
We are on 2023.1.4 We just upgraded from 10.1.500.46
The ones that are working were added to the report in 10.1.500.46
I have not had any luck adding anything in 2023
Ok, and have you PUT any data into this UD field for the part(s) you’re testing?
Deleted the JobMtl relationship and added this:
Since JobPart isn’t in a relationship, I added one:
and I changed the data definition to:
="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,T
3.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.Company,
T6.PartNum,
T7.Company,
T7.PartNum,
T7.InnerTubeWall_c
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 JobPart_" + Parameters!TableGuid.Value + " T6
ON T1.[Company] = T6.[Company] AND T1.[JobNum] = T6.[JobNum]
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T7
ON T6.[Company] = T7.[Company] AND T6.[PartNum] = T7.[PartNum]
"
And success!!!
Thank you to all for your help on this. I greatly appreciate this.
Marking this as the solution since it has the final working code if anyone comes here in the future, but the solution was from Randy, John and others here. Thank you all for all the work you put in.
I’ve had this case open with support for a couple weeks. Took you guys less than a day.
2 Likes
For the future, it’s possible to grab common links without setting up linkage. Using Linked Tables on the JobHead and picking the part number, you can add any field from the Part table, including user-defined.
Kinetic will create a name for the picked field that you use in the SSRS.
4 Likes
That looks amazing, but how do you call it out in the SSRS?