Navigating between reports and dashboards when there is no common fields to pass parameters

Hello All

Today I want to talk about navigating between reports and dashboards. As we all know, this feature can be done in 11G through action frameworks, this allows us to click on any particular value of the actionlink defined column and navigate us to another dashboard page (BI Content if we choose it in the actionlink) and the value we clicked previously will be filtered.

This is a pretty standard feature that has been part of OBIEE product since it was still called Siebel Analytics. However, in order for this feature to work as straightforward as it is, the condition is that there must be a common field used in both reports. In other words, if you want to navigate from report 1 to report 2 by clicking on the values of a column, then that column needs to exist in both reports for this to work.

This becomes an issue sometimes when that column is technically not the same column yet it is the same column from the business perspective. In other words, we have all used alias in our data modeling and it gives us a lot more flexibility in creating the kind of reports we want. Alias of the same physical table can be used in different star and snow-flake schemas without causing conflicts. However, these alias columns, although being referred to the same physical table, aren't being treated the same column from OBIEE.

Here is the case:

We have a dashboard here that I am going to click on any values of 'Vlan Interface', then the page should become another dashboard with only that 'Vlan interface' filtered

However, it is not working. The destiny report is successfully being fetched, however the filter box is empty. This means the value that we clicked was being passed from the first page, but it is not received in the destiny page.

The reason here is that, although both the Vlan Interface column and Interface prompt has the same data and they are from the same interface column in the physical table, they are different columns according to OBIEE. They are from different folders and they have different names, they simply exist in different places.

 I wish OBIEE can have the intelligence, or an added feature that allow users to define actionlinks by including or excluding the navigation of the actual physical column. Well, until that happens in the future, we just have to work around it now.

To make the destiny dashboard receive the values passed from the first dashboard, there must be a common field between the 2. Since we want to click on 'Vlan Interface' and go to the second dashboard, then we will create a dashboard prompt using 'Vlan Interface' columns and then place it on the destiny dashboard:

In this prompt, we will set the default section as SQL statement:

Select "Node Interface", "Node Logical Interface" from table where rcount("Node Interface", "Node Logical Interface") = 2.

This is nothing but setting a default value so that if you do come to this dashboard by direct access, this prompt will have some default value to run against all of the reports there. If you access this page by clicking on 'Vlan Interface' value from the other dashboard, then the value you click will be shown in this prompt after the navigation. This is basically the idea. I choose rcount = '2' simply because I know the first value in my table is 'N/A'.

Set the presentation variables here, we will pass this variable to the existing 'interface' prompt on this page later.

Now that we have created this prompt, we will put it on the destiny dashboard

Now that this prompt is on the dashboard, but I want it be work behind the scene only. I don't want users to see this prompt and the 'interface' prompt at the same time. So it's just the matter of hiding this section all the time.

Simply create a report that always returns some data and use it as the condition:

Define a condition in the section Condition:

True if row count = 0 determines that this section will display only when the row count of this report = 0, which is always going to be false. This implies that this section will always be hidden.

Now, we need to modify the existing 'interface' prompt to make it affected by the 'Vlan interface' prompt that we just created.

Here, the idea is to change the default section of this prompt so that it's default value is whatever 'Vlan Interface prompt' passes :

SELECT "Node Interface"."Node Logical Interface" from Table where "Node Interface"."Node Logical Interface" = '@{V}'

Here, the presentation variable 'V' could be the value of rcount = 2 in the physical table or whatever Vlan Interface value the user click from the first page:

Now, let's give it a test. First, we go to this dashboard page with direct access. We see that the 'Vlan Interface' prompt is hidden, and the interface prompt is showing a default interface value, this is good.

Now, let's go back to the first dashboard page to test the navigation part:

Click on Vlan interface 'ETH Vlan 1410 U1/M1/IF0':

We arrive at the destiny dashboard with 'ETH Vlan 1410 U1/M1/IF0' being passed successfully:

Everything looks as if nothing extra has been done, this is perfect.

Thanks

Until next time