Referencing the same table more than once on a report - Help Needed

Kinetic ERP
, ,
21

I finally got back to giving this a shot. Right now I am trying it with the Vendor and Vendor PP table as it’s a similar setup. The below code shows how the SSRS is set up:

        <CommandText>="SELECT T1.RptLanguageID,T1.Company,T7.VendorID as T7VendorID, T24.VendorID as T24VendorID, T8B.FFAddress1 as T8BFFAddress1, T8B.FFAddress2 as T8BFFAddress2, T8B.FFAddress3 as T8BFFAddress3, T8B.FFCity as T8BFFCity, T8B.FFCountry as T8BFFCountry, T8B.FFState as T8BFFState, T8B.FFZip as T8BFFZip,T1.Consecutive,T3.LaborEntryMethod, T3.OpDesc, T5.JCDept,T10.BaseFileName ,T10.Company ,T10.XFileName ,T10.XFileRefNum, T9.Key4, T8.FFAddress1, T8.FFAddress2, T8.FFAddress3, T8.FFCity, T8.FFCountry, T8.FFState, T8.FFZip  , T1.CurrentLevel,T1.IsHeader,T1.IsSubAssem,T1.ParentPartNum,T1.PartNum, T2.RptLanguageID as PartRev_RptLanguageID,T2.AltMethod,T2.Company as PartRev_Company,T2.PartNum as PartRev_PartNum,T2.RevisionNum,T2.RevShortDesc,T2.Calc_Consecutive,T2.Calc_SubAssembly, T3.RptLanguageID as PartOpr_RptLanguageID,T3.AddlSetupHours,T3.AddlSetUpQty,T3.BrkQty01,T3.CommentText,T3.Company as PartOpr_Company,T3.DaysOut,T3.EstSetHours,T3.EstUnitCost,T3.Machines,T3.OpCode,T3.OprSeq,T3.PartNum as PartOpr_PartNum,T3.PrimaryProdOpDtl,T3.PrimarySetupOpDtl,T3.ProdStandard,T3.QtyPer,T3.StdFormat,T3.SubContract,T3.Calc_Consecutive as PartOpr_Calc_Consecutive,T3.Calc_ProdHrs,T3.Calc_RequiredQty, T4.RptLanguageID as PartOpDtl_RptLanguageID,T4.CapabilityID,T4.Company as PartOpDtl_Company,T4.ConcurrentCapacity,T4.DailyProdRate,T4.OpDtlSeq,T4.OprSeq  as PartOpDtl_OprSeq,T4.PartNum as PartOpDtl_PartNum,T4.ProdCrewSize,T4.ResourceGrpID,T4.ResourceID,T4.SetUpCrewSize,T4.SetupOrProd
 FROM MethodsMasterTable_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T2
  ON T1.Consecutive = T2.Calc_Consecutive AND T1.PartNum = T2.PartNum AND T1.RevisionNum = T2.RevisionNum
 LEFT OUTER JOIN PartOpr_" + Parameters!TableGuid.Value + " T3
  ON T2.Company = T3.Company AND T2.PartNum = T3.PartNum AND T2.RevisionNum = T3.RevisionNum
 LEFT OUTER JOIN PartOpDtl_" + Parameters!TableGuid.Value + " T4
  ON T3.Company = T4.Company AND T3.OprSeq = T4.OprSeq AND T3.PartNum = T4.PartNum AND T3.RevisionNum = T4.RevisionNum
 LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " T5
  ON T4.Company = T5.Company AND T4.ResourceGrpID = T5.ResourceGrpID
 LEFT OUTER JOIN VendPart_" + Parameters!TableGuid.Value + " T6
  ON T6.Company = T3.Company AND T6.PartNum = T3.PartNum AND T6.OpCode = T3.OpCode  
  
 LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T7
  ON T3.Company = T7.Company AND T3.VendorNum = T7.VendorNum  
 LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T24
  ON T6.Company = T24.Company AND T6.VendorNum = T24.VendorNum   
  
 LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T8
  ON T8.Company = T7.Company AND T8.VendorNum = T7.VendorNum   
LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T8B
  ON T8B.Company = T24.Company AND T8B.VendorNum = T24.VendorNum  
 LEFT OUTER JOIN XFileAttch_" + Parameters!TableGuid.Value + " T9
  ON T9.Company = T3.Company AND T9.Key1 = T3.PartNum AND T9.Key2 = T3.RevisionNum  
 LEFT OUTER JOIN XFileRef_" + Parameters!TableGuid.Value + " T10
  ON T9.Company = T10.Company AND T9.XFileRefNum = T10.XFileRefNum   
  "</CommandText>

In the RDD I reference the Vendor and Vendor PP table but give them no relationship. When I run the report I get no vendor information. Is this because I do not have a relationship listed for these two tables, or is this because of something else?

Thanks to everyone for the help thus far!

22

Add a UD field in EmpBasic to store the supervisor’s email and create a BPM to update it.

1 Like
23

So now using the below ssrs and linking the vendor table to vendpart only by company i get the vendor table to populate corectly on both references:

        <CommandText>="SELECT T1.RptLanguageID,T1.Company,T7.VendorID as T7VendorID, T24.VendorID as T24VendorID, T25.FFAddress1 as T25FFAddress1, T25.FFAddress2 as T25FFAddress2, T25.FFAddress3 as T25FFAddress3, T25.FFCity as T25FFCity, T25.FFCountry as T25FFCountry, T25.FFState as T25FFState, T25.FFZip as T25FFZip,T1.Consecutive,T3.LaborEntryMethod, T3.OpDesc, T5.JCDept,T10.BaseFileName ,T10.Company ,T10.XFileName ,T10.XFileRefNum, T9.Key4, T8.FFAddress1, T8.FFAddress2, T8.FFAddress3, T8.FFCity, T8.FFCountry, T8.FFState, T8.FFZip  , T1.CurrentLevel,T1.IsHeader,T1.IsSubAssem,T1.ParentPartNum,T1.PartNum, T2.RptLanguageID as PartRev_RptLanguageID,T2.AltMethod,T2.Company as PartRev_Company,T2.PartNum as PartRev_PartNum,T2.RevisionNum,T2.RevShortDesc,T2.Calc_Consecutive,T2.Calc_SubAssembly, T3.RptLanguageID as PartOpr_RptLanguageID,T3.AddlSetupHours,T3.AddlSetUpQty,T3.BrkQty01,T3.CommentText,T3.Company as PartOpr_Company,T3.DaysOut,T3.EstSetHours,T3.EstUnitCost,T3.Machines,T3.OpCode,T3.OprSeq,T3.PartNum as PartOpr_PartNum,T3.PrimaryProdOpDtl,T3.PrimarySetupOpDtl,T3.ProdStandard,T3.QtyPer,T3.StdFormat,T3.SubContract,T3.Calc_Consecutive as PartOpr_Calc_Consecutive,T3.Calc_ProdHrs,T3.Calc_RequiredQty, T4.RptLanguageID as PartOpDtl_RptLanguageID,T4.CapabilityID,T4.Company as PartOpDtl_Company,T4.ConcurrentCapacity,T4.DailyProdRate,T4.OpDtlSeq,T4.OprSeq  as PartOpDtl_OprSeq,T4.PartNum as PartOpDtl_PartNum,T4.ProdCrewSize,T4.ResourceGrpID,T4.ResourceID,T4.SetUpCrewSize,T4.SetupOrProd
 FROM MethodsMasterTable_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T2
  ON T1.Consecutive = T2.Calc_Consecutive AND T1.PartNum = T2.PartNum AND T1.RevisionNum = T2.RevisionNum
 LEFT OUTER JOIN PartOpr_" + Parameters!TableGuid.Value + " T3
  ON T2.Company = T3.Company AND T2.PartNum = T3.PartNum AND T2.RevisionNum = T3.RevisionNum
 LEFT OUTER JOIN PartOpDtl_" + Parameters!TableGuid.Value + " T4
  ON T3.Company = T4.Company AND T3.OprSeq = T4.OprSeq AND T3.PartNum = T4.PartNum AND T3.RevisionNum = T4.RevisionNum
 LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " T5
  ON T4.Company = T5.Company AND T4.ResourceGrpID = T5.ResourceGrpID
 LEFT OUTER JOIN VendPart_" + Parameters!TableGuid.Value + " T6
  ON T6.Company = T3.Company AND T6.PartNum = T3.PartNum AND T6.OpCode = T3.OpCode  
  
 LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T7
  ON T3.Company = T7.Company AND T3.VendorNum = T7.VendorNum  
 LEFT OUTER JOIN Vendor_" + Parameters!TableGuid.Value + " T24
  ON T6.Company = T24.Company AND T6.VendorNum = T24.VendorNum   
  
 LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T8
  ON T8.Company = T3.Company AND T8.VendorNum = T3.VendorNum   
LEFT OUTER JOIN VendorPP_" + Parameters!TableGuid.Value + " T25
  ON T25.Company = T6.Company AND T25.VendorNum = T6.VendorNum  

  
 LEFT OUTER JOIN XFileAttch_" + Parameters!TableGuid.Value + " T9
  ON T9.Company = T3.Company AND T9.Key1 = T3.PartNum AND T9.Key2 = T3.RevisionNum  
 LEFT OUTER JOIN XFileRef_" + Parameters!TableGuid.Value + " T10
  ON T9.Company = T10.Company AND T9.XFileRefNum = T10.XFileRefNum

The issue now is that the two vendorPP tables do not populate. I keep trying to link them to the vendor tables or to the vendpart and the partopr tables. This unfortunatly results in nothing being shown from the VendorPP table, but all revelant Vendor Table information works. Anyone have any ideas?