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

October 20th, 2010 by

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.


Comments

  1. Saravana Says:

    Hi Venkat,

    Very Nice. How do we make the Action Links to open in new browser window/tab. The link always opens on the same page. Is it possible to open the BI content in new window.

    Thanks for your help,
    Saravana

  2. Henrik Nielsen Says:

    Hi Venkat,

    Thanks for a good post. I was struggling with this yesterday, and got it to work with the 1st level-based hierarchy approach, even though the customer was not satisfied with it.

    I am also trying to figure out the answer to Saravana’s question – how do we get the “Navigate to BI” action link to open in a new window?

    Thanks,

    Henrik

  3. David Csikos Says:

    Hi Venkat,

    Could you write me what is full URL, please?

  4. Manohar Says:

    Hi Venkat,
    The post was very helpful.

    I am using Go URL approach which is very apt for my requirement. But the problem is that always target report/dashboard page always opens in a new browser window. Is there any way that I can make the navigation happen in same browser window?

    Can you also please show the full URL you are using?

    Thanks
    Manohar

  5. Jahnavi Says:

    Hi Venkat,

    Thanks for your post. I have been able to pass parameters using “Navigate to Web Page” . I have 2 questions though. When the navigation happens to the target page only 25 rows are displayed. But when i click on the icon for “Next 25 rows” , All the parameters go away and all the rows are shown (without applying the passed parameters as filters). Also, If I have a table prompt for the target page and I change the value of the prompt the parameters go away again. Is there any way around this?

  6. Manohar Says:

    Hi,
    I am using 10g. Is there a way to force the system to open the guided navigation into a new window?
    Thanks.

  7. Sri Says:

    Hi Venkat,
    How could I use @[actionsrv]? I see it in the url. I needed this info for having a dynamic url when there is any change in the obiee instances.

  8. Suri Says:

    Hi Venkat,
    The post was very helpful.

    Please Can You Provide Full URL of Action Links.when try to use gourl It Nor Prompted.

    Thanks,
    Suri

  9. Ronny Says:

    Hi Venkat,

    Which version of OBIEE you used to demo the parent child hierarchy navigaion. I am in 11.1.1.6.0 and tried similar navigation from parent child hieararchy report to the target report passing the empname to the target report but when I click on the revenue, it doesn’t do anything and in the left hand side, below on the status bar of my browser gives me an error like this,

    Message: ‘getLevelInfo(…)’ is null or not an object
    Line: 1
    Char: 11142
    Code: 0
    URI: http://localhost:7001/analytics/res/b_mozilla/answers/selectionsmodel.js

    Can you please provide some info on this? Why is this happening?

  10. vyke Says:

    venkat,

    I think it is time to review this article. As of 11.1.1.6, you can pass the context as long as the detail report also has the hierarchy and has selection steps to restrict & override with prompt. But it still has its own issues but at least now there is a better way to pass the context.

Website Design & Build: tymedia.co.uk