Sunil S. Ranka's Weblog

Superior BI is the antidote to Business Failure

Way To Solve Multiselect Prompt Bug

Posted by sranka on April 6, 2009

Hi All,

Thanks for all the positive response . Its been while since I posted any thing. Been busy with preparing for my collaboration-09 presenation. Yes!!! I am presenting at OAUG’s collaboration-09 conference schedule in the 1st week of May in Florida. For people who are interested, topic would be — Need for web 2.0 and OBIEE integration –. Coming back to this post.

Faced some weird and unusual problem with Multiselect.  The problem was so severe that if you don’t have proper security implemented on RPD side, than any one can see any data. hmmmmmm.. sounds scary!! yes it is scary so here is what happens.  In the following image you get to see all the name of the countries from the selected continent.

Results when clicked on ... from the base page

Results when clicked on ... from the base page

The actual fun begins when you hit the Go button,  and irrespective of which continent has been selected all the countries in the world will get displayed. see the image attached

Results shown when clicked on the Go button on the result screen

Results shown when clicked on the Go button on the result screen

What is the issue :

After investigating found out that whenever GO button is clicked, the constraint value has not been passed and all the values from the dimensions, are been retrieved.

Solution:

To solve the issue we need to modify two javascript files residing in the folder res/b_mozilla/prompts/ :

  • globalfilterprompt.js changes to — GFPDoMultiSelect — function
  • gfpmultiselect.js changes to — GFPMultiSelect.prototype.search — function

Changes :

Following is the modified — GFPMultiSelect.prototype.search — function, all the text in the bold are the changes ::

GFPMultiSelect.prototype.search = function()
{
this.sWhere = “”;

var obj1 = window.parent.document.getElementById(“customMSPromptdiv”);

/* sranka — added for MultiSelect Data Persistance*/
try{
if(obj1.value == ”){
this.sWhere = ” ( 1 = 1 ) ” ;
}else{
this.sWhere =  obj1.value;
}
}catch(e){
this.sWhere = ” ( 1 = 1 ) ” ;
}

if(this.matchTable.style.display == ”)
{
if (this.searchForm.Constraint.value != “”)
{
var sValue = null;
switch (this.searchForm.Match.value)
{
case “beginsWith”:
sValue = GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value) + “%”;
break;
case “endsWith”:
sValue = “%” + GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value);
break;
case “contains”:
sValue = “%” + GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value) + “%”;
break;
case “like”:
sValue = this.searchForm.Constraint.value;
}

if (sValue != null)
this.sWhere = this.sWhere + ” AND ” + this.vColumns[0] + ” LIKE ‘” + sValue + “‘”;

}
}
else if(this.dateTable.style.display == ”)
{
var tA = null;
var tB = null;

switch(GFPMultiSelect.primaryType)
{
case ‘date':
tA = tDTP.parse(this.searchForm.BetweenA.value, 2 | 8 | 16 | 32);
tB = tDTP.parse(this.searchForm.BetweenB.value, 2 | 8 | 16 | 32);
break;
case ‘time':
tA = tDTP.parse(this.searchForm.BetweenA.value, 4);
tB = tDTP.parse(this.searchForm.BetweenB.value, 4);
break;
case ‘timeStamp':
// convert
tA = tDTP.parse(this.searchForm.BetweenA.value, 1 | 2 | 4 | 8 | 16 | 32);
tB = tDTP.parse(this.searchForm.BetweenB.value, 1 | 2 | 4 | 16 | 32);
//convert to data timezone
if (this.nDisplayToDataTZOffset)
{
if (tA != null)
tA.adjustTimeZoneOffset(this.nDisplayToDataTZOffset);
if (tB != null)
tB.adjustTimeZoneOffset(this.nDisplayToDataTZOffset);
}
break;
}

if(this.searchForm.BetweenA.value.length > 0 && !tA)
{
alert(kmsgWBInvalidInput + “\”” + this.searchForm.BetweenA.value + “\””);
return false;
}

if(this.searchForm.BetweenB.value.length > 0 && !tB)
{
alert(kmsgWBInvalidInput + “\”” + this.searchForm.BetweenB.value + “\””);
return false;
}

if(tA != null)
{
var sA = DateTimeParser.buildDateTimeClause(‘>’, this.vColumns[0], GFPMultiSelect.primaryType, tA);

if(sA != null)
this.sWhere = sA;
}

if(tB != null)
{
var sA = DateTimeParser.buildDateTimeClause(‘<‘, this.vColumns[0], GFPMultiSelect.primaryType, tB);

if(sA != null)
this.sWhere = this.sWhere.length == 0 ? sA : (this.sWhere + ” AND ” + this.sWhere + ‘ AND ‘ + sA);

}

}
else if(this.numericTable.style.display == ”)
{
var tA = this.searchForm.BetweenNA.value;
var tB = this.searchForm.BetweenNB.value;

if(tA.length > 0)
{
tA = parseFloat(tA, 10);
this.sWhere = this.sWhere + ” AND ” + this.vColumns[0] + “>=” + tA;
}

if(tB.length > 0)
{
tB = parseFloat(tB, 10);
var clause = this.vColumns[0] + “<=” + tB;
this.sWhere = this.sWhere + ” AND ” + this.sWhere.length == 0 ? clause : this.sWhere + ” AND ” + clause;
}
}

this.choicesDiv.innerHTML = “”;
this.totalSpan.innerHTML = “0”;
this.totalSpan.setAttribute(“total”, 0);
this.moreLink.style.display = “none”;

VTDisplayValues(this.choicesDiv, this.vColumns, this.subjectArea, ‘kmsgGFPMultiSelectSearchValueTableRow’, this.sWhere, this.timeZone, this.timeZoneOffset,
‘scroll’, this.sId, null, null, null, null, null, “idGFPMultiSelect” + this.sId);
}

Following is the modified — GFPDoMultiSelect– function, all the text in the bold are the changes ::

function GFPDoMultiSelect(tEvent, sTextAreaID, sColumn, sSubjectArea, sWhere, sID, sCategory, sPrimaryType, sDisplay, sSQL, sDataTimeZoneOffset, sDisplayTimeZone)
{

/* sranka — Added this code as patch for persisting values for MultiSelect */
var parentElem = window.parent.document.getElementById(sTextAreaID);
//alert(‘GFPDoMultiSelect GlobalFilterPrompt.js :: ‘ +sTextAreaID);
var newdiv = window.parent.document.createElement(‘input’);
var divIdName = ‘customMSPromptdiv';
newdiv.setAttribute(‘id’,divIdName);
newdiv.setAttribute(‘name’,’name’);
newdiv.setAttribute(‘value’,sWhere);
newdiv.setAttribute(‘type’,’text’);

var browserName=navigator.appName;

if(browserName == ‘Netscape’){
parentElem.appendChild(newdiv);
}else{
window.parent.parent.document.body.appendChild(newdiv);
}

var tMultiSelect = new GFPMultiSelect();
var tDialog = new XUIDialog(“idGFPMultiSelect”+sID, tMultiSelect, null);
tDialog.show(null, -1, -1, null, null, null, null, true);
tMultiSelect.initialize(sTextAreaID, sColumn, sSubjectArea, sWhere, sID, sCategory, sPrimaryType, sDisplay, sSQL, sDataTimeZoneOffset, sDisplayTimeZone);

return false;
}

I hope this will help solving some of the issue. I know that the code I have provided is not well formatted, please ping me or leave me a message for any questions or difficulties.

Hope this helps

Thanks

Sunil S Ranka

About these ads

7 Responses to “Way To Solve Multiselect Prompt Bug”

  1. Ravi Allam said

    Hi Sunil,
    I have tried what ever have you told in your blog for bring the constraint values. I have changed 2 files
    1)globalfilterprompt.js changes to — GFPDoMultiSelect — function
    2)gfpmultiselect.js changes to — GFPMultiSelect.prototype.search — function
    that you have mentioned but there was not luck. Can please tell that if i have missed anything from what you have told.

    Your kind help is appreciated.

    Thanks,
    Ravi Allam

  2. Cederash said

    Даже и не придирешься!

  3. Ferinannnd said

    Спасибо. Добавлено в закладки

  4. Avertedd said

    Да, есть над чем задуматься. Спасибо!

  5. Sujit said

    Hi Sunil,

    Great article. I came across this problem for one of my clients a year ago. Ive applied the modified script to the js files appropriately. I am just curious though, is it expected that if I click on the elipses (‘…’) button, the GFPDoMultiSelect function generates rectangular boxes on the lower left corner? For every click I do on the elipses, one rectangular box is added to the page. Some are blank with just a blue border (exactly like the section border), and some contains the filter definition for each prompt.
    Thanks in advance.

    Sujit

  6. Antony said

    2 Ravi Allam

    You have to copy the changed files into %OC4J_HOME%\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts and restart oc4j, BI Server and Presentation server services.

    2 Sinul
    It’s a great job, but I have some questions:
    1. In your code for GFPMultiSelect.prototype.search we may find such changed strings:
    – this.sWhere + ” AND ” + this.sWhere + ‘ AND ‘ + sA
    – this.sWhere + ” AND ” + this.sWhere.length == 0 ? clause : this.sWhere + ” AND ” + clause
    What is the meaning of such construction?
    For example, if sWhere=”(1=1)” then we get:
    – “(1=1) AND (1=1) AND ” + sA. In this case the sWhere repeats twice.
    – “(1=1) AND (1=1) AND ” + clause. In this case the sWhere repeats twice too.
    sWhere cann’t be “” or null according to begin of GFPMultiSelect.prototype.search. So we may replace the next strings
    – this.sWhere = this.sWhere.length == 0 ? sA : (this.sWhere + ” AND ” + this.sWhere + ‘ AND ‘ + sA); on this.sWhere = this.sWhere + ” AND ” + sA;
    – this.sWhere = this.sWhere + ” AND ” + this.sWhere.length == 0 ? clause : this.sWhere + ” AND ” + clause; on this.sWhere = this.sWhere + ” AND ” + clause;
    2. When I tested your code i found irregular behaviour of Multiselect.
    I have a complex prompt with two prompts in it. The first prompt is a drop-down list with two values, for example ‘A’ and ‘P’, default value ‘A’. The second prompt is a multiselect with constrain option checked. So I add this prompt on dashboard. The I press button ‘…’ – I get the constrained list, press button ‘Go’ in multiselect window with match ‘begins with’ and your code works – the constrain remains. Then I change the value in a drop-down list from ‘A’ to ‘P’ and press button ‘…’ for multiselect prompt. All ok. I get constrained result/ But then I press button ‘Go’ in multiselect window with match ‘begins with’ and get “No Choices Available”. It’s a BUG! How may I correct it? When I press ‘Go’ button for complex prompt and reports on dashboard is done then multiselect ‘…’ button works well for value ‘P’ in drop-down list until I change it to ‘A’ and all repeats once again.

    • Antony said

      I found solution – I ask and I answer :)

      The problem is:
      we create ‘input’ element but not delete it. Then we create next and it appears on screen. When we get it value we get invalid string. So I change the Sinul’s code to:

      var parentElem = window.parent.document.getElementById(sTextAreaID);
      //alert(‘GFPDoMultiSelect GlobalFilterPrompt.js::’ +sTextAreaID);
      var divIdName = ‘customMSPromptdiv';

      //get descriptor of our input element
      var olddiv = window.parent.document.getElementById(divIdName);
      var browserName=navigator.appName;

      //if ‘input’ element exists then remove it
      if (olddiv != null)
      {
      if(browserName == ‘Netscape’)
      {
      var len = parentElem.childNodes.length;

      for(var i = 0; i < len; i++)
      {
      if(parentElem.childNodes[i].id == divIdName)
      {
      parentElem.removeChild(Node1.childNodes[i]);
      break;
      }
      }
      }else{
      olddiv.removeNode(true);
      }
      }

      var newdiv = window.parent.document.createElement('input');

      newdiv.setAttribute('id',divIdName);
      newdiv.setAttribute('name','name');
      newdiv.setAttribute('value',sWhere);
      // I change attribute 'type' to 'hidden' because users cann't see this 'input' element
      newdiv.setAttribute('type','hidden');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: