Identify Hierarchy level and value in OBIEE hierarchical Columns

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.

For Example:

Consider the Product hierarchy shown below from the Sample App schema – BISAMPLE.

Product Hierachy Product Hierachy

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:

Product Hierarchy Levels

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.

Hide Column

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).

Detail Target report

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:

Detail Reports

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:

Brand level conditions

For LOB Level:

LOB Details

For Product Type Level:

Prod Type Details

For the product detail level:

Product Detail

This is how the interaction tab would look like at the end:

Conditional Action links

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:

Brand Details

Target Report:

Brand Target

Communication – LOB level:

LOB Target

Target Report:

LOB Target Report

Cell Phone – Product Type:

Prod Type Link

Target Report:

Type Target Report

CompCell RX3 – Product Detail:

Product Detail Link

Target Report:

Product detail Target

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.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

This Blog is a platform for discussing the various Oracle BI solutions and how they help the Enterprises to make better decisions.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today