OBIEE 11g introduced several features in Action Frame Work that makes it possible to link BI content to several other content types via action links. One of the most common uses of action links is to setup drilldowns to other BI content. This a fairly straight forward process. In master analysis you need to include all columns that are defined as prompts. You can then setup action link in master report column properties to navigate to detail analysis. This is very easy to setup as long as both master and detail analysis are using same subject area.
What will happen if master and detail analysis are not coming from same subject area. It should work seamlessly as long as columns are same. I haven’t tried how it will behave if column names are same but coming from different business models. It would make a nice topic for another post.
In certain cases you may simply have no choice but to drilldown to detail report using URL. It could be due to different column names, or different business models or having the need to pass non-equi conditions or apply formula’s on column before they are passed.
For this post, I would like focus on how to dynamically construct a GO URL to drilldown report.
Dynamic URL for drilldown:
Below are two possible solutions.
Option 1:
Embedding HTML link in report column. Several bloggers have already explained this technique. You can find one good article from Cap Gemini BICG here
You can construct a GO URL to drilldown report with parameters and use that URL as hyperlink in master report using above technique. You can find an example of GO URL syntax here
You would have to manually change server name in URL when code is migrated to qa or prod from dev.
You can overcome this challenge by introducing a repository or session variable in RPD that has name of the server.
This is a tried and proven method by OBIEE developers for several years.
Option 2:
We can achieve similar result using action frame work “Invoke Browser Script” method. This option doesn’t require changing column format to HTML etc. However, you do need to know little bit about JavaScript.
In below example I have setup a master analysis and detail analysis. When I click on a product in master report it shows up in detail report. I’m being lazy to setup a complex scenario. This concept would work for any scenario.
Step 1:
Setup a javascript function that handles URL creation and calling in userScript.js
Every function you like to add to userScript.js has two entries in file. One for function definition and other for publishing it’s parameters to action frame work.
Notice my URL base. It starts with ‘saw.dll’. There is no need to worry about code migration from dev to qa to prod. It will automatically call corresponding objects from where ever you are running the code.
USERSCRIPT.navmyURL = function(aParams)
{
var urlbase = ‘saw.dll?PortalGo&path=%2Fshared%2FVenu%20Sample%2FURL%20parms%20Drill&Action=Navigate&col1=%22Products%22.%22Product%22&val1=’;
var url = urlbase + aParams[‘pProdnum’];
window.open(url, “mywindow”);
};
USERSCRIPT.navmyURL.publish =
{
// The existence of this ‘publish’ object causes the ‘USERSCRIPT.example_displayParameters’ function to be
// shown when browing the available user script functions (during creation of a Script action).
// If you wish the Script function to have parameters automatically created on selection of the function,
// create a ‘parameters’ object as shown below.
// You can have any number of parameters, with each parameter requiring a unique name, prompt and an
// optional value.
//pProdnum
parameters :
[
new USERSCRIPT.parameter( ‘pProdnum’, ‘Enter value for Product Number’, ‘this will be set to the url’ )
]
// If no generated parameters are required, either create an empty array
// parameters : []
// or don’t declare the ‘parameters’ object at all.
};
Step2:
Once a function is defined in userScrip.js file, it will become available via Action Framework browser scripts option. I have defined product number as parameter to my function.
Step 3
Step 4:
Society of cardiac nurses membership form 