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.

**Details:**

**Where clauses in Analysis:**

**jQuery Scripts:**

Add below javaScript to Dashboard.

<head>

<style type=”text/css”>

.CVFormatCell{text-align:left;}

.PromptStepTitleCell{display:none;}

.promptButtonsCell{display:none;}

</style>

<script src=”http://code.jquery.com/jquery-latest.js”> </script>

<script type=”text/javascript”>

function setForm() {

var i=0;

$(‘form’).each(function() {

i++;

if (i == 2) {

var myForm = $(this);

$(myForm).find(“label”).each(function(){

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

$(this).parent().parent().hide();

}

});

var j = 0;

$(“.promptEditBoxField”).each(function(){

j++;

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

$(this).hide();

}

});

}

});

}

</script>

<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() {

i++;

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);

$forminputs.eq(13).hide();

$forminputs.eq(14).hide();

$forminputs.eq(15).hide();

}

});

$(“#gobtn”).click();

}

</script>

<script type=”text/javascript”>

function clear11FilterDisplay() {

$(“.FilterCell”).each(function(){

if( ($(this).html() == ‘1 = 1’) || ($(this).html() == ‘1=1’)){

$(this).parent().html(‘ ‘);

}

});

}

</script>

<script type=”text/javascript”>

$(document).ready(function(){

setForm();

});

</script>

</head>

**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”>

setForm();

clear11FilterDisplay();

</script>

Result:

**Conclusion:**

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.