Indented BOM in a BAQ

Hi all, I need to create a baq where I can identify all the manufactured parts in a BOM and sub assemblies so I can extract the comments of each. Any ideas on how can I pretty much have the indented BOM in a BAQ? Thanks

There is an example of how to do a recursive CTE query in Epicor’s documentation. Search that up and follow the example to create your own.

Thank you very much John, the example is very good. However it doesn’t drill down on my assemblies so I tested it in Epicor Training database and it works. I wonder why it wouldn’t recognize my lower levels

with [SubQuery1] as
(select
[PartMtl].[Company] as [PartMtl_Company],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(0) as [Calculated_Hierarchy],
(cast ( substring(’…’,1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum
as nvarchar(25))) as [Calculated_Ind1]
from Erp.PartMtl as PartMtl
where PartMtl.PartNum = @PartNum

union all
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl1].[PartNum] as [PartMtl1_PartNum],
[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring(’…’,1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
as nvarchar(25))) as [Calculated_Ind2]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.MtlPartNum = SubQuery1.PartMtl_PartNum
and PartMtl1.Company = SubQuery1.PartMtl_Company)

select
[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[SubQuery11].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1]
from SubQuery1 as SubQuery11

Hi Ivonne,
Did you ever get this to work? I am looking to build the same (Indented BOM Dashboard).