I am trying to add the JobMtl PartNumber to a nonconformance tag, or the canned Mtl Tag Report Data Definition. I have builtin relationships to get the field in and have added it to the exclusions list. However, I cannot get the field to show up in Microsoft Report Builder. I believe that because there is a preexisting part number field listed in this report data definition, coming from nonconformance, that it is not syncing my data definition correctly. Has anyone else run into a similar issue or know of a way around it?
Did you update the RDL’s Query expression to add the new table and fields? And alos added the Query fields? to the RDL’s datasource?
We needed something like this for the comment and @Chris_Conn updated the ReportParameter on load of the Material Tag form. If their field is there, but blank you could do the same.
What do you mean by update the RDL Query Expression? Can you do that on a canned Epicor report? If so how do I do this?
Did you also modify the SSRS dataset query and add it to the fields there?
Do you mean adding the table and relations to the report in RDD and adding the field to the exclusions list then yes. If not then I have not.
Basic outline of modifying a canned Epicor report…
- Duplicate the Report in Report Style Maintenance
- Duplicate the RDD in Report Data Definition
- Edit the new RDD (add tables, fields, etc …)
- In Report Style, select the newly created RDD (from step 2) for your new report
- Using SSRS Report Builder, modify the new RDL created in step 1, to:
a. Edit the Query Expression of the datasets
b. Add the query fields to the dataset.
1 Like
Be careful the MtlTag is a special beast that is used all over for various inventory transactions. Changes for one purpose can crop up in other places.
We did make one from scratch since it was just a label.
Here are some screenshots of where you will need to edit the RDL dataset query in Report Builder:
Follow the syntax in the dataset already to get the field from the table you would like.
Then, you will have to add it in as a query field:
This post has some more info about editing MtlTag.
Like @gpayne said, make sure you create duplicate/new RDD and RDLs as to not affect other areas that use this MtlTag dataset.
2 Likes
All,
I have used the photos that @jorel has provided and have made a query field for what I am after. In my case T4 = our JobMtl table. I added T4.PartNum to the end of the query for this report and then added a query field called JobMtlPartNum and set it equal to PartNum. The issue I am having now is that it is defaulting to our NonComf PartNum instead of our JobMtl PartNum. What do I need to do to get it to go to the correct PartNum?
Dylan,
You could give the PartNum you are looking for an alias. At the end of the query you can do “T4.PartNum as JobMtlPartNum” Then in the query field that you added change it to the alias you gave it instead of PartNum.
That would lead me to believe that the dataset is not quite set up right just yet. Would you mind sharing what your dataset query looks like?
Below is the query:
=“SELECT T1.AsmSeq,T1.TagNum,T1.BCAsmSeq,T1.BCBinNum,T1.BCJobNum,T1.BCLotNum,T1.BCOprSeq,T1.BCPartNum,T1.BCWhseCode,T1.BinNum,T1.ItemQty,T1.JobNum,T1.LaborNote,T1.LotNum,T1.NCComment,T1.NonConfTranID,T1.OpCode,T1.OprSeq,T1.PartNum,T1.PartDesc,T1.POLine,T1.PONum,T1.PORel,T1.Reason,T1.ReasonDesc,T1.ResGrpID,T1.TagFormat,T1.TagTitle,T1.UM,T1.VendID,T1.VendName,T1.WhseCode,T1.QtyNum, T1.LegalNumber, T1.JobSeqType, T1.Revision, T1.BCPCID, T1.PCID, T1.DMRNum
, T1.[Company], T1.[MtlQueueSeq], T1.[MtlSeq], T1.[PurPoint], T1.[RefAsmSeq], T1.[RptUserID], T1.[UM_UOMSymbol], T1.[VendorNum], T3.[AttributeSetID], T3.[CommentText], T3.[Description], T3.[EmpID], T3.[FailedQty], T3.[PassedQty], T3.[SysDate], T3.[TranID], T5.[ClassID], T6.[JCDept], T6.[ResourceGrpID], T6.[ResourceType], T7.[ActionDate]
, T3.[PsdCommentText], T8.[Department], T8.[Name], T8.[Shift], T8.[SupervisorID], T4.[PartNum] as JobMtlPartNum
FROM MtlTags_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN NonConf_" + Parameters!TableGuid.Value + " T3 ON T3.[TranID] = T1.[NonConfTranID]
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T4 ON T3.[Company] = T4.[Company] AND T3.[JobNum] = T4.[JobNum]
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T5 ON T4.[Company] = T5.[Company] AND T4.[PartNum] = T5.[PartNum]
LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " T6 ON T1.[ResGrpID] = T6.[ResourceGrpID]
LEFT OUTER JOIN DMRActn_" + Parameters!TableGuid.Value + " T7 ON T3.[DMRNum] = T7.[DMRNum]
LEFT OUTER JOIN EmpBasic_" + Parameters!TableGuid.Value + " T8 ON T8.[EmpID] = T3.[EmpID] AND T8.[Company] = T3.[Company]"
Let me know if you need other pictures
Found the issue! The relationship between NonConf and JobMtl was a definition only instead of output!
Thank you to all who helped! Thumbs up for all!
2 Likes