Identify Hierarchy level and value in OBIEE hierarchical Columns
By Ashish M Jain-Oracle on Feb 06, 2013
OBIEE 11g introduced the new “in-place” drill option by introduction of hierarchical columns. Many times there is a requirement to identify the level of the hierarchy that you are at and also drill to a different report based on the level of hierarchy.
Consider the Product hierarchy shown below from the Sample App schema – BISAMPLE.
Now, we may want to drill to a different location/report based on the level of the hierarchy that we are at. For eg. At the Brand level (“BizTech”, “FunPod”, “HomeView”) we may want to navigate specifically to a brand level report. Additionally we may want to specifically pass the Brand value to the target report.
The hierarchical columns do not provide an option to pass the parameter value to the target report.
We can handle this in a different way. The two challenges here are to identify the level of the hierarchy you are at and also passing the parameter which the value of the particular member of that level.
For this first we need to include all the level columns in the criteria and hide it. In this example, we will inclue columns: P1, P2, P3, P4 as shown below:
In order to hide these columns we can click on the criteria tab and select column properties for each of the four columns. Then select Column Format tab and check the Hide option.
One another thing to notice is at any level, the value for that level and the level above has values, where as the level below that particular level is null. This is what we can use to identify the level that we are at in a hierarchy.
At this point, we can now create a target analysis that we want to drill into. For Eg. Brand level. In that case we need to make the column P4 Brand as a filter in that report. (Is Prompted).
Similarly we can create multiple report for different level and include that particular level and the levels above as filter in the analysis.
The 4 report I created below include:
Now we come back to the original report with the hierarchical column and start to include the action links to ensure for each level the right target report link shows up.
In the column Properties for the revenue column, Interaction Tab, add action links that show up conditionally.
For Brand level:
For LOB Level:
For Product Type Level:
For the product detail level:
This is how the interaction tab would look like at the end:
Now, the action links would show appropriately at each level and also drill to the right report based on the level we are at and will also pass the required values to the target report appropriately.
BizTech – Brand level:
Communication – LOB level:
Cell Phone – Product Type:
CompCell RX3 – Product Detail:
This way we can navigate to the right target report based on the level we are at in the hierarchy.
This option would normally work for a balanced hierarchy.