Manufacturing BOM where used report 1

[UserofGP]

I would like to generate a crystal report that provide a multi level whereused information in BOMs.

I started writing the script with table BM010115 but I am stuck because of the following issue:

ITEM A
SubItem B
SubItem C
SubItem D
subSubItem E
subSubItem F
subSubItem G

In this example, if I run the report for (G), I want A,D and F listed. I dont know how to make this happen.

 

[BobSchleicher]

Here is the SQL from a CRYSTAL report I made. I have reports with more detail, like MAKE, BUY, EITHER, and BOM qty. I am just showing this one for easier viewing.

 SELECT BM010115.PPN_I, BM010115.CPN_I, BM010115_1.CPN_I, BM010115_2.CPN_I, BM010115_3.CPN_I, BM010115_4.CPN_I, BM010115_5.CPN_I
 FROM   ((((COMPANYDBNAMEHERE.dbo.BM010115 BM010115 LEFT OUTER JOIN COMPANYDBNAMEHERE.dbo.BM010115 BM010115_1 ON BM010115.PPN_I=BM010115_1.CPN_I) LEFT OUTER JOIN COMPANYDBNAMEHERE.dbo.BM010115 BM010115_2 ON BM010115_1.PPN_I=BM010115_2.CPN_I) LEFT OUTER JOIN COMPANYDBNAMEHERE.dbo.BM010115 BM010115_3 ON BM010115_2.PPN_I=BM010115_3.CPN_I) LEFT OUTER JOIN COMPANYDBNAMEHERE.dbo.BM010115 BM010115_4 ON BM010115_3.PPN_I=BM010115_4.CPN_I) LEFT OUTER JOIN COMPANYDBNAMEHERE.dbo.BM010115 BM010115_5 ON BM010115_4.PPN_I=BM010115_5.CPN_I
 WHERE  BM010115.PPN_I='PARTNUMBERHERE'
 ORDER BY BM010115.PPN_I, BM010115.CPN_I, BM010115_1.CPN_I, BM010115_2.CPN_I, BM010115_3.CPN_I, BM010115_4.CPN_I, BM010115_5.CPN_I

I can email you the CRYSTAL report if you want. The site doesn't like people to post an email address so you will have to write it out.
example-joe dot schmoe at company dot com.

BobSchleicher

 

[UserofGP]

Yes, please email it to me. It is scott-williams at hotmail dot com

I appreciate the effort.