OBIEE 11G: Display hierarchy and columns from the hierachy at the same time

One of the newest feature of 11G is the possibility to expand/collapse hierarchy level (yep.. even in 2014 it seems to be a killer feature…heh!). The problem is you can’t display the hierarchy and the column used to build the hierarchy at the same time with a table. The hierarchy will be OK but the columns will display null value.

Suppose the Hierarchy Year -> Month -> Week -> Day

Add the hierarchy AND the columns used to build it like this:

Hierarchy OBIEE 11g

As you can see, Month and Week are empty. This is caused by the Hierarchy.


OBIEE Hierarchy Result

If you need to display the value, there is a hack. You only need to use the _whatever_ function with the columns ! The less intrusive is to append an empty string at the end.
OBIEE Hierarchy Hack


et voilà !