I have a SQL view that I use… Not sure if this will be helpful to you.
WITH pm AS (SELECT x.Company, x.PartNum, x.RevisionNum, x.QtyPer, x.FixedQty, x.MtlPartNum AS ChildPartNum, x.ViewAsAsm
FROM Erp.PartMtl AS x INNER JOIN
Erp.PartRev AS pr ON pr.Company = x.Company AND pr.PartNum = x.PartNum AND pr.RevisionNum = x.RevisionNum AND pr.AltMethod = x.AltMethod AND pr.AltMethod = ’ ’ AND pr.Approved = 1 AND
pr.EffectiveDate <= GETDATE() AND pr.EffectiveDate =
(SELECT MAX(EffectiveDate) AS Expr1
FROM Erp.PartRev AS pr2
WHERE (Company = pr.Company) AND (PartNum = pr.PartNum) AND (Approved = 1) AND (EffectiveDate <= GETDATE()))), bm AS
(SELECT 1 AS BOMLevel, PartNum AS TopLevelPartNum, Company, PartNum, RevisionNum, QtyPer, FixedQty, ChildPartNum, CAST(QtyPer AS decimal(30, 10)) AS PartQty, ViewAsAsm
FROM pm AS root
UNION ALL
SELECT bm.BOMLevel + 1 AS BOMLevel, bm.TopLevelPartNum, node.Company, node.PartNum, node.RevisionNum, node.QtyPer, node.FixedQty, node.ChildPartNum, CAST(node.QtyPer * bm.PartQty AS decimal(30,
10)) AS PartQty, node.ViewAsAsm
FROM pm AS node INNER JOIN
bm ON bm.Company = node.Company AND bm.ChildPartNum = node.PartNum
WHERE (bm.ViewAsAsm = ‘1’))
SELECT BOMLevel, TopLevelPartNum, Company, PartNum, RevisionNum, QtyPer, FixedQty, ChildPartNum, PartQty, ViewAsAsm
FROM bm