07 Sep 2021 Advanced Oracle BI Navigation using Go URL

Oracle Analytics Server is becoming one of the main tools used by organisations to discover insights from their data through analyses, dashboards, etc. An analysis might look simple at first sight, but to provide an optimal user experience and to fulfil all the needs, there are hidden challenges. Something that might be particularly tricky, but at the same time is one of the most widely used features, is navigation through different analyses.

Action Links are the way to navigate from one report to another in OAS. Most users know how to navigate using the ‘Navigate to BI Content’ action link, which is very useful in most situations, but might not be enough if we need to perform a custom navigation passing several parameters to the report which we are navigating to. The solution for this is Go URL, using the ‘Navigate to a Web Page’ option and setting up all the parameters.

In this article we will explain how Go URL works in OAS and look at two different real-case scenarios where this solution has been particularly apt: to navigate from one subject area to another, and to use a custom navigation parameter to meet complex navigation requirements.

Go URL: how does it work in OAS?

Let’s move on to the technical details of this solution. The first step is to create an action link in the column of the main report from which we want to navigate, as usual, by clicking on ‘Column Properties’ and going to the ‘Interaction’ tab:

Then we need to click on ‘Add Action Link’ and select the option ‘Navigate to a Web Page’:

A new screen will appear, asking for a URL. The URL entered in this screen is the key to performing the navigation:

The URL syntax needs to be the following:

NAVIGATE TO A REPORT

https://host:port/analytics/saw.dll?PortalGo&Path=@{1}&Action=@{2}&P0=@{3}&P1=@{4}&P2=@{5}&P3=@{6}

NAVIGATE TO A DASHBOARD PAGE

https://host:port/analytics/saw.dll?Dashboard&PortalPath=@{1}&Page=@{2}&Action=@{3}&P0=@{4}&P1=@{5}&P2=@{6}&P3=@{7}

USEFUL TIP!
If you are working in an OAS which has different environments (for instance,  Development and Production), it is extremely useful to replace the ‘https://host:port/’ with ‘[@ps]’. This is an alias that will change accordingly if the report is migrated to another domain, so that it is not necessary to modify the URL when migrating reports to other environments.

Once the URL has been entered, it is time to define the parameters. You have to define as many parameters as are set in the URL. So, if this was the URL:

https://host:port/analytics/saw.dll?PortalGo&Path=@{1}&Action=@{2}&P0=@{3}&P1=@{4}&P2=@{5}&P3=@{6}

it would be necessary to define six parameters. When you click on ‘Define Parameters’, the following window pops out:

The table below explains how each of the parameters works:

When you need to filter more than one column, you need to set more parameters:

&4,7,10… Operators used to filter next columns.

&5,8,11… Operators used to filter next columns.

&6,9,12… Operators used to filter next columns.

Now that we know how they work, we can click on ‘Add Parameters’ to add all the necessary parameters:

In ‘Name’ we set the variable number (@{1}), in ‘Prompt’ we set the parameter to which the variable refers (&Path) and in ‘Value’ we set the value the variable needs to have; there are different possibilities when setting the value box:

• Value: To set a fixed value, for example the target path, the operator, the target column, or the value to filter the target column if it is fixed.
• Session Variable: The name of the session variable.
• Repository Variable: The name of the repository variable.
• Column Value: To pass the value of a column from the main report.

For example, if we are drilling from Headcount and we want to filter the Department Name in the target report, we need to set the value to the column ‘Department Name’:

USEFUL TIP!
To pass a presentation variable as a parameter using Go URL (as there is no direct option to set a presentation variable as value), we need to create a new column in the criteria with the presentation variable and set the value to that column.

Although the ‘eq’ might be the most used operator, Go URL offers many other possibilities. All operators that can be used are presented below:

The format of the target column to filter set in &P2 is:

&P2=”ttt”.”ccc” : In this parameter, ttt is the table name and ccc is the column name. (ex. “Fact Sales”.”Amount”).

USEFUL TIP!
If the target column is a calculation, we need to pass the whole string of the calculation to filter that column. For example, if we want to filter the column Avg (“Fact Sales”.”Amount” by “Dim Country”.”Country Name”), we would need to set the value of the parameter associated to &P2 as the whole string (of type value).

When more than one value needs to be added in &P3:

&P3=n+xxx+yyy+…+zzz: n is the number of values, and xxx, yyy, and zzz are the actual values.

For example, if we use the operator between (bet) to filter only the amounts between 100 and 200 in the target report, the URL snip would be:

&P1=@{1}&P2=@{2}&P3=2+@{3}+@{4}

@{1} = bet

@{2} = “Fact Sales”.”Amount”

@{3} = 100

@{4} = 200

It is extremely important not to forget to set the target column as prompted in the target report, as usual when using the Navigate to BI Content. If not, the navigation will not work.

Something else to highlight is that, by default, when setting a ‘Navigate to a Web Page’ Action Link, clicking on the link makes the following window pop out:

This can be useful to allow the user to select which parameters to choose, or if the objective is to achieve an automatic navigation, it might be annoying from a UI perspective, so it is possible to select which parameters appear in this window and also to make this window disappear.

• Fixed: If selected, the parameter will pass the value even though the value is null.
• Hidden: If selected, the parameter will not appear in the ‘Invoke Action’ dialogue window. When setting a parameter as hidden it is automatically set as fixed; if all the parameters are set as hidden, the ‘Invoke Action’ dialogue window will NOT appear.
• Optional: Gives the user the possibility to fill in the value of this parameter or not.

Now that we properly understand how Go URL works, it is time to see some challenging real-case scenarios that we have come up against in ClearPeaks, and figured out thanks to this feature.

Scenario 1: Navigating from One Subject Area to Another

The most usual practice is to navigate through reports that pull data from the same subject area. However, we may need to navigate from one subject area to another. This will make navigation much more challenging as the usual ‘Navigate to BI Content’ will probably not work properly.

Let’s suppose that we have a subject area that has information on, for example, Finance Balances. This subject area is very useful to create a report, but when we need to drill down to detail, we find that this subject area does not have all the financial details that we need. However, we do have another subject area which provides much more detail but lacks some elements.

In this situation, what we need to do is navigate from one subject area to another, and most probably we’ll need to pass filters from the main report to the drill-down. For example, the main report is created with the subject area ‘General Balances’, and we need to drill down from ‘Net Income’ to a report created with the subject area ‘Detailed Balances’, passing the year as a parameter, but this year has different names in the source and the target:

This gives us the following result:

Then we need to create the corresponding action link using ‘Navigate to a Web Page’ and Go URL with the following parameters:

URL: [@ps]analytics/saw.dll?PortalGo&Path=@{1}&Action=@{2}&P0=@{3}&P1=@{4}&P2=@{5} &P3=@{6}

Scenario 2: Complex Navigation Using an Auxiliary Navigation Parameter

Let’s look at the following scenario:

We have a report where the projects are classified by project type and the customer rating for each project. It might be interesting from a business perspective, for example, to know who the project managers of these projects were. It would also be really useful to be able to drill down to the list of project managers for each of the project counts. For example, in the Advanced Analytics project type, we can see the managers who have received a ‘Good’ review:

From a technical perspective, the following questions arise: how is it possible to drill down to Project Managers from the ‘Count of Projects’, especially when the Project Managers are not even included in the main report? And what if there is a Project Manager who manages projects of more than one type? And what if the projects are rated differently? This is what we will explain now, with the implementation process divided into several steps.

In this section, we will see how to create the main report in the most difficult scenario: when the condition to classify a project as ‘Good’, ‘Neutral’ or ‘Bad’ is different for each of the project types. For example:

In this case, the conditions to filter the count of projects will be different not only for each label, but also for each project type. This is the reason why, to recreate this scenario, it will be necessary to use ‘Unions’. The example report has, in fact, four different unions (one for the headings and one for each of the three project types):

Apart from the heading union, which contains just the heading strings, each union contains the following parameters, as seen in the figure above:

• Project Type
• Good Condition
• Neutral Condition
• Bad Condition
• Sort
• Navigation Parameter

With the conditions set as (for example, for Oracle BI and ‘Good’):

FILTER(“Projects”.”Count of Projects” USING “Customer Rating” > 9)

The sort is the row number: (1) Headers, (2) Oracle BI, (3) Modern BI, (4) Advanced Analytics and the ‘Navigation Parameter’ is ‘OBI;’, ’MBI;’ or ‘AA;’, which is the column that will be used to define the custom navigation using Go URL.

To implement this solution, we have to create a drill-down report too. The columns of this report are the Manager ID, the Manager Name, one for ‘Good’ navigation, one for ‘Neutral’, and one for ‘Bad’.

The navigation parameters are what will allow us to properly filter the managers of the corresponding projects. In the navigation parameters, each manager will have information of the types of projects and the rating of the projects they have managed. For example, if John has managed one Oracle BI project rated as ‘Good’, one Advanced Analytics project rated as ‘Bad’ and another rated as ‘Good’, we would see the following:

The formula of these navigation parameters is, for example, for the ‘Good’ navigation column, the following:

CASE WHEN “Customer Rating” > 9 and “Project Type” = ’Oracle BI’ THEN ‘OBI;’

||

CASE WHEN “Customer Rating” > 8 and “Project Type” = ’Modern BI’ THEN ‘MBI;’ END

||

CASE WHEN “Customer Rating” > 7 and “Project Type” = ’Advanced Analytics’ THEN ‘AA;’

END

Then we need to create the corresponding action link using ‘Navigate to a Web Page’ and Go URL with the following parameters:

URL: [@ps]analytics/saw.dll?PortalGo&Path=@{1}&Action=@{2}&P0=@{3}&P1=@{4}&P2=@{5} &P3=@{6}

With this set, we can see how we can find the same project manager for different types of projects:

Conclusion

In conclusion, Go URL is an extremely versatile tool that allows Oracle BI developers to create solutions to navigate through OAS reports with much more freedom than when using the traditional ‘Navigate to BI Content’ action link. However, it might be a little bit more complex to implement than traditional navigation, so we recommend using it only in particularly challenging scenarios like those described in this article.

We hope it has been useful! Our team of experienced Oracle specialists are here to help you, so do contact us to see how we can optimise your BI projects.