Hierarchical Report

[damjanu]

Hi All,

INTRO
-----
I have a question for hierarchical report.
I have searched the topic, but found some
ideas, but I am still confused.

I have a table with id | parent_id | name fields
where I store my category hirerarchy.
The hierarchy can be of unlimited depth.
(perheps not more then 8).
I also have product table:
id | category_id | name

I need to show it in report for printing, and it
needs to look like this:
level1_category1
level2_category1
level3_category1
level3_category2
product1
product2
level2_category2
level3_category3
level4_category1
product3
level5_category1
level6_category1
product4
product5
level4_category2
product6
product7
....

So I think this is clear.

How do I accomplish this is not

I do not mind hard code, but I would really appriciate some hints.

QUESTION
--------

FIRST WAY
I read somewhere in a post here that I can have an unbound
report and that with a recursive function which would
go thru my category table depth level by depth level I can get my data
printed like I need.
If someone could go into some detail about this it would really
be of great help. I am not interested in the recursive function
as much as how I would set this up in access. Also I do not know how to
go about how many text fields I need on my report. One for each category
and one for product, and then alter their indentation using
Left property?

SECOND WAY
I can get this data from a query that I already have. It comes
to this:

level_category1 --
level_category1 -- level2_category1
level_category1 -- level2_category1 -- level3_category1
level_category1 -- level2_category1 -- level3_category1
level_category1 -- level2_category1 -- level3_category1 -- product1
level_category1 -- level2_category1 -- level3_category1 -- product2
....

So omitted fields would be null, and number of columns would be equal
to the deepest brench. (so this is more like an approximation of result set)

But although I feel I could use this to easier get my wanted result,
I do not know how to. I do not know if I could do it with 1 category
text field and 1 product text field somehow Left-ified by the depth
level, or if I would need number of category text fields equal
to the deepest brench.....

I know I am asking for a lot, but I really need help.
Thanks so much.

 

[Jeff Boyce]

Is there a chance your data would lend itself to a cross-tab report format?

 

[damjanu]

I dont know what that is (cross-tab report)

But I did successfully do what I wanted .
For future reference:

The point is that I created a query that gave me all categories and
all products in a given category and all their kids (method with which
I did this is not a point know, but I am proud of that too)

So I gave that query to Report.ReocrdSource.
And I also opened a regular record source as well on that same query.
So essentially I had to parallel record sources, one which was
internally used by report to call my event handlers, and one which I
used to print data and format contrlos.

On the report I have 2 controls, 2 txt boxes.

In detail format event handler I basically parsed my own rs, assigning
the value of product and category name from my rs to my controls on
the report, and indented the controls as required with the .Left
property (from my query I also get a depth number).

This was inspired by this post here :
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]

and to quote most important part:
"Open a recordset on your table in the report's Open event
and locate the top level items's record. Then in the Detail
section's Format event copy the recordset fields to the text
boxes in the detail section. (Consider setting the
controls' Left property instead of using the Space
function.)

After populating the controls with the appropriate values,
locate the next item's record (keeping track of the levels
as you go). If there is another item to process, set
Me.NextRecord = False to tell access that you have another
detail to process.

Use the report's Close event to close the recordset and
perfom any other needed cleanup.