Traversing Parent-Child Relationship Data

[lvalaris]

I'd like to know if at all possible how to write a Crystal Report that traverses parent-child relationships and prints each parent and child while exploding through the different levels of variable depth (unknown). Typical such data structures are Bill of Materials.

In my programming days, I would write a recursive routine (that would call itself) passing the child to the routine. The routine would find all children of the parent passed to it, and in turn call itself again passing each of the children. This way a parent-child structure could be "exploded" through to all depths. Can this type of report be written in Crystal, and how?

Thanking you in advance,
Leo

 

[dgillz]

leo-

I cover this EXACT problem several months ago and did not find an easy answer. Please look in the Macola ERP forum for a thread on this.

If your BOM is only 2 levels deep you sould use a subreport. However, beyond that you are helpless as a subreport cannot call another subreport.

You will probably have people telling you to use heirarchecal grouping. This does not work. I tried this for hours and got a "where used" report, but not a Multilevel, recursive BOM.

The best workaround that I found (with emphasis on the word "work&quot is to first determine the maximum number of levels for your BOM. Then create that many detail sections in your report - details b,c,d, etc. Then create however many aliases of the BOM file, self joined to each other from Component level 2 to parent level 2, component level 2 to parent level 3, etc. Drag all the fields from the top level into details a, next level into details b and so on. Format the details sections to suppress blank section to get rid of white space. Theres your report If a component has a child, it will print directly below the component. If is does not he next component in that level will print.

What I do not like about this is if you need to display information about the parent and component items you have to link to the item master and item warehouse location files (at least in Macola's case) to EVERY level of the parent and child, using aliases (Aliai=plural of alias?) again.

My example was 4 levels deep and had 20 tables!!! It worked though.

Let me know if you have questions on self joins aka aliases or on anything else. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[ejg]

Hi Leo,

Are you using an oracle database? If so you may be able to use an oracle package and stored procedure to get the data you need.

Within the stored procedure you can use a connect by in the query to return the data you want.

for example;

select a,b,c
from table1
start with parentkey = (select a.childkey from table1 a where
a.serialnumber = (parameter1))
connect by prior parentkey = childkey;