Feeds:
Posts
Comments

Archive for the ‘OBIEE Jquery’ Category

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.

concept

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

prompt_for_operator

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:

prompt details

Where clauses in Analysis:

where_conditions

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

dashboard_setup

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>

analysis

Result:

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.

Read Full Post »

Every once in while we all come across of Dashboard requirement where users want to add ton of prompts. Then they would complain that they have to scroll down to use them or click on “Apply” button for submission.

Typical approach for these kinds of challenges would be to add multiple prompts to dashboard with each prompt having it’s own apply button. In most cases this is a good solution. However you need to know few limitations with this approach.

  • Prompts are divided into multiple groups.
  • Users have to click on Apply button’s individually.
  • Multiple trips to BI server. Only one group of prompts are submitted with each click on Apply button. I have recently elaborated this issue in a related post.here
  • It is not possible enforce constraints with divided prompts.
  • We can place prompts in separate sections, use collapse and expand options to organize prompts. When a prompt section is collapsed all input controls are hidden.

Using JQuery we solve this problem. We can have add a custom link inside prompts to hide/show less frequently used prompts. This will ensure that all input controls are inside one prompt. So they can all be submitted with one single click. We can also enforce constraints etc.,

I have posted a similar article where we can hide/show prompts based on a prompt value. here This approach is very similar to that.

Here is how this solution works.

When dashboard is launched for the first time few less frequently used (business has to determine what they would be) are hidden.

img1

User can choose to see them if needed by clicking on “Show More Prompts” link.

Note: This is custom link inserted by JQuery inside OBIEE prompt at run time.

img2

When prompts are shown, link will change to “Hide Extra Prompts”.

These preferences persist when user is on the dashboard. Users can submit dashboard without losing the show/hide preference they just made.

Implementation Steps

Step 1:

Add JQuery functions to Dashboard in a text object in the same section as prompt. These functions are called in document.ready function so that links are embedded and executed the first time dashboard is launched. If your prompt has constraints, every time a selection is made OBIEE refreshes entire Dashboard Prompt code from Server. In order apply custom code in newly refreshed code, we would need to call same function from broserdom.js. I will be explaining this in step3.

We are also using a hidden input field as defined in Step2 to remember whether user clicked on “Show” or “Hide” option.

<head>

<style type=”text/css”>

.CVFormatCell{text-align:left;}

</style>

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

<script type=”text/javascript”>

function customFunctions() {

/* this code is executed several times by browserdom.js during load time as well as interactions with prompts

You need to have a logic to call hidePrompt only one time for efficiency

You can contact me if you need any help

*/

setForm();

}

function setForm() {

var k = 0;

var linktype = $(“#linktype”).val();

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

k++;

//if (k == 2) alert($(this).html());

var l = 0;

$(this).find(“input”).each(function(){

l++;

if ($(“#a1show”).length == 0) {

if (l == 1) $(this).parent().parent().parent().parent().parent().append(‘ <br/><div style=”text-align:left;width:100%;”> <a id=”a1show” onClick=”javascript:showElement()”> <u>Show More Prompts</u></a> <a id=”a2hide” onClick=”javascript:hideElement()”> <u>Hide Extra Prompts </u></a> </div>’);

}

if (linktype == “hide”) {

if (l == 2) $(this).parent().parent().parent().parent().parent().parent().parent().parent().hide();

}

});

});

if (linktype == “hide”) {

$(“#a2hide”).hide();

$(“#a1show”).show();

} else {

$(“#a2hide”).show();

$(“#a1show”).hide();

}

}

function showElement() {

var k = 0;

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

k++;

//if (k == 2) alert($(this).html());

var l = 0;

$(this).find(“input”).each(function(){

l++;

if (l == 2) $(this).parent().parent().parent().parent().parent().parent().parent().parent().show();

});

});

$(“#a1show”).hide();

$(“#a2hide”).show();

$(“#linktype”).val(“show”);

}

function hideElement() {

var k = 0;

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

k++;

//if (k == 2) alert($(this).html());

var l = 0;

$(this).find(“input”).each(function(){

l++;

if (l == 2) $(this).parent().parent().parent().parent().parent().parent().parent().parent().hide();

});

});

$(“#a2hide”).hide();

$(“#a1show”).show();

$(“#linktype”).val(“hide”);

}

</script>

<script type=”text/javascript”>

$(document).ready(function(){

/*  This code is executed when you visit dashboard page for the first time or after apply button is submitted.*/

setForm();

});

</script>

Step 2:

Add below code another section on dashboard. This section will contain only a text object with below text. Check “Contains HTML” check box.

<input type=”text” name=”linktype” id=”linktype” value=”hide” />

Step3:

I had a typo in below screenshot. It should be customFunctions instead of customerFunctions. This is one time change to browserdom.js which will execute customFunctions function on dashboard page when it exists.

In all dashboards where you want to implement this functionality you will be adding code similar to below.

Note: we have already covered this code in Step1. I’m using the same to explain concept here. Main purpose of browserdom.js change is to reapply our custom code whenever OBIEE refreshes prompts code from Server using AJAX calls.

function customFunctions() {

setForm();

// you can add all relavant functions you need to call

}

browserDOM.js

Other Important tips: BrowserDom.js is called several times during page execution. Your JavaScript needs to smartly written to execute only one time during page events.

Read Full Post »

First, let’s make a valid use case for this scenario.

OBIEE Dashboard Prompt object contains one or more user input controls. User can input values and submit them by clicking on “Apply” button. In newer releases of OBIEE there is support for auto submission of prompts depending upon your configuration.

Why we need to submit multiple prompts?

Consider below scenario.

You would like to give users a choice to select two different criteria’s for selecting products. You would like summarize results from first criteria as “Selection 1”, from second criteria as “Selection 2”.

These selections are reported in analysis for comparison.

Design 1

Below is a sample screenshot of dashboard that attempts to provide this functionality.

img1

Issues with design 1.

  • There are two apply buttons.
  • User can only make one set of Brand & Product selections.
  • Clicking on Apply button can submit only that prompt.
  • Any changes made in the other prompt are simply lost and are not submitted to dashboard.
  • So, this solution is a two step process. User submits selection 1, then selection 2.
  • If users are not properly trained this would lead to confusion.
  • Also there other cosmetic issues like analysis not aligning in dashboard properly.

Design 2

Since most of the issues are related to how Apply button works and prompts are submitted, it is obvious that only one prompt with both selection 1 & selection 2 inputs.

img2

Issues with design 2.

  • If users make a mistake and want to reset selections, both selections would be lost.
  • May lead to confusion if several input controls are involved in each selection.
  • Both selections are in prompt, so it will lead to confusion.

Conclusion: It appears that is not possible to submit more than one dashboard prompt in one click. Combining both selections into single prompt leads to confusion & bad UI.

Below is the solution I’m proposing.

Design 3

img3

  • Keep 2 separate prompts for Selection 1 & Selection 2. (Similar to design 1)
  • Hide apply & reset buttons for these prompts.
  • Create a third prompt that combines input values from Selection 1 & Selection 2. (Similar to design 2)
  • Keep combined prompt in a separate section & hide using “display:none” in custom CSS.
  • Add a custom buttons on dashboard for resetting Selection 1 & Selection 2 prompts.
  • Add a custom “Apply” button, which is named as “Compare”.
  • Write a JQuery script to move selected values from Selection 1 & Selection 2 to combined prompt. Submit hidden combined prompt.

Summary of Design 3 Solution: Individual Selection 1 & Selection 2 prompts are used for UI only. Compare button apply these selections in combined prompt. Since combined prompts is submitted, Dashboard gets are selections in single click. So, we have win-win situation. Dashboard has better UI, we are using very little custom code to make this happen.

Design 3 Implementation Steps

Img 4

Prompts

Arrange Selection 1, Selection 2, Compare (Combined Prompts) as displayed above.

Hide Column 4, Section 1 by entering custom CSS code “display:none” in section formatting options. Select “Use Custom CSS style” option.

HTML & JavaScript, JQuery Objects

Note: Add them as TEXT objects as displayed above. Select “Contains HTML markup” check box.

Reset Button for Selection 1:

<table width=”100%” style=”text-align: right;”> <tr> <td>

<input  type=”button” class=”button” name=”Reset1″ value=”Reset” id=”Reset1″ onClick=”reset1()” />

</td> </tr> </table>

Reset Button for Selection 2:

<table width=”100%” style=”text-align: right;”> <tr> <td>

<input  type=”button” class=”button” name=”Reset2″ value=”Reset” id=”Reset2″ onClick=”reset2()” />

<input  type=”button” class=”button” name=”Compare” value=”Compare” id=”Compare” onClick=”move()”  /> </td> </tr> </table>

JQuery Code: It has functions for Reset1 , Reset2, move & submit selections to combined hidden prompt

<head>

<style type=”text/css”>

.CVFormatCell{text-align:left;}

#gobtn.button{display:none;}

#reset.button{display:none;}

</style>

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

<script src=”http://api.jquery.com/scripts/events.js”></script>

<script type=”text/javascript”>

function reset1() {

var j = 0;

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

j++;

if ( j == 2) {

$(this).find(“input”).each(function(){

if( $(this).attr(‘id’).match(/reset/) ) {  $(this).focus(); $(this).click(); }

});

}

});

}

function reset2() {

var j = 0;

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

j++;

if ( j == 3) {

$(this).find(“input”).each(function(){

if( $(this).attr(‘id’).match(/reset/) ) {  $(this).focus(); $(this).click(); }

});

}

});

}

function  move(){

var j = 0;

var formData = [ ];

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

j++;

if (( j == 2) || (j ==3)){

$(this).find(“input”).each(function(){

if( $(this).attr(‘type’).match(/text/) ) {

//alert($(this).attr(“id”));

formData.push($(this).attr(“id”));

}

});

}

if (j == 4) {

var l = 0;

$(this).find(“input”).each(function(){

if( $(this).attr(‘id’).match(/gobtn/) ) $(this).click();

if( $(this).attr(‘type’).match(/text/) ) {

$(this).click();

$(this).val($(“#” + formData[l]).val());

$(this).change();

$(this).parent().find(“img”).click();

var replaceID = $(this).attr(“id”);

var valuesArray = $(“#” + formData[l]).val().split(‘;’);

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

if ($(this).parent().html().indexOf(“CHECKED”) > 0) $(this).click();;

});

$.each(valuesArray, function(index, value) {

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

if ($(this).html() == valuesArray[index]) {

var clickID = $(this).attr(“for”);

if ( clickID.indexOf(replaceID) >= 0){ $(this).click();}

}

});

});

$(this).parent().find(“img”).click();

$(this).click();

l++;

}

});

}

});

}

</script>

<script type=”text/javascript”>

$(document).ready(function(){

var j = 0;

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

j++;

if ( j == 4) {

$(this).find(“input”).each(function(){

if( $(this).attr(‘type’).match(/text/) ) { $(this).parent().find(“img”).click(); $(this).parent().find(“img”).click();}

});

}

});

// just to get out of last clicks

var j = 0;

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

j++;

if ( j == 4) {

// ideally focus on first field click and leave

$(this).find(“input”).each(function(){

if( $(this).attr(‘id’).match(/reset/) ) {  $(this).focus(); $(this).click(); }

});

}

});

});

</script>

</head>

Thanks for visiting this article.

Read Full Post »

Dashboards are one of the key strengths of OBIEE suite.

There are few limitations with out-of-the-box dashboard functionality. It is almost impossible to implement custom HTML form events that any basic web programming tool would provide.

For example, you have radio prompt on the dashboard. Depending on the radio button clicked you want to hide or show certain other prompts in the same prompts group.

Current out-of-box functionality supports hiding entire prompts section by adding custom CSS syntax “display:none” in section properties. But you cannot hide certain prompts while showing other prompts in the same prompts section.

If you are savvy JavaScript or JQuery programmer you can add a script to Dashboard to achieve this functionality.

However, it will only work when you land on the dashboard page for the first time. Once you click on the prompt, depending the constraints, prompt type etc., OBIEE would issue an AJAX call to refresh all prompts from server again. Since all ID’s and references change, the stickiness of your script is lost.

Today, I would like to share you a solution I have found to work around this problem. This is a useful techniques for those developers and admins that are on the hook for delivering desired user functionality at all costs.

This technique requires a one line change to browserDOM.js. So any time you upgrade OBIEE, you need to make sure to add this code back in this file.

Requirement:

By default your prompts are like below. ( All objects are developed using OBIEE SampleLite application.)

default

You would like to hide “Brand” prompt when Product radio button is selected.

hide1

You would like to hide “Product” prompt when Brand radio button is selected.

hide2

Below is the list of steps to achieve desired functionality.

Step 1: Add below JQuery JavaScript to your dashboard.

<head>

<style type=”text/css”>

.CVFormatCell{text-align:left;}

</style>

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

<script type=”text/javascript”>

function customFunctions() {

/* this code is executed several times by browserdom.js during load time as well as interactions with prompts

You need to have a logic to call hidePrompt only one time for efficiency

You can contact me if you need any help

*/

hidePrompt();

}

function hidePrompt() {

var j = 0;

var selected = ‘x’;

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

j++;

if (j == 2) {

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

if ($(this).attr(“checked”) == “checked”)  selected = $(this).val();

});

var k = 0;

$(this).find(“input”).each(function(){

if ($(this).attr(“class”) != “checkboxRadioButton”) {

k++;

if ( (selected == “Product”) && ( k == 2) )  $(this).parent().parent().parent().parent().parent().parent().parent().parent().hide();

if ( (selected == “Brand”) && ( k == 1) )  $(this).parent().parent().parent().parent().parent().parent().parent().parent().hide();

}

});

}

});

}

</script>

<script type=”text/javascript”>

$(document).ready(function(){

/* This code is executed when the Dashboard page is loaded for the first time or after apply button is submitted */

hidePrompt();

});

</script>

Step 2: Add below to your browserDOM.js in following locations.

*******\Oracle_BI1\bifoundation\web\app\res\b_mozilla

*******\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla

browserDOM.js

Above should be enough to help solve your prompt customization requirements If you already know JavaScript or JQuery.

Note: I had a typo in this screenshot. It should be customFunctions() instead of customerFunctions().

Other Important tips: BrowserDom.js is called several times during page execution. Your JavaScript needs to smartly written to execute only one time during page events. You can see my comments in JavaScript.

In upcoming articles I would like to share more about how JQuery works with OBIEE.

Update: Recently I have been informed that other JQuery experts achieved the same without modifying any code in browserdom.js. Ideally this is what you would want.

This proves the point that we can use JQuery to improve standard OBIEE UI.

Read Full Post »