Hello
We need to modify the PartTags report. We only need to add one field but I cannot figure out how to add it correctly. I keep getting an error in Report Builder : The Value expression for the text box lblRptLiteralsLPartNum4 refers to the field DMRNum. Report item expressions can only refer to fields within the current dataset scope.
If you’ve successfully joined the table in the query section of the dataset, you will still need to add the field to the list within the “Fields” section of the dataset. We’ve noticed this as well. Hope that helps.
We are noticing the mtltags report and many others have RDDs that must be custom built… Trying to modify them is useless, will have to join other tables within the dataset query manually to accomplish what we are trying to do. Sounds like what you guys are doing too…
Dustin
Were either of you ever able to get the DMR Number on the the Material Tag report? I’ve tried customizing the RDD but I cannot figure out where the DMR Number lives, or how I would join to that table given the limited number of columns/data that are in the default dataset for the MtlTag RDD.
@Tev @dustin.perkins See if this helps: (I tried for 2 whole days to make the standard RDD work with this report and nothing would work since its a temporary dataset so no matter how I joined with tables and relationships it would never work.)
Modify your MtlTag Dataset in the SSRS report based on the last field in the query below with the (select statement) and try it. I recently modified my MtlTag while working on an autoprint method to include the Material Queue Sequence id in relation to the NonConformance Tran ID. And the method below worked since where dealing with a temporary table being generated for the MtlTag dataset.
Blockquote
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.[Company]
,T1.[MtlSeq]
,T1.[PurPoint]
,T1.[RefAsmSeq]
,T1.[RptUserID]
,T1.[UM_UOMSymbol]
,T1.[VendorNum]
, (SELECT ISNULL(DMRNUM,0000) FROM [EPICORTEST].[ERP].[DMRHEAD] (NOLOCK)
WHERE PONUM = CONVERT(INT,T1.PONUM)) as ‘’ FROM MtlTags_" + Parameters ! TableGuid.Value +
" T1 "
I can check on this. I seem to remember that I had to add the non- conformance details to get to the DMR details.
Brad
1 Like
When I look in the RDD, I do not see any report table exclusions for the MtlTags data source. I am looking to bring in the PORel.OurQty field into the MtlTags.
MtlTags is a temporary table that includes fields from several different fields. I would open the report in Report Builder and look in the dataset to see if the field you are looking for is there. If not you will have to make a Report Relationship with the MtlTags table in the RDD. (PORel to MtlTags) You can join them on PONum, POLine, and PORel. To use this relationship, you will have to add a part of the query to the dataset in Report Builder. Check out this post:
Were you ever able to get the DMR number on the Material Tag? In the RDD, the DMR number already is included in the standard RDD but I am not able to run test data to confirm it is displaying the DMR number correctly on the Material Tag. Has anyone been able to display the DMR number on the Material Tag using the standard report style? What are the steps and test data process to display the DMR number on the Material Tag?
Were you able to get this to work with adding the DMR?