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 bloghttp://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:
- Take a backup of columnfilterprompt.js (if something goes wrong then you are unable to use this column filter)
- 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.
Posts
