OAC Navigation to BIP

Navigating from OAC to BI Publisher with Parameters

Here at ClearPeaks, we are always trying to understand how tools work so that we can squeeze the best out of them to meet our customers’ needs. In this article we’re going to explain how to set up an action link from an OAC to a BI Publisher report with the special particularity of passing a set of parameters, including a date filter. Even though this article deals with OAC, the method can also be applied to OBIEE or OAS.

 

Our starting point is an OAC dashboard that depicts the hiring cost by gender and nationality for a specific date range:

 

Hire Cost Analysis dashboard

Figure 1: Hire Cost Analysis dashboard

 

When clicking on the Cost To Hire amount for any row, a navigation to BI Publisher is set in order to display the disaggregation of the hiring cost. In this case, the cost for French females will be broken down:

 

Cost to Hire dashboard – French females.

Figure 2: Cost to Hire dashboard – French females

 

BI Publisher – Hiring Cost Details Report

Figure 3: BI Publisher – Hiring Cost Details Report

 

How can we pass dates that are not even shown from a report through a navigation? This will be explained in the article, with the implementation process divided into several steps:

 

STEP 1: Create your OAC Report

 

In this case, the report granularity might need aggregated data, so a drilldown to BI Publisher makes sense. Since the report is filtered by a dashboard prompt, it needs a date filter as prompted:

 

OAC report – Date filter

Figure 4: OAC report – Date filter

 

STEP 2: Create your BI Publisher Report

 

To create a BI Publisher report, you need to create a data model first, and then the report where the layout is defined. In this case, the query and the report parameters will be defined in the data model.

 

It is very important to set proper names to the parameters and use them in the query.

 

BI Publisher Report – Parameter definitions

Figure 5: BI Publisher Report – Parameter definitions

 

BI Publisher Report – Query filters using parameters

Figure 6: BI Publisher Report – Query filters using parameters

 

STEP 3: Create an Action Link Between OAC and BI Publisher

 

You can learn how to guide user navigation in OAC dashboards and analysis using action links. An action link will allow users to access external web content in the context of your BI analysis, to use conditions and agents to automate the initiation of business processes, or to invoke a Web Service.

 

To set up an action link, go to your OAC report and click on the column properties of the field that you want to use to navigate. In our example, Cost To Hire will be the selected column to navigate to BI Publisher.

 

: Set up an action link – Column properties

Figure 7: Set up an action link – Column properties

 

Set up an action link – Interaction tab

Figure 8: Set up an action link – Interaction tab

 

Set up action link – create action link – Navigate to a Web Page

Figure 9: Set up an action link – Create action link – Navigate to a Web Page

 

The type of action link is crucial to determining the navigation possibilities. In this case, since BI Publisher has a different URL from OAC, it is important to set the action link as Navigate to a Web Page.

 

The first URL to enter is from BI Publisher, so navigate to your BI Publisher report and click on the settings icon to get the URL:

 

Set up an action link – Get BI Publisher URL

Figure 10: Set up an action link – Get BI Publisher URL

 

In our example, the URL is:

 

https://url_to_oac/ui/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FBIP%2FHiring%20Cost%20Details%
20Report.xdo&bipParams{«_xmode»:»2″,»_sTkn»:»242d733617993ae0f2b»,»_xiasynch»:»»,
«_xpf»:»»,»_xpt»:»0″,»_dFlag»:»false»,»_edIndex»:»0″,»_dIndex»:»0″,»_rToken»:»»,
«_ranDiag»:»false»,»_xdo»:»%2FBIP%2FHiring%20Cost%20Details%20Report.xdo»,
«_paramspSTART_DATE»:»»,»_paramspEND_DATE»:»»,»_paramspNATIONALITY»:»»,
«_paramspGENDER»:»»,»_xt»:»Hiring%20Cost%20Details%20Report»,»_xf»:»analyze»,
«_xana»:»view»}&_linkToReport=true

 

Enter the URL in the text box shown below, prompted after setting the action link as a Navigate to a Web Page (Figure 9):

 

Set up an action link – Enter the BI Publisher URL here

Figure 11: Set up an action link – Enter the BI Publisher URL here

 

Click on OK to complete.

 

STEP 4: Define Date Columns to Pass Through Navigation

 

This step is often the key to making this navigation work, considering the difficulty of passing the filter set to BI Publisher by the dashboard prompt, and how to split that date range into 2 different values to filter the SQL explained in step 2.

 

First, let’s see what value is returned by the dashboard prompt: to do so, assign a presentation variable to the date prompt and use it in the OAC report to visualise the format. Navigate to OAC and edit the dashboard prompt:

 

Edit dashboard prompt

Figure 12: Edit dashboard prompt

 

Set up a Presentation Variable

Figure 13: Set up a Presentation Variable

 

Once the presentation variable has been set, add it to the report:

 

Include the presentation variable to the report

Figure 14: Add the Presentation Variable to the report

 

Presentation Variable to OAC report

Figure 15: Presentation Variable to OAC report

 

Remember to add single quotes once the presentation variable has been added to the report:

 

Presentation Variable syntax

Figure 16: Presentation Variable syntax

 

Once added, we can see its value from a dashboard perspective:

 

Presentation Variable Date format when displayed in the OAC dashboard

Figure 17: Presentation Variable date format when displayed in OAC dashboard

 

As can be observed in Figure 17, the value shown in the dashboard is a string concatenation of the 2 dates selected in the prompt with a comma as a separator, so the idea is to apply string functions to extract both dates, the initial and the final.

 

For the former, apply the following formula:

 

CAST(to_datetime(SUBSTRING(‘@{PV_HIRE_DATE}’ FROM 0 FOR LENGTH(‘@{PV_HIRE_DATE}’) – LOCATE(‘,’,’@{PV_HIRE_DATE}’)) ,’YYYY-MM-DD’) AS DATE)

 

And for the latter:

 

CAST(to_datetime(SUBSTRING(‘@{PV_HIRE_DATE}’ FROM LOCATE(‘,’,’@{PV_HIRE_DATE}’)+1 FOR LENGTH(‘@{PV_HIRE_DATE}’) – LOCATE(‘,’,’@{PV_HIRE_DATE}’)+1),’YYYY-MM-DD’) AS DATE)

 

Bear in mind that both fields need to be casted to date as we want to pass these fields as date fields. In addition, as they are not relevant to the dashboard itself, they will be hidden from the OAC table. It is crucial not to hide the fields from the Criteria tab (column properties); go to the editing options and hide the fields from there.

 

Hide unused fields in OAC table

Figure 18: Hide unused fields in OAC table

 

STEP 5: Parametrise the Action Link

 

Go back to the OAC report, click on Cost To Hire column properties and edit the action link as described in step 3.

 

Edit the action link to include parameters

Figure 19: Edit the action link to include parameters

 

Click on Define Parameters and the following window will appear:

 

Defining parameters to use in the navigation

Figure 20: Define parameters to use in the navigation

 

Now it’s time to parametrise the different variables that will be passed to BI Publisher. The first thing to note is that all parameters need to be hidden in order to avoid another window prompt once the user starts navigating.

 

The first 3 parameters will be kept by default, but the rest will be removed. Based on the image below, the following 4 parameters will be created:

 

Set up parameters

Figure 21: Set up parameters

 

The key here is that the date filters have been included in the report in step 4 and so we can now select them.

 

The different options when defining a parameter are shown below:

 

Options to set up a parameter in an action link

Figure 22: Options to set up a parameter in an action link

 

By and large, Value will be used to filter a specific value like ‘Female’, and Column Value will be selected to filter the clicked value in the OAC report.

 

In our example, the user is not required to select any variable in the target BI Publisher report, so the number of parameters to be included in the navigation should match the number of parameters set in the BI Publisher.

 

Finally, once all the parameters have been set correctly, the URL needs to be modified according to the following logic:

 

https://url_to_oac/analytics/saw.dll?bipublisherEntry&Action=@{1}&itemType=@{2}&bipPath=@{3}&bipParams={«_xmode»:»2″,»_sTkn»:»78416170177d3ff717b»,»_xiasynch»:»»,»_xpf»:»»,»_xpt»:»0″,
«_dFlag»:»false»,»_edIndex»:»0″,»_dIndex»:»0″,»_rToken»:»»,»_ranDiag»:»false»,
«_xdo»:»/BIP/Hiring Cost Details Report.xdo»,»_paramspSTART_DATE»:»@{4}»,»_paramspEND_DATE»:»@{5}»,»_paramspGENDER»:
«@{6}»,»_paramspNATIONALITY»:»@{7}
«,»_xf»:»analyze»,»_xana»:»view»}

 

As you can see, the number headed by ‘@’ corresponds to the Name column in Figure 21.

 

STEP 6: Double-check your Navigation

 

Everything should work now, but since managing dates can be challenging sometimes, it is very important that we ensure that the date format matches the source field format in the database. In this case, since the date format in the OAC report is YYYY-MM-DD and the one required for the database is MM/DD/YYYY, the format of the date parameters in the BI Publisher data model needs to be changed.

 

Specifying the date format to match with the database one

Figure 23: Specify the date format to match the database

 

Now you can have a go!

 

Conclusion

 

In this article we have customised a navigation from an OAC report to a BI Publisher report and filtered the target report properly.

ClearPeaks are experts in Oracle technologies, always up-to-date with market trends and the latest features. If you believe our expertise can be of use to you, please contact us – we’ll be happy to help!

 

Oriol R
oriol.romero@clearpeaks.com