BOM Explosion using Hierarchy & HierarchyBelongsTo

[#1]

I'm attempting to perform a bill of materials explosion in QV using Hierarchy & HierarchyBelongsTo. My data looks something like this:

Parent	Child	Type	Qty
A	B	Make	2
A	E	Buy	1
A	F	Buy	1
B	C	Make	2
C	D	Buy	2
D	G	Make	1
G	H	Buy	1

My goal it to get a flat table depicting the total BOM structure for several hundred thousand parts (perhaps millions of parts). The resulting table would look something like this:

Parent	Child	Type	Qty	Level
A	A	Make	1	0
A	B	Make	2	1
A	C	Make	4	2
A	D	Buy	8	3
A	E	Buy	1	1
A	F	Buy	1	1
B	B	Make	1	0
B	C	Make	2	1
B	D	Buy	4	2
D	D	Make	1	0
D	G	Make	1	1
D	H	Buy	1	2
G	G	Make	1	0
G	H	Buy	1	1

Hierarchy seems to get me close, but, I'm missing top level parent ID and am not sure how to calculate the expanded quantities. Any ideas?

[#2]

Any one have any ideas???

[suniljain]

Pls Try Attached File

[#4]

HI

anyone solved this problem....

could anyone tell me how to solve this issue....

[#5]

Same problem, still no solution 🙂

Did anyone solve this yet? I'm trying to combine a Hierarchy with another HierarchyBelongsTo on the same source. The solution seems so close and yet so far away...

[hic]

Two points:

1) You need to have this in two tables, since you can have several transactions on the same node.

2) You need to define a root node – i.e. a node with NULL as parent. I have added this to the source table, but this can be done in the script instead.

[#7]

Thx for replying.

To meet my reporting requirements i need a BOM bridge table like this:

child, ancestor, Qty

A, A, 1

A, B, 2

A, C, 4

A, D, 8

B, B, 1

B, C, 2

B, D, 4

C, C, 1

C, D, 2

D, D, 1

Then in the report, child will relate to an article table and ancestor will relate to an invoice line table. So when i select A, i want to calculate the total sales qty amount even for invoice lines that where booked on B, C or D.

I don't think that the example you made solves this problem.

[hic]

I am fairly sure that the pivot table calculates exactly what you want. On the group for ‘A’, Qty’s for all its sub-nodes will be summed.

If you instead use HierarchyBelongsTo, you will get a bridge table which will give you an Ancestor list box, which could be useful, but it is not necessary for the summation. See new attachment.

Further, if you put both the Ancestor and the Qty in the same table, you will double-count the sold units: the same unit will appear on several lines.

[hic]

... but if you really want the bridge table with quantities in it, then just take the HierarchyBelongsTo table in my example and join it with the original transaction table. (Don't load Parent in the transaction table).

[#10]

Also did this myself, but how do i calculate how many times G is in A (from your example it should be 8)...

In your example, if i make a table graph with NodeID and AncestorID in it as dimensions and sum(Qty) as expression, the numbers don't give what i need.