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.