I am trying to adapt a report we run from our Epicor ERP system. The relevant tables that this report uses are "Part" and "PartMtl". PartMtl relates a parent part to it's child parts, and Part holds the details about a part. So the report is a parts list for a particular part (determined by PartMtl), and then each child part has some details (which it gets from the Part table).
The query doesn't actually contain any JOIN statements, it just pulls the fields from the PartMtl table. I added a JOIN statement to pull the fields I want from Part, but that didn't work. Then I noticed that the query already pulls some fields from the Part table, but not with a JOIN statement. The fields in question start with MtlPartNum, and then have an underscore and the name of the field in the Part table. I tried to add my fields to the query using this format, and then adding them to the list of fields, but this throws an error about a non-existent column in PartMtl.
My question is, what is going on behind the scenes in SSRS that is setting up those underscored fields as being part of the Part table instead of the PartMtl table? When I try to add fields in the same way it just assumes they are part of PartMtl and throws the error.
="SELECT T1.RptLanguageID,T1.Company,T1.EstScrap,T1.EstScrapType,T1.FindNum,T1.MtlPartNum,T1.MtlSeq,T1.OverRideMfgComments,T1.OverRidePurComments,T1.PartNum,T1.QtyPer,T1.UOMCode,T1.ViewAsAsm,T1.Calc_Consecutive,T1.Calc_CurrAltMethod,T1.Calc_CurrRevision,T1.Calc_RequiredQty,T1.Calc_UOM,T1.MtlPartNum_IUM,T1.MtlPartNum_PartDescription,T1.RelatedOperation FROM PartMtl_" + Parameters!TableGuid.Value + " T1"
The reporting system is Epicor ERP 10 uses a report database to hold the data for the reports. When the report is run the data is generated into the reports database using tables with the GUIDs in their names i.e. PartMtl_" + Parameters!TableGuid.Value. These tables reside in a separate database to the main ERP DB.
The field names are probably pulled in as Linked fields in the Report Data Definition. These are managed through a form in the application and the documentation should tell you how to add fields.