RSS

Case Insensitive Search Using Dashboard Prompt

20 Apr

To make case insensitive search If you look in the configuration guide you will find that the CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQConfig file controls the case sensitive search within OBIEE. But this configuration cannot make your search as case insensitive. Be aware that you might be fooled by your database settings. If you are on an Oracle database 10G+ you can use a connection script in the repository to allow case insensitive searches. (John has posted about this in his blog http://obiee101.blogspot.com/2009/02/obiee-configuring-case-insensitive.html)

But if you don’t have access to database to do all these steps. Then you need to configure your filter behavior in OBIEE application itself.

To do this we need to alter the code in columnfilterprompt.js In following two  locations:

1.\OracleBI\web\app\res\b_mozilla\prompts

2.\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts

Steps to do:

  1. Take a backup of columnfilterprompt.js (if something goes wrong then you are unable to use this column filter)
  2. Open Columnfilterprompt.js in a editor and search for the function

function CFPRefresh(sViewID, sType, numPageValues, sDisplay, sFilter, sCallFunction, bNoRefresh)

{

——–

——–

——–

if(document.getElementById(sViewID + “MatchTable”))

{

//if (tForm.Constraint.value != “”)

{

var sValue = null;

switch (tForm.Match.value)

{

case “beginsWith”:

sValue = CFPSqlEscape(tForm.Constraint.value) + “%”;

break;

case “endsWith”:

sValue = “%” + CFPSqlEscape(tForm.Constraint.value);

break;

case “contains”:

sValue = “%” + CFPSqlEscape(tForm.Constraint.value) + “%”;

break;

}

if (sValue != null)

sWhere = aColumns[0] + ” LIKE ‘” + sValue + “‘”;

}

}

——

——

}

3. Now comment the bold content i.e sWhere = aColumns[0] + ” LIKE ‘” + sValue + “‘”; and add this code  in  if (sValue != null) loop.

Code:

sWhere = “UPPER(“+aColumns[0]+”)” + ” LIKE UPPER(‘” + sValue + “‘)”;

4. Now your code should look like this:

function CFPRefresh(sViewID, sType, numPageValues, sDisplay, sFilter, sCallFunction, bNoRefresh)

{

——–

——–

——–

if(document.getElementById(sViewID + “MatchTable”))

{

//if (tForm.Constraint.value != “”)

{

var sValue = null;

switch (tForm.Match.value)

{

case “beginsWith”:

sValue = CFPSqlEscape(tForm.Constraint.value) + “%”;

break;

case “endsWith”:

sValue = “%” + CFPSqlEscape(tForm.Constraint.value);

break;

case “contains”:

sValue = “%” + CFPSqlEscape(tForm.Constraint.value) + “%”;

break;

}

if (sValue != null)

//sWhere = aColumns[0] + ” LIKE ‘” + sValue + “‘”;

sWhere = “UPPER(“+aColumns[0]+”)” + ” LIKE UPPER(‘” + sValue + “‘)”;

}

}

—–

—–

—-

}

5. Now restart Oracle Java host service and Presentation service for better results restart OC4J also.

6. Now check the prompt with different case letters and observe that it returns values.The same code is useful in multiselect constarint choice also.

case-insenstive

Till next time…

Advertisements
 
13 Comments

Posted by on April 20, 2009 in OBI EE

 

13 responses to “Case Insensitive Search Using Dashboard Prompt

  1. F

    April 20, 2009 at 5:47 pm

    Thanks a bunch! just what I needeed.

     
  2. Girish

    June 3, 2009 at 12:55 pm

    Hello Shiva,

    I’ve tried the above suggested approach, but it is not working for me. I do not see any difference in the prompt search behavior. Is there anything else I need to do apart from the above steps?

    Thanks,
    Girish

     
  3. Shiva Molabanti

    June 6, 2009 at 6:48 pm

    Girish,
    This is enough to get it work.I have posted for the column prompt.It seems you are seeing in Multiselect of dashboard prompt.If it so u have to change the same ‘Swhere’ in Multiselectprompt.js file.I have done and it is working for me.

    Thanks
    Siva.

     
  4. Raj

    June 22, 2009 at 8:32 pm

    Hi Shiva,

    I made the changes you suggested in gfpmultiselect.js file in the C:\OracleBI\web\app\res\b_mozilla\prompts folder as follows;

    // this.sWhere = this.vColumns[0] + ” LIKE ‘” + sValue + “‘”;
    this.sWhere = this.“UPPER(”+vColumns[0]+”)” + ” LIKE UPPER(’” + sValue + “‘)”;

    but I could not locate the ‘analytics’ folder C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics

    Any thoughts on this would be appreciated.

    Its is not working with the change I did in one file alone.

    Thanks in advance,
    Raj

     
  5. Chris

    August 28, 2009 at 5:18 pm

    Does anything have to be differently if the web server and BI server are different machines? We are running IIS and have modified the js file (we could only find 1 file to modify). We have restarted the services several times and this is not working. I can get this work on my laptop that is running IIS, but my BI server and web server are on the same box.

    Thanks,
    C.

     
  6. Vivek Dabra

    January 21, 2010 at 6:18 am

    Hi,

    I have made the changes specified in both files gfpmultiselect.js file and columnfilterprompt.js file, in both locations \OracleBI\web\app\res\b_mozilla\prompts and \OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts. But it is still not working after Restarting the services.

    Any further help will be really appreciated.

    Regards,
    Vivek Dabra

     
  7. Bharath

    March 11, 2010 at 9:12 pm

    Hello Shiva,

    I tried to implement this but didn’t work. First I couldn’t find the path the second patch you mentioned on the server (\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts).

    I tried to find the Multiselectprompt.js file on the server but not successful.

    But I still made the changes you mentioned above on the files columnfilterprompt.js and gfpmultiselect.js files on the following location(\OracleBI\web\app\res\b_mozilla\prompts) to give a shot. But It didn’t worked.

    Am I missing something here? or I am looking at the wrong place? Please guide me…..

     
    • rhill

      November 11, 2010 at 12:57 am

      shiva,
      i was successful to implement a case insensitive search using your instructions on my local pc obiee installation. windows xp. thank you very much.

      however, the same changes were not successful when applied to the files on a linux server. the home directories were different, but the rest of the paths were the same.

      the columnfilterprompt.js file was present in both locations, as expected. i renamed the original files, but left them in their original directories, as i had done successfully in Windows.

      is there something different to do if the server is not Windows?
      thanks

       
  8. Anil

    August 15, 2011 at 6:38 pm

    will this work if I have Weblogic server?

     
  9. Malti

    March 16, 2012 at 4:02 pm

    Hi,

    Is this possible in 11g that the users type mixed case into a ‘contains all’ prompt and have that search against values that are stored in the DB as all upper case? What steps are required?

    Thanks
    Malti Khanna

     
  10. Armando

    October 9, 2012 at 12:52 pm

    Hello all,
    I have the same problem but in 11G because I’m trying to filter by the string without any apostrophe or strange character, I have a PL/SQL function which remove any strange character, could I call the function from the file .js in order to do the following?

    sWhere = “MyPLSQLFunction(“+aColumns[0]+”)” + ” LIKE MyPLSQLFunction(‘” + sValue + “‘)”;

    Thanks and best regards

     
  11. Kirthana Lakshmanan

    January 24, 2014 at 10:05 am

    Guys,
    Case insesitivity can be achieved by

    1.CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQSConfig fileON OBIEE 10G is set to OFF

    2. In RPD, the Physical layer, the table which host the column which is going to be used as a prompt should be set to Cacheable

    General->Cacheable check it

    Restart BI services

     

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

 
%d bloggers like this: