Mtltags RRD in kinetic is not getting updated with new table relationships

[iarunkumark (Arun)]

Hi Folks, I was trying to add few custom fields on Mtltag report from ParLlot table and it seems not working.
I duplicated base RDD and add table PartLot and add a relationship to Mtltag, then duplicate the style and add similar relationship in RDL file as well. unfortunately its not picking any fields from additional tables, Since we are on Cloud I couldn’t add any SQL queries inside the report. even I tried adding JobHead for testing and result is same.

Report

 ="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.UM_UOMSymbol,T1.VendID,T1.VendName,T1.WhseCode,T1.QtyNum, T1.LegalNumber, T1.JobSeqType, 
T1.Revision, T1.BCPCID, T1.PCID, T1.DMRNum, T1.AttributeValueSeq, T1.AttributeSetShortDescription, T1.NumberOfPieces, T2.Project_c, T3.ProjectID 
FROM MtlTags_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PartLot_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.PartNum = T2.PartNum AND T1.LotNum = T2.LotNum
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3 ON T1.Company = T3.Company AND T1.JobNum = T3.JobNum"

[Michael_Ramsey]

@iarunkumark

T3.ProjectID

I see this T3 alias but do not see table in FROM section defining a Table with said alias.

[iarunkumark (Arun)]

I missed the last line while copying, now updated.
thanks

[Michael_Ramsey]

I do have a custom MtlTags report style, but it is formatting change only, I have not modified the report data definition. It is possible that this post may shed some light on what is going on and how to accomplish what you are looking to do?

Modifying Mtltags Report - Add DMR number

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:

[Jonathan]

Is the relationship type as Output?

What do you mean you couldn’t add SQL queries inside the report? I am not familiar with how much you can customize reports on Cloud, I assumed you could upload your custom RDL.

[Michael_Ramsey]

@iarunkumark

, T2.Project_c,

What actually is field Project_c in PartLot table? It’s not there by default is it?

[iarunkumark (Arun)]

it’s a custom field which I have added in partLot table

[iarunkumark (Arun)]

As in previous comment Michael have mentioned an old post where they are using SQL code as (SELECT ISNULL(DMRNUM,0000) FROM [EPICORTEST].[ERP].[DMRHEAD] (NOLOCK)
WHERE PONUM = CONVERT(INT,T1.PONUM) which is not possible in cloud I believe.

[iarunkumark (Arun)]

I did notice the same post and was trying if anything have changed after the Kinetic update and since we are on cloud the solution mentioned in the post will not work.

[Mark_Wonsil]

@iarunkumark

Since we are on Cloud I couldn’t add any SQL queries inside the repor

< Putting on my Zero Trust Security hat >
Giving the SSRS service account access to your production database, cloud or not, is really not a good security posture.
< /hat off >

It also leads to hard coded dependency in the report making it a “surprise” when you switch servers. Probably better off figuring out why the RDD isn’t working.

[Mark_Wonsil]

My next step would be to check your PartLot_(GUID) and MtlTag_(GUID) tables and see if there is any data there. As a cloud user, you can switch the output on the RDD from DataBase to XML-Crystal. Do a generate only, then use the System monitor to download the data. If no data is found, then you have RDD issues. If the data is there, it’s an issue with your SSRS report.

[iarunkumark (Arun)]

Thanks mark, I will try generating an xml and verify the results

[Jonathan]

Yeah, you can’t query the ERP database directly from the report.

If the RDD is modified correctly, it should collect all information into the Reporting database, which are the _GUID you see referenced in the RDL.

So like Mark said, a data issue is an RDD issue, a report layout issue is an RDL issue.