I have an SSRS report (BOM Listing Report) that I was asked to create a custom version of which would show users the MfgPartNum for each Part and it’s reference designators. Here is the original query I came up with:
=“SELECT T1.Calc_TopPartSeed,T1.Company,T1.[Description],T1.MtlSeq as MtlSeq,T1.PartNum,T1.RefDes,CAST(T1.RefDesSeq as nvarchar) as RefDesSeq,T1.RevisionNum,T1.Rotation,T1.Side,T1.XLocation,T1.YLocation,T1.ZLocation,T1.Calc_AltMethod,T2.MfgPartNum FROM PartMtlRefDes_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN PartXRefVend_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.MtlPartNum = T2.PartNum"
This ended up causing reference designators to repeat with the same MfgPartNum. For example:
So, I am now trying to write a query that would return one MfgPartNum per each unique PartNum/RefDes combo, I currently have this:
=“SELECT T1.Calc_TopPartSeed, T1.Company, T1.[Description], T1.MtlSeq as MtlSeq, T1.PartNum, T1.RefDes, CAST(T1.RefDesSeq as nvarchar) as RefDesSeq, T1.RevisionNum, T1.Rotation, T1.Side, T1.XLocasqtion, T1.YLocation, T1.ZLocation, T1.Calc_AltMethod, T2Subquery.MfgPartNum FROM PartMtlRefDes_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN (SELECT P1.MtlPartNum, P1.RefDes, MAX(P2.MfgPartNum) AS MfgPartNum FROM PartMtlRefDes_" + Parameters!TableGuid.Value + " P1 LEFT OUTER JOIN PartXRefVend_" + Parameters!TableGuid.Value + " P2 ON P1.Company = P2.Company AND P1.MtlPartNum = P2.PartNum GROUP BY P1.MtlPartNum, P1.RefDes) T2Subquery ON T1.Company = T2Subquery.Company AND T1.MtlPartNum = T2Subquery.MtlPartNum AND T1.RefDes = T2Subquery.RefDes"
When running the report with this new query, I am not getting any reference designators or MfgPartNums. So it basically is killing the BOMListing_RefDes.rdl portion of the report. Does anyone see anything obviously wrong? Are subqueries like this allowed in SSRS reports?