How to get the Top requirement of a child material ?

Hello,

I have a requirement to create a custom report to show the demand and supply pegged for a material and along with this, client want to see the top requirement of the demand.

For instance: Material A has the BOM item B.

Material B has BOM item C

Material C has BOM item D.

Now we have created a Independent requirement for mat A. This independent requirement will create a dependent requirement to the mat B, C & D. We have executed the MRP and now the supplies have been created for each demand like below:

For Mat A:

Demand is independent req of 2 and supply is Planned order of 2.

For Mat B:

Demand is dependent req of 2 and supply is Planned order of 2

For Mat C:

Demand is dependent req of 2 and supply is PR of 2

For Mat D:

Demand is dependent req of 2 and supply is PR of 2.

Now if we run our custom report, it will the show the demand and supply pegged results but also we wanted to know the top requirement for each record like for material D, the top requirement is the independent requirement of mat A.

Could you please provide some logic to fulfill this requirement.

Thanks in advance,

Ashish