Archive for the ‘OBIEE UI’ Category

This is a common problem new OBIEE developers can run into.

Below analysis is showing Brand, Product, Revenue and revenue share by product with in Brand.



Below is the formula used to calculate revenue percentage by product with in brand.



Problem: Percentages with in Brand total to 100%, which is correct.

However, grand total is showing 274.5%. I am expecting to see 100% in grand total row.


Root Cause:



OBIEE is receiving SUM by product from database. All report context specific calculations ( SUM (REVENUE BY BRAND)) are being handled in BI server. So, OBIEE was able to show 100% at Brand level. However, BI server was not properly calculating results in grand total row. I don’t understand the exact reason, but it is failing to perform proper calculation.





Change Percentage calculated column aggregation rule to “Server Complex Aggregate”








OBIEE is sending sum(revenue by Brand) calculation to database. See sum(d1.c1) over (partition by d1.c2) in above screenshot.

Also you can notice additional sum(revenue) sql statement sent to database for grand total row calculation.

Read Full Post »

OBIEE 11g action frame work is an excellent feature that offers lot of benefits for BI users. However, there is a minor inconvenience with action links.  Any action link that is related to http URL request or Browser script and other type of links are being exported as part of column data. See below image. This is not an issue of those action links that navigate to other BI content.


Below link shows an analysis that is using “Invoke HTTP action”. I have add a link to http://www.google.com to produce this example.

Action Link



  • This work around works only in Dashboards.
  • We need to add an identical copy this analysis to dashboard in a separate section.  Make sure export link is enabled for this analysis.
  • Add a jQuery script in this section.
  • Hide both new analysis and jQuery script by entering “display:none” in format section.
  • Remove export link from main analysis.
  • Add a custom export link to main analysis by adding html to its section.


Trick: When user clicks on custom export link it will simulate a click on hidden analysis export menu. Since this analysis doesn’t contain action link it’s export doesn’t show action links.


This issue seems to exist in also. I hope Oracle will provide an option to exclude action links from exporting. Until that time this is the only solution I can think of.


Hidden Section:

dashboard layout




jQuery inside text object:

<script src=”http://code.jquery.com/jquery-latest.js”> </script>
<script type=”text/javascript”>
function otherExport() {

$(“.ResultLinksCell”).find(“a”).each ( function () {
if ( $(this).html() == “Export”) {
$(“.shadowMenuCell”).find(“a”).each( function () {
if ($(this).attr(“onclick”).indexOf(“Format=pdf”) != -1) {



Main Section:


Remove export links

remove links


Add Custom link using html text

<table align=”center”> <tr> <td>
<a href=”javascript:void(0)” onClick=”otherExport()”> Export pdf </a>
</td> </tr> </table>


Final Result

Below image shows custom export link in Dashboard

You can add a separate link for each export option or you can build menu similar to OBIEE export menu here. I took the easy route.



final dasboard look


PDF export results

final result


In addition to addressing action link in export issue, this technique is also useful for exporting additional columns in export etc.

Read Full Post »

Recently I have installed sample app v207.  After using image couple of time, I was not able to restart OBIEE BI Server and Presentation services. After digging into log files I found below message.

oracle.ods.virtualization.engine.util.DirectoryException: LDAP Error 49 : [LDAP: error code 49 – Password Policy Error :9000: GSL_PWDEXPIRED_EXCP :Your Password has expired. Please contact the Administrator to change your password.]


There is not much information regarding a fix for this problem in linux based VM. Most of the instructions described in related blogs need OID management services client. I was able to fix this problem using below steps.

Open a terminal in VM and follow below commands.

cd /home/oracle/oid/Oracle_IDM1/ldap/bin

export ORACLE_HOME=/home/oracle/oid/Oracle_IDM1/
export ORACLE_INSTANCE=/home/oracle/oid/bioid2/

/* TNS Names for OID are in /home/oracle/oid/bioid2/config folder. Tnsname for OID is NOT ORCL. It is OIDDB */ I spent several hours to figure this out. Thanks to APP DBA website.


Below commands unlocks super user account

./oidpasswd unlock_su_acct=true connect=OIDDB


Navigate to folder where ldapmodify resides.

cd /home/oracle/oid/Oracle_IDM1/bin

Create “pwdreset.ldif”  file using vi editor.  Enter below lines.

dn: cn=orcladmin,cn=Users,dc=us,dc=oracle,dc=com
changetype: modify
replace: userpassword
userpassword: Admin123


Run below command to reset password

./ldapmodify -h obieesampleapp.us.oracle.com  -p 3060 -D cn=orcladmin -w Admin123 -f pwdreset.ldif

create pwdpolicydef.ldif file has below

dn:cn=pwdpolicyentry, cn=common,cn=products,cn=oraclecontext
replace: pwdmaxage
pwdmaxage: 15552000
replace: pwdexpirewarning
pwdexpirewarning: 1209600
add: pwdgraceloginlimit
pwdgraceloginlimit: 5


Run below command to increase password expiration age.

./ldapmodify -h obieesampleapp.us.oracle.com -p 3060 -D cn=orcladmin -w Admin123 -f pwdpolicydef.ldif


Useful bog http://blog.csdn.net/lele5000/article/details/8057797

Read Full Post »

OBIEE has a fairly simple EDIT interface for allowing ad-hoc users to create and modify reports (analysis). Sometimes you will come across business use cases where using edit is simply too much for users. Business users deserve a simple interface for dynamically filtering a data set that is presented in dashboard. It has to be simple and easy to use for users with basic level of technical capabilities.

In this article we will discuss few options for building “Dynamic Where Conditions” to offer simple filtering capability at runtime.

Below is an example of dashboard that allows users to select Column, Operator and Value to filter results.


As shown in below screenshot, Product & Region prompts are built using out-of-box functionality.


Main Concept behind this example is the ability of OBIEE to support presentation variables in where conditions. We don’t need to use jQuery to achieve this functionality. However I’m using jQuery to make this little nicer and also have the capability to prevent invalid input values and build only valid WHERE conditions.


prompt details

Where clauses in Analysis:


jQuery Scripts:

Add below javaScript to Dashboard.

<style type=”text/css”>

<script src=”http://code.jquery.com/jquery-latest.js”> </script>
<script type=”text/javascript”>
function setForm() {

var i=0;
$(‘form’).each(function() {

if (i == 2) {
var myForm = $(this);

if ( $(this).attr(“title”) == ” “) {

var j = 0;

if ( ( j >= 4) && ( j <= 6)) {



<script type=”text/javascript”>

var oparrtxt = [ “Equal To”, “Not Equal To”, “Less Than”, “Greater Than” ];
var oparrsym = [ ” = “, ” != “, ” < ” , ” > ” ];
var coldisplay = [‘Revenue’, ‘Fixed Cost’, ‘Variable Cost’, ‘Unit Price’, ‘Discount Amt’];
var colformula = [‘”Base Facts”.”1- Revenue”‘, ‘”Base Facts”.”11- Fixed Costs”‘, ‘”Base Facts”.”10- Variable Costs”‘, ‘”Simple Calculations”.”20  Actual Unit Price”‘, ‘”Base Facts”.”3- Discount Amount”‘]

function clickApply() {
var i=0;
$(‘form’).each(function() {

if (i == 2) {
var $forminputs = $(“.promptTextField, .promptEditBoxField”);
var where1 = “1=1”;
var where2 = “1=1”;
var where3 = “1=1″;

if ( ($forminputs.eq(4).val() == ‘–Select Value–‘) || ($forminputs.eq(6).val() == ”)) {
//alert(‘no value selected’)
else {
where1 = colformula[jQuery.inArray($forminputs.eq(4).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(5).val(), oparrtxt)] + $forminputs.eq(6).val() ;

if ( ($forminputs.eq(7).val() == ‘–Select Value–‘) || ($forminputs.eq(9).val() == ”)) {
//alert(‘no value selected’)
else {
where2 = colformula[jQuery.inArray($forminputs.eq(7).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(8).val(), oparrtxt)] + $forminputs.eq(9).val() ;

if ( ($forminputs.eq(10).val() == ‘–Select Value–‘) || ($forminputs.eq(12).val() == ”)) {
//alert(‘no value selected’)
else {
where2 = colformula[jQuery.inArray($forminputs.eq(10).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(11).val(), oparrtxt)] + $forminputs.eq(12).val() ;

$forminputs.eq(13).val( where1);
$forminputs.eq(14).val( where2);
$forminputs.eq(15).val( where3);





<script type=”text/javascript”>
function clear11FilterDisplay() {
if( ($(this).html() == ‘1 = 1’) || ($(this).html() == ‘1=1’)){
$(this).parent().html(‘ ‘);

<script type=”text/javascript”>



Custom Apply button:

Add below to Dashboard to create “Apply” button.

<input type=”button” id=”Apply” name=”Apply” value=”Apply” onClick=”clickApply()” class=”button” />


jQuery Scripts in Analysis:

Add below script to analysis to remove prompt labels and 1=1 conditions from displaying in filter view.

<script type=”text/javascript”>






This is just a simple example of what can be done using dynamic where conditions. I can see several practical applications for this technique to improve usability of BI applications.

Feel free to drop a comment on how you can leverage this concepts to improve your business.

Read Full Post »

OBIEE provides several different options for hiding prompts.

  1.  Hidden Prompts
  2. Hiding prompts using guided navigation
  3.  Hiding prompts in a section
  4. Hiding certain UI controls inside a prompt

Although options 1 & 2 have been known for a while there are very few articles written about them.

1. Hidden Prompts

Here is the link to official Oracle documentation about this feature. http://docs.oracle.com/cd/E21764_01/bi.1111/e10544/prompts.htm#CIHCDDEG

This option is mainly used for setting default filters and variable values on dashboard. These prompts exist on dashboard page, but are hidden from users.

I will be writing a separate blog about few use cases for this feature.

2. Hiding prompts using guided navigation

This is another method for hiding prompts. Using guided navigation conditions we can make the section where prompts reside appear & disappear. This technique can be used for data driven/security driven prompts.

3. Hiding prompts in a section

This is another frequently blogged method, but the use cases were not widely discussed. Below are the links couple of blogger that have described the method. This is done by using custom css syntax “display:none” in “Format Section” dialog box.



This is a hidden gem for developers that want to provide custom UI improvements. The benefits go far beyond from adding custom headers and titles. I have written couple of use cases for this option.


4. Hiding certain UI controls inside a prompt

Last but not least. You can use this option to provide better UI for your users. I have described this technique in my previous blog. I had to edit browserDom.js file to make this work, but I have seen other experts do this without changing browserdom.js.

It all depends on the level of javaScript skills you have in your shop.


Read Full Post »

In OBIEE, we can use couple of methods for giving users the option to choose a display of information from list of choices presented to them.

Option 1:  View Selector

Option 2:  Guided Navigation

There are several blogs on these topics. Here are couple of links I was able to quickly find.   View Selector             Guided Navigation

View Selector is an anlysis/report level feature. So it can be used for individual reports as well as for dashboards. You can insert a report with view selector view inside dashboard pages.

On the other hand Guided Navigation is a Dashboard level feature.

Below are few important distinctions between these features.

  • Because of its nature view selector can present different views of data that originates from a single query (single or union query).
  • Guided navigation can be used switch between different reports or different views from the same report. We have to submit dashboard pages to different pages. That means we making trips back & forth between presentation server and BI Server.

Above are the major fundamental differences between these two approaches.

Sometimes you will come across a dashboard requirement where you can use either of these approaches. Technically this situation arises only when all of the required views can come from same query.

So, if you have to make decision between view selector & guided navigation which one would you choose?

I’m using below rational for making my decision.

  • I would use view selector when all of these views are coming from the same granularity of data. This way there is no need to submit the page again to see the data.
  • I would use guided navigation when granularity changes between views and there is a performance impact because of it.

What would you do differently? Please share your thoughts.

Read Full Post »

You can increase or decrease number of prompts shown in dropdowns by editing prompt XML code. This is done by adding numItemsPerPage attribut to saw:PromptUIControl element.


<saw:promptUIControl xsi:type=”saw:multiSelect” includeAllChoices=”false” maxChoices=”-1″ numItemsPerPage=”1024″>

Note: Every UI element in prompt has its own saw:promptUIControl element.

Instanceconfig.xml change affects all dropdowns.  (<Prompts><MaxDropDownValues>XXXXX</MaxDropDownValues></Prompts>)

Using numitemsperpage in prompt XML file affects only a specific prompt without changing behavior globally.

Read Full Post »

Older Posts »