Walking a hierarchy tree

[martintd]

I am using CR9 and Microsoft SQL database.

I have an organisationl hierarchy that includes a number for the level of the organisation.

I want to be able to find records of people in the bottom org level but display (and group on) the name of the department that is several levels above theirs.

Example data:
Organisation Name Level
Business Services 4
Accounting 5
Accounts Payable 6

The parent of Accounts Payable is Accounting, and the parent of Accounting is Business Services. How can I show Business Services for the people who are in Accounts Payable? There are also people in Accounting and Business Services that I need to display the records for.

Thanks

Tony Martin
tony.martin@comalco.riotinto.com.au

 

[chelseatech]

You can use the Hierarchical grouping and a variable to track what level in the hierarchy you are in.

Check out issue 28 of Crystal Clear for an example of this technique. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[martintd]

Thanks,

I also need to be able to display a running total count for data (people in levels 4,5 and 6) against the organisation name in level 4.

Any help with this would be appreciated.

The context is a training database that records when people have registered, completed or cancelled from courses. I am trying to create a report that will display the totals by department for numbers registered on a course vs completed the course.

Regards,

Tony Martin
tony.martin@comalco.riotinto.com.au