Oracle BI EE 11g – Navigation & Passing Parameters using Hierarchical Columns

BI EE 11g like 10g supports report to report navigation. BI EE 11g also passes down the parameters from one report to the other provided the column & presentation table names match (same as 10g). But in 11g now, we have the ability to use Hierarchical Columns. Lets see how to pass parameters from one report to the other using Hierarchical Columns.

Hierarchical columns have 2 main characteristics.

1. We cannot apply formulas or filters directly on hierarchical columns. So, we cannot do an "is prompted" filter on a hierarchical column.

2. Filtering on Hierarchical columns are done through Selection Steps.

So, in order to support filtering (Adding, Removing or Keeping members) on a Hierarchical column through a dashboard prompt, we have the ability to Override the selection steps through a prompt. The screenshot below basically shows the option enabled.

But this feature somehow does not pass the parameter(context values) from one report to another. I am not sure whether this is a bug but this parameter passing does not work for Hierarchical Columns. To validate this, i did quick check on the documented GO URL parameters of 11g and there is no mention of support for passing parameters to selection steps. So i am assuming this is the intended behavior for Hierarchical Columns.

So how do we enable this parameter passing from one report to another on Hierarchical Columns. There are a couple of ways depending on what kind of Hierarchical column you have. Lets start with Parent-Child Hierarchies first.

Parent-Child Hierarchy:

For example, let's assume we have the following report.

In this case, Sales Rep Hierarchy is a Parent-Child Hierarchy. The intention is to enable drilling on the measure column and then pass the Sales Rep name from this report to the target report (shown below)

To do that, we start with including an Attribute column of the parent-child Sales Rep Hierarchy into the parent report (say Sales Rep Name)

Ensure that this column is hidden. Since it is an attribute column, it will not alter the grain of your report.

Now, add an Action in the Revenue Measure to navigate to the 2nd report.

And in the second report, add an "Is Prompted" filter on the Sales Rep Name column.

Now if you navigate from the source to the target report, the contextual values of the hierarchical column will get passed as well (through the Sales Rep Name column).

This will work well if you are using a Parent-Child hierarchical column as we will always have an attribute column that will change as you navigate through the hierarchy. But in a level based Hierarchy, we cannot have a single column whose values will change as we traverse the hierarchy. So to achieve the parameter passing in a level based hierarchy, we can use 2 methods. One is based on standard Navigation and the other is based on Go URL.

Level-Based Hierarchy:

1. Standard Navigation: Lets use the same first report we had. In this report, the Customer Type Hierarchy is a Level Based Hierarchy.

To enable parameter passing, we need to include all the columns that contribute to the Level Based Hierarchy i.e Customer Type, Customer Status & Customers.

The good thing in 11g is, the hierarchical column determines the grain of the report. So, even after you add the lower level columns, the report grain will not change, instead the lower granular attribute columns will become null.

There are multiple ways to pass down the parameters but neither of them will be elegant. I will discuss a method that will involve less work but the report might require a lot of columns.

In the target report, make all the level columns as is prompted as shown below

Now enable navigation drilling in the main report on all the 3 attribute level columns to the target report. So, when we want to drill from Customer Type Level, we need to click on a value in the Customer Type Column. When we want to drill from Customer Name level, we need to click on a value in the Customer Name column. This way we can negate the NULL values getting passed to the target report. Ensure that the ordering of columns is maintained (from left to right) as generally when we navigate only the dimension values on to the left are passed down to the target report.

For example, in the report below we are clicking on Row 3 as shown below on the Credit Hold Customer Status, only the Customer Status and Customer Type (column on the left) get passed to the target report. The NULL value of Customer Name does not get passed on to the target.

In the same way, to drill from Customer Type Level, the column value on the Customer Type column should be clicked upon.

The problem with this approach is we will face issues when we try to navigate from more than one level based hierarchy. Also, this is not a feasible solution when you have a hierarchy with 15 levels.

2. Go URL: The Go URL approach can be used even in a Parent-Child hierarchy. In this approach, we will start with including all the 3 attribute level columns in to our report and then hiding them.

Then when we enable the navigation using Action Links, instead of using Standard BI Navigation, we shall be using the Web Page Navigation.

Here we will be entering the Go URL parameters as shown below.

We will be making this a Conditional Navigation. So, the idea is whenever Level 1 is Null, no parameters will be passed, whenever Level 2 is null, Level 1 will be passed and whenever Level 3 is null, Level 2 will be passed and when none are null then Level 3 will be passed.

Now when we drill the parameters will get passed through the Go URL.

I hope that the context passing of hierarchical columns gets added in as a feature into the product (or if its a bug, should get fixed) as it is a pretty common requirement.