DrillThroughHierarchy
The syntax for Oracle is:
SELECT *
FROM TABLE(DrillThroughHierarchy('UNIT'))
WHERE top_mem IN(
SELECT mem_id
FROM unit
WHERE mem_name LIKE '{Unit}')When {Unit} is replaced by consolidated member US from the Unit dimension, the result is:
| MEM_ID | MEM+PID | LEVELS | TOP_MEM | TOP_NAME |
|---|---|---|---|---|
| 4 | -1 | 1 | 4 | "" |
| 5 | 4 | 2 | 4 | "" |
| 46 | 4 | 2 | 4 | "" |
| 6 | 4 | 2 | 4 | "" |