Reporting a tree structure

[prreport]

Hi,

I want to report from a table that has data stored in a tree structure.
For eg:- A is made of B,C,G. It is shown in the database as
A B
A C
A G
B is made of D and E which is in the database as
B D
B E
C is made of E and F and is present in the database as
C E
C F

In the report, I should show that A is made of 1 D, 2 E, 1 F and 1 G. It should not show B and C.

I tried using subreports but I can't add another report under subreport. I tried using while loop but it doesn;t work.

Can anyone help me.

Thanks

 

[dgillz]

I have successfully done something quite similar to this, its not pretty or quick but it works.

Step1-create as many detail sections as you need if the tree is 7 deep you will have details a thru details g.

Step2-using Aliases (self joins), add the table to itself X number of times, linking components A to Parents B, Components B to Parents C, etc.

Step3-Recreate the same layout in each detail section, using a different alias in each detail section.

Step4-To get rid of white space, format all the details sections to suppress blank section. This way if a component in level A has no children you will not see 6 levels of white space immediately following it.

Step5- To not show items with children, You could test If Not (Isnull({Aliastable.ParentItem))) and then conditionally suppress these items. If a component was assign to a parent multiple times you wuld see it multiple times, but not a total.

Thats it. The place where it gets really ugly is if you need to pull out other information about the parent and child items. In the case of Macola, using this example you would have to add other files on to this report 7 times - one for each level.

Good luck, and if anyone knows a better way to do this I would love to know it. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[IdoMillet]

You can achieve similar results using a UNION approach.

I believe these types of situations frequently justify denormalizing your data structure. You can even use triggers to maintain the denormalized data structure in a manner that effectively removes the dangers of denormalized data...

I'm quitting here because this topic is too "heavy"
for discussing it in this format.

Cheers,
- Ido ixm7@psu.edu