OBIEE master-detail requests navigation - refreshing details when validate master record

I've been working as a Oracle Forms & Reports developer for many years and I thought that it would be great to have some of Oracle Forms functionality implemented here in OBIEE. Think about having two OBIEE reports. One is the master and the second is the detail. You may use departments employees or order and order items as an example. In Oracle Forms you'll have two blocks, one is master and the second is detail. When you are located in the first block row (master record) you have possibility to get detail data automatically. In other words detail records are fetched immediately when a user queries the master record.

After some time of thinking I figured out the way how to do that in OBIEE and what is the best way to present it. I'll show how to implement master-detail requests navigation using HTML form text field on the master report along with iframes and using javascript events. The idea is to have master and detail reports in the same dashboard page, and including dashboard prompt. In the first report we have one text field (HTML form) in which the cursor is positioned automatically so we use this field to jump on the next or the previous master record, and as the result we are getting immediately detail report. Each report is placed in its own iframe. So, basically we navigate from the master to details on changing a record in the master request. I'll explain the whole process, from the building master-detail reports navigation to final customization.

So I want to have something like this:



Steps

Step one - building master and detail reports

Master



Detail



Master request shows categories and detail shows top ten products ranked by amount sold for the current row master category.

Step two - building current iframe custom expression on master request including both requests using concatenated iframes and placing it in the narrative view to establish navigation in the same page between reports without losing window focus

Current iframe contains two concatenated iframes. Each iframe is build for one request and the detail request iframe is placed below the master. Each iframe has name. We use this column to place it in the narrative view of the master request because we'll show it on the dashboard to show automatically both reports (frames).

Current iframe:



Better view:



As you can see the master request column TIMES.CALENDAR_YEAR is filtered by dashboard prompt presentation variable. When the request is placed inside iframe the classic navigation with column filter set to is prompted is not working so we use presentation variable to pick up value from the dashboard prompt and place it in the master iframe request.

Place current iframe column in narrattive view:



Step three - HTML form text field and javascript events implementation on the master request, triggering detail request when validate master record

Column product category on the master contains complex detail request navigation:



Better view:



Explanation what we have done with the code above:

We made form field for activating navigation, the text field is readonly. OnKeyDown javascript event disables backspace, delete and return keys to disallow user to change it (product category must be visible and not changed). Form text field need to be editable for triggering details. Enter and down-arrow key use tab key to validate next row. OnSelect javascript event opens detail request URL in the iframe and transfers all filters from the master request. OnClick javascript event select all in the text field (prevention if the user decides to click on the field). The focus is always in the master field. Finally we customize the form and hide editable functionality from the user.

Step four - build the dashboard prompt and dashboard page



Show only narrative view:



Step five - the final look and test

Initial:



The position is on the editable field and the detail request is automatically refreshed. You can press enter, tab or down-arrow to cross into new master row and validate detail request record for the current master. The focus is on the same page because we use iframes, so we refresh only detail iframe.









Unfortunately previous master row navigation can be established only using shift+tab or shift+enter keys.

Using more then one GO URL request navigation with HTML drop down list

There was a question on OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=968563&tstart=0 and it's very good base for me to write this post on how to place multiple GO URL navigation to a different subject areas in one column using drop down list.

In one of my previous posts http://108obiee.blogspot.com/2009/09/go-url-request-navigation-between.html I describe how to use/make GO URL request navigation between different subject areas.

I this example we'll use GO URLs from previous post and put them in only one column and give the user ability which one GO URL navigation from the drop drown list to choose.

As a reference for writing this post I use this link http://www.tek-tips.com/faqs.cfm?fid=2960 and thanks to that site for placing the code in. The code is combination of javascript and html select and option tag which makes drop down list and put links inside them. So when the user chooses value from the drop down list he navigates on the target report.

This first thing is to make Answers report, I used my Normal model subject area:



Write expression with HTML select and option tag to make drop down list and inside put your GO URLs:





We call javascript function inside column expression in Answers in the select tag and onChange so now we need to declare it somewhere.

This can be done in the dashboard text object in the text properties:





Now place report along with the text object inside the dashboard:



Test

Initial start:



The first report navigation from the drop down list:



Result:



The second report navigation from the drop down list:



Result:

Same page navigation drills using drill in place, iframes and embedded content in section

In this lession I'll cover same page navigation drills between two reports (not two dashboard pages), and I'll show how to implement this by using different methods and what are disadvantages and advantages of each of them. We want to stay in the focus of the first report (same page navigation) when we call the second report, for all cases.

We'll use reports:





On the PROD_CATEGORY (Navigate) column (Report23) we set value interaction to navigate to Report 24:



On the PROD_CATEGORY (Dimension drill) column (Report23) we set dimension level drill down:



GO URL to Report 24 column on Report23:



Current iframe column:



GO URL to Report 23 column on Report24:



Here we try to turn back dashboard prompt presentation variable from the first focus (when we choose it for the Report23) for returning back from Report24 to Report23 because we don't want to reset previous filter (but this don't work for navigation between two reports on the same page, it works only for navigation between two dashboard pages)

Report navigation inside iframe

We build iframe for Report23 and show it as narrative view in the dashboard.



Column is described above (Current iframe). With this, we have ensured ourself that everything is staying in the same focus (same page).

We test it:



If we navigate from all three columns we stayed in the same page (same focus. same window) and that is right. But here is the problem if we go to Report24 (from the first or the third column) and try to return back with GO URL to Report 23 column on Report24 we cannot set the previous filter in Report23 with presentation variable that we choosed. Report24 cannot see it. So if we go to that link, we get all records:



Drill in place inside a section

This only works for dimension level attributes drills and it has the same dashboard page focus. Otherwise it opens new window and loses focus.



Embedded content in section

Everything works fine, focus is always in the same dashboard page when we navigate using all methods except we cannot use dashboard prompts, neither presentation variables in reports that are inside embedded content. We didn't use drill in place option in the section.



GO URL in embedded content properties:

http://localhost:9704/analytics/saw.dll?GO&nquser=Administrator&path=/shared/Development/Report%2023&Action=Navigate