Pivot table – Conditional Coloring and Tooltips

Once I was working for a giant UK based client where we had a requirement to replace the existing .Net system . As everybody know that OBIEE is not a programming language rather a tool and there must have certain limitations as this could not be customized like a language .This is pretty hard to make a realization to them as they mostly focus on business needs rather thinking from technical perspective . Which is true indeed ! So sometime we say this is not possible very upfront and outright and sometime we need to further dig down on any alternative solution or workarounds . Sometime it is successful and sometime not . So this is all about the background of this thread and how myself succeed to help there business achievement .

The requirement was something like there would be multiple measure which need to be displayed against dimensions in pivot table . “Measure 1”  is a target achievement measure which need to be shown either Green or Red status based on target value set (target value is not hard-coded and its dynamic and driven by a backend threshold value ). For other measures at the end month there might be some order which is confirmed , some unconfirmed and some yet to be build . Now once user hover mouse to the colored cell (of those status represented in Legend) it should show the information in tooltips .A valid information in this scenario could be specifying  the dynamically changed date time window and mention the type of the period i.e building , confirmed or unconfirmed .

Lets have a look on the pivot report :

Conditional Coloring and Tooltip

Nice huh ?? I have actually googling around to get a readymade solution and unfortunately it doesn’t help . Finally after building this I even get surprised about nothing is technically impossible !

Nows lets see the how the measure and its status entirely driven dynamically in RPD  .Only change done in webcat is treating the data format as HTML .

 Conditional Coloring and Tooltip2

Conditional Coloring and Tooltip3



However you can put same calculation in Catalog itself in column formula to achieve this . But for this let BI server understand that you want “Server Complex Aggregate” to represent the conditional column measure .

Conditional Coloring and Tooltip4






The commentary is another interesting stuff where based on status of each month order Admin user want to add/update there comments which navigated to a target where Writeback has been implemented . Commentary code is like below :


Conditional Coloring and Tooltip5

Report Title Navigation – OBIEE

Till release , as I know , there is no facility/feature to navigate from  Report title to any Dashboard . But we had a customer requirement to get that feature so when you click on “Top 10 Sales Overview” report title it will move you to “Actual Sales” Dashboard page . There is no OOB facility to cater this requirement as the Title doesn’t accept any HTML code inside it . You could push this reqr back and could come up with an alternative solution to provide a link just below that report which will finally navigated to the target dashboard .

However this could be achieved if we do a little bit tweak with the report views . So I have found a workaround which is definitely behave like you are clicking on title and navigating to dashboard . Requirement is to show the navigation like below :

Title Navigation1








So as you see I have moved my subtitle info to Title view and pick up Narrative view , match the color and template like default OBIEE title template and achieve my business .Internally the Title region will be driven by Narrative view rather actual Title view .Here is the Narrative view code to fulfill the objective .

Title Navigation2

Multiple RPD & Multiple Presentation service instance on single BI Server

The objective of the thread is to understand the process of configuring multiple RPD and Presentation service in same BI server instance .Once that will be understood it can be further extended in scalable solution to integrate the approach on multiple server based instance as per business demands .The OBIEE suite documentation mentioned this functionality could be achieved but never mentioned the detail steps how to achieve this .

The initial bit could be found at below URL of Mark Rittman though not in detail :


My goal in this thread is to clearly define the entire process however multiple Presentation Services instances on a machine is “NOT SUPPORTED BY ORACLE” .

The effective use of this approach will support multiple RPD editing online and also modifying multiple BI catalog online through two different instance .Also this would facilitate the running of failover instance in real life production environment .Apart from that customer could also ask for hosting two different (dev and test) instance(environments) in single host . 

Now lets assume that the two RPD i.e deba.rpd & test.rpd and there respective two catalogs both working properly while loaded individually and working independently .

Configuring the NQSCONFIG

Lets configure the NQSCONFIG.ini file as below :

RPD1  =       deba.rpd , DEFAULT; 
RPD2    =     test.rpd ;

Configuring the DSN

Create 2 Oracle BI Server System DSN i.e DSN1 to point to default repository deba.rpd and DSN2 to point to test.rpd . Since we are configuring two RPD in parallel to single BI server instance hence  for both DSN we point to server Local with default Port 9703 . The 9703 port is ODBC communication port between BI Server and Presentation Services .

Multiple RPD and Presentation Instance-1Multiple RPD and Presentation Instance-2






N.B : whatever repository will be put under change Default repository section will be override by the DEFAULT settings in NQSCONFIG.ini .You can tick “Connect to Oracle BI Server to obtain default settings” ,so that RPD connection will be established as per the definition in .ini file after BI server restart .

Configuring the Catalog and instanceconfig.xml

For both deba.rpd and test.rpd we have catalog file under ../$OracleBIDataHome/web/catalog/” as “deba” and “test” having different sets of reports in each of them to distinguish properly.So lets make catalog “deba” online after making the below changes in instanceconfig.xml .


Restart the services and log into catalog for “deba” and you can verify that the query running on deba.rpd and your dashboard display the reports relevant for catalog “deba” .Verify that from NQServer.log file .

Edit the xml and make the similar type of change to point to DSN2 and catalog “test” .It should work as well pointing to repo “test” .

Now till this point single BI server hosted two repository and you since the two repo available online hence you can easily make the online modification just by selecting the DSN from Admintool in online mode .

Deploying a new Presentation Service instance

As we know that there must be something different to distinguish two Web server instance . The typical URL is : http://localhost:9704/analytics where “analytics” is the context root and deployable J2EE module .My aim to create another context root by deploying a new instance of the analytics application which can be done by the Enterprise Manager as (http://localhost:9704/em). Typical default userid is “oc4jadmin” .

Lets follow the steps below to deploy the module “analytics.ear” from ..\$OracleBIHome\oc4j_bi\j2ee\home\applications .

Multiple RPD and Presentation Instance-3Multiple RPD and Presentation Instance-4






Multiple RPD and Presentation Instance-5

 Multiple RPD and Presentation Instance-6





Duplicating instanceconfig.xml for Presentation services

The second presentation service should use its own catalog i.e “test” and connect to the repo “test.rpd” while first presentation service would use “deba” .Hence there must be two separate xml web config file to point to different catalog .Duplicate the instanceconfig.xml file and create two xml as instanceconfig_deba.xml , instanceconfig_test.xml .


<Listener port=”9712″/>



Replace $ORACLEBIDATAHOME as per your env settings .

Configuring Presentation service communication channel for OC4J

Context root “analytics” can be communicated by OC4J using the default settings as per web.xml under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics\analytics\WEB-INF” via default port 9710  .

But to communicate with context root “analytics_deba” I need to change the web.xml file under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics_deba\analytics\WEB-INF” via default port 9712  .

Search for oracle.bi.presentation.sawserver.port and change the param-value into 9712.

Multiple RPD and Presentation Instance-7







After creating the new configuration files we have to re-start the BI Server , BI Presentation services. Please restart the OC4J instance also .

Running two presentation service parallelly

To start the new presentation services I create two batch file with below target shortcuts .

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_test.xml

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_deba.xml

First stop all service and then start BI and OC4J service and after that invoke the above two scripts .So the presentation services will be started and you can login and type the following URL  in your web browser:

So presentation service application/context-root  “analytics” will refer to one repository file “deba.rpd”  and “analytics_deba” will refer to “test.rpd” but you now have the ability to create different reports, dashboards etc via different presentation services using single BI server env/resource .

N.B : You can define multiple catalog path in one instanceconfig.xml but only one will be active at a time and that will be the one which defined first .Hence the idea is to create multiple instanceconfig to access multiple catalog .