How to use a single table database hierarchy with Report Builder?

[C17]

I have a table called “Units” that contains a single-table hierarchy; each
unit has a parent unit except the topmost unit. This is represented in the
table using the standard adjacency model:

UnitID [Primary Key]
UnitDescription
…other fields…
ParentUnitID [‘Points to’ UnitID in this table via a relationship]

For example, here are some rows in the table:
UnitID UnitDescription ParentUnitID
7 Total College NULL
5 Non-Academic Depts 7
12 Academic Depts 7
3 Math Dept 12
14 English Dept 12
65 Chemistry Dept 12

Now, what I want my users to be able to do in Report Builder is to
drill-down through this hierarchy when running a report.

Does anyone know how I can achieve this? Is there something I can do with
the Data Source View or the Report Model that would make it straightforward
for the user?

Thank you,

C17

[Todd C]

Check BOL for "common table expressions" and "recursive queries"

Have not built one like this, but have seen samples in magazines and books.

Google author and SQL guru Itzik Ben-Gan.

Good luck.
=====
Todd C

[vinuthan]

Hi,

Do you intend browse against the cube or directly the table?
If it is cube, then we could create a parent-child relationship in the cube.
As you drop the hierarchy, u will be able to drill down without any
additional effort