Recursive or Self referencing data with Crystal Reports

[jernst]

I am trying to create a report that will display a hierarchal report of the following type information. Assuming I have database items that have Parent/Child relationships (self referencing or recursive data), and
stored in single table, how can I display, the drill down data? For example, I have a part that has a child part. That child part has a child part of its own, and so on down.

Parent Part
Child Part 1
Child Part 2
Child Part 3
etc....

The subsequent levels show reference to the one previous level ONLY. For example, if I look at Child Part 3 (as illustrated above), the previous reference to Child Part 2 is the ONLY reference. I would have to then go to Child Part 2 to see it's link to Child Part 1 etc.... In the table, you have Part, and Parent Part. I am trying to find out if Crystal Reports can accommodate this kind of drill down, hierarchal, recursive type of data. The problem (as I see it), is that the table will show a Part and it's previous Parent part, but not it's child associations beyond a single level.

Can Crystal Reports handle this, and if so, how?

 

[kenhamady]

CR V8 has a Heirarchical Grouping feature which might have been added just for you (I can't think of anyone else who has used it in real life yet). Read the help on this and see if it does what you want.

Otherwise, you would have to add a separate instance of the table for each level (giving these extra instances and alias). I would start at the lowest level with the main table, and link that record to it's parent in the second instance, which would link to its parent in the third instance, and so on.
If you use left outer joins all the way out, you might be able to do what you want. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dcm]

jernst: The key to your report may be to consider including multiple copies of your datatable. This will enable you to join Table3.Child3 to Table2.Child2, Table2.Child2 to Table1.Child1, Table1.Child1 to Table.Parent

This would then enable you to choose a group structure for your report which would reflect your wishes and by hiding the various group sections enable you to drill down to reveal the next level of detail etc. David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[BobHunter]

Is that you Lyle ? We've cracked it with the Hierarchical Grouping but not the version 7 recursive way. It would be much appreciated if someone can solve this.

 

[jernst]

I have successfully setup the Heirarchical Grouping feature to display my data, but have a little problem with it. If I create NEW records, they display correctly. For example:

Parent Part
Child Part 1
Child Part 2
Child Part 3
etc....

BUT, when using existing database records, I get the following:

Parent Part
Child Part 1
Child Part 2
Child Part 3
etc....

If you notice, I cannot get the Child Part 1 to offset and be recognized as a child to Parent 1. This ONLY happens on existing records. If I create new parts and relationships it works fine. Any ideas?

John Ernst
jernst@assetworks.com
jernst@satx.rr.com

 

[kml]

Does anyone know if you can utilize this hierarchical funcitonality against multiple tables? As as example, we currently have a table that lists the parent/child relationships and another that contains the data - do we need to join these tables?

 

[kenhamady]

I don't think the Instance and Parent have to be in the same table, but they have to be in the same result set in memory. Linked tables produce a result set. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/