logo

9 февр. 2010 г.

BIEE: использование поиска в фильтрах без учета регистра

Часто встречаю требования Заказчиков о необходимости поиска в фильтрах, окнах мульти-выбора без учета регистра.


Согласитесь, разумное требование.
Почему я, являясь бизнес пользователем BI, должен помнить в точности имя некоего контрагента?
В стандартном функционале BI Answers/BI Dashboards нет возможности выбрать тип поиска без учета регистра.

Как вариант, вы можете заставить ваших пользователей применять функцию UPPER к столбцу данных и в последующем искать уже по строке в верхнем регистре. Но можно сделать иначе.

Все, что нам потребуется – это небольшие изменения javascript-файлов и файлов XML-сообщений BIEE.

Напоминаю, что необходимо делать резервные копии всех изменяемых стандартных файлов. И при любом патче на Oracle BIEE тщательно проверять, не присутствует ли в нем измененная версия вашего файла.

Нам потребуется создать новый XML-файл сообщений в каталоге {OracleBI}\web\msgdb\customMessages
Назовите файл по вашему усмотрению (желательно осмысленно =) ) и поместите в него следующий код:

<?xml version="1.0" encoding="utf-8"?>
<!--DO NOT MODIFY THIS FILE. THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages. Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="StandardViewTemplates" table="Messages">

<WebMessage name="kuiColumnFilterEditor" export="true" translate="no"><HTML>
<table class="ColumnFilterOuterTable"><tr><td class="FilterValuesSideCell"><form id="@{eid}FormAdvancedSQL" action="none" method="post"><input type="hidden" name="FilterXML"/><input type="hidden" name="SubjectArea"/></form><form id="@{eid}Form" action="none" method="post" formSubmit="return XUIPanel.onSubmit(&#39;@{eid}&#39;, event);"><input type="hidden" name="ReportXML"/><input type="hidden" name="Formula"/><table class="XUIPromptTable" cellspacing="0" cellpadding="0"><tr><td class="XUIPromptCaption"><sawm:messageRef name="kmsgFilterEditorColumn"/></td><td class="XUIPromptStaticText" valign="middle" dir="ltr"><span id="idColumnFilterColumnName"/><span style="display:none;" id="idColumnFilterColumnTimeZone">&amp;nbsp;
<sawm:messageRef name="kuiTimeZoneInfoPopup"/></span></td></tr>
<sawm:if name="bShowAggByLink"><tr><td colspan="2"><table cellspacing="0" cellpadding="0" id="@{eid}AggByTable"/></td></tr><tr><td colspan="2"><table id="@{eid}AggByTableLink" class="XUIPromptTable"><tr><td id="@{eid}AggregateByCell" class="XUIPromptLink"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgFilterEditorAggByLink"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgFilterEditorAggByTitle"/></sawm:setParam><sawm:setParam name="buttonScript">ColumnFilterEditor.onAggregateBy(&#39;<sawm:param name="eid"/>&#39;, event)</sawm:setParam></sawm:messageRef></td></tr></table></td></tr></sawm:if><tr><td id="@{eid}TopOperatorCaptionCell" class="XUIPromptCaption" nowrap="true"><sawm:messageRef name="kmsgFilterEditorOperator"/></td><td class="XUIPromptEntry" colspan="1"><select name="Operator" onchange="ColumnFilterEditor.onChangeOperator(&#39;@{eid}&#39;)"/></td></tr></table><div class="FilterValuesDiv"><table id="idColumnFilterValuesTable" class="XUIPromptTable" cellspacing="0" cellpadding="0">
<tr style="visibility:hidden"><td class="XUIPromptCaption" nowrap="true"><sawm:messageRef name="kmsgFilterEditorOperator"/></td><td class="XUIPromptEntry" colspan="2"/></tr></table></div><table class="XUIPromptTable"><tr><td id="@{eid}AddCell" class="XUIPromptLink"><sawm:messageRef name="kuiAnswersBarButtonMenu"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgFilterEditorAdd"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgFilterEditorAdd"/></sawm:setParam><sawm:setParam name="menuID"><sawm:param name="eid"/>AddMenu</sawm:setParam></sawm:messageRef></td><td id="@{eid}ClearCell" class="XUIPromptLink"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgFCDClearValues"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgFCDClearValues"/></sawm:setParam><sawm:setParam name="buttonScript">ColumnFilterEditor.onClearValues(&#39;<sawm:param name="eid"/>&#39;)</sawm:setParam></sawm:messageRef></td></tr></table></form></td><td id="@{eid}ChoicesSide" class="FilterChoicesSideCell"><form id="@{eid}ChoicesFormId" name="@{eid}ChoicesForm" action="none" method="post" formSubmit="return XUIPanel.onSubmit(&#39;@{eid}&#39;, event);"><table id="@{eid}MatchTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><sawm:messageRef name="kmsgFCCConstrainChoicesMatch"/></td><td class="XUIPromptEntry"><select name="Match"><option value="like_upper"><sawm:messageRef name="kmsgFCCConstrainChoicesLikeUpper"/></option><option value="beginsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesBeginsWith"/></option><option value="endsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesEndsWith"/></option><option value="contains"><sawm:messageRef name="kmsgFCCConstrainChoicesContains"/></option></select></td><td class="XUIPromptEntry"><input type="text" name="Constraint" size="15"/></td></tr></table><table id="@{eid}BetweenTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><b><sawm:messageRef name="kmsgFilterEditorShowDatesBetween"/></b></td><td class="XUIPromptEntry"><input type="text" maxLength="12" size="9" name="BetweenA"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="eid"/>BetweenA</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;<sawm:param name="eid"/>ChoicesForm&#39;].BetweenA</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;<sawm:param name="eid"/>ChoicesForm&#39;].BetweenA</sawm:setParam></sawm:messageRef>&amp;nbsp;<sawm:messageRef name="kmsgFCCBetweenDatesSeparator"/>&amp;nbsp;
<input type="text" maxLength="12" size="9" name="BetweenB"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="eid"/>BetweenB</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;<sawm:param name="eid"/>ChoicesForm&#39;].BetweenB</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;<sawm:param name="eid"/>ChoicesForm&#39;].BetweenB</sawm:setParam></sawm:messageRef></td></tr></table></form><table class="XUIPromptTable" width="100%"><tr><td class="XUIPromptLink" align="right"><a href="javascript:void(null)" onclick="ColumnFilterEditor.onAllChoices(&#39;@{eid}&#39;); return false;"><sawm:messageRef name="kmsgFCTAllChoices"/></a>&amp;nbsp;&amp;nbsp;<a href="javascript:void(null)" onclick="ColumnFilterEditor.onLimitedChoices(&#39;@{eid}&#39;); return false;"><sawm:messageRef name="kmsgFCTLimitedChoices"/></a></td></tr></table>
<div id="@{eid}ChoicesDiv" height="200px" class="NQWValueTable"/></td></tr></table>
<div id="@{eid}AddMenu" class="NQWMenu" menuInit="ColumnFilterEditor.initializeAddMenu" onmouseover="NQWMenuMouseOver(event)" onmouseout="NQWMenuMouseOut(event)"><sawm:messageRef name="kuiMenuShadowBegin"/><a class="NQWMenuItem" name="addvalue" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddExpr(&#39;@{eid}&#39;,&#39;sawx:untypedLiteral&#39;);return false;"><sawm:messageRef name="kmsgFilterEditorAddValue"/></a><a class="NQWMenuItem" name="addexpr" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddExpr(&#39;@{eid}&#39;,&#39;sawx:sqlExpression&#39;);return false;"><sawm:messageRef name="kmsgFilterEditorAddSQLExpr"/></a><a class="NQWMenuItem" name="addvar" href="javascript:void(null)" onclick="return false;" onmouseover="saw.menuItemMouseOver(event,&#39;@{eid}VariableMenu&#39;);"><span class="NQWMenuItemText"><sawm:messageRef name="kmsgFilterEditorVariable"/></span><span class="NQWMenuItemArrow"><img src="fmap:common/menurtarrow.gif" border="0"/></span></a><sawm:if name="isListFormat"><a class="NQWMenuItem" name="addwebexpr" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddWebExpr(&#39;@{eid}&#39;);return false;"><sawm:messageRef name="kmsgMktgWebExpression"/></a></sawm:if><sawm:messageRef name="kuiMenuShadowEnd"/></div>
<div id="@{eid}VariableMenu" class="NQWMenu" subMenuInit="ColumnFilterEditor.initializeVariableMenu" onmouseover="NQWMenuMouseOver(event)" onmouseout="NQWMenuMouseOut(event)"><sawm:messageRef name="kuiMenuShadowBegin"/><a class="NQWMenuItem" name="addsessionvar" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddExpr(&#39;@{eid}&#39;,&#39;sawx:variable&#39;,&#39;session&#39;);return false;"><sawm:messageRef name="kmsgFilterEditorVariableServerSession"/></a><a class="NQWMenuItem" name="addrepositoryvar" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddExpr(&#39;@{eid}&#39;,&#39;sawx:variable&#39;,&#39;repository&#39;);return false;"><sawm:messageRef name="kmsgFilterEditorVariableServerRepository"/></a><a class="NQWMenuItem" name="addwebvar" href="javascript:void(null)" onclick="ColumnFilterEditor.onAddExpr(&#39;@{eid}&#39;,&#39;sawx:eval&#39;);return false;"><sawm:messageRef name="kmsgFilterEditorVariableWeb"/></a><sawm:messageRef name="kuiMenuShadowEnd"/></div>
</HTML></WebMessage>


<WebMessage name="kmsgGFPMultiSelectSearchBody" translate="no"><HTML><table cellspacing="0" cellpadding="0" onclick="return GFPMultiSelect.ClickPanel(&#39;@{eid}&#39;)"><tr><td class="MultiSelectCaption"><sawm:messageRef name="kmsgFunctionSelectorSelected"/></td><td/><td><form id="@{eid}SearchForm" name="@{eid}SearchForm" action="none" method="post" formSubmit="return XUIPanel.onSubmit(&#39;@{eid}&#39;, event);"><table id="@{eid}MatchTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><sawm:messageRef name="kmsgFCCConstrainChoicesMatch"/></td><td class="XUIPromptEntry"><select name="Match"><option value="like_upper"><sawm:messageRef name="kmsgFCCConstrainChoicesLikeUpper"/></option><option value="beginsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesBeginsWith"/></option><option value="endsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesEndsWith"/></option><option value="contains"><sawm:messageRef name="kmsgFCCConstrainChoicesContains"/></option><option value="like"><sawm:messageRef name="kmsgFilterUIOperatorISLIKE"/></option></select></td><td class="XUIPromptEntry"><input type="text" name="Constraint" size="15"/></td><td class="MultiSelectButtonCell"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Go(&#39;<sawm:param name="eid"/>&#39;)</sawm:setParam></sawm:messageRef></td></tr></table><table id="@{eid}DateTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><b><sawm:messageRef name="kmsgFilterEditorShowDatesBetween"/></b></td><td class="XUIPromptEntry"><input type="text" maxLength="12" size="9" name="BetweenA"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="eid"/>BetweenA</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;<sawm:param name="eid"/>SearchForm&#39;].BetweenA</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;<sawm:param name="eid"/>SearchForm&#39;].BetweenA</sawm:setParam></sawm:messageRef>&amp;nbsp;<sawm:messageRef name="kmsgFCCBetweenDatesSeparator"/>&amp;nbsp;
<input type="text" maxLength="12" size="9" name="BetweenB"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="eid"/>BetweenB</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;<sawm:param name="eid"/>SearchForm&#39;].BetweenB</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;<sawm:param name="eid"/>SearchForm&#39;].BetweenB</sawm:setParam></sawm:messageRef></td><td class="MultiSelectButtonCell"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Go(&#39;<sawm:param name="eid"/>&#39;)</sawm:setParam></sawm:messageRef></td></tr></table><table id="@{eid}NumericTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><b><sawm:messageRef name="kmsgFilterEditorShowValuesBetween"/></b></td><td class="XUIPromptEntry"><input type="text" size="7" name="BetweenNA"/>&amp;nbsp;<sawm:messageRef name="kmsgFCCBetweenDatesSeparator"/>&amp;nbsp;
<input type="text" size="7" name="BetweenNB"/></td><td class="MultiSelectButtonCell"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPGo"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Go(&#39;<sawm:param name="eid"/>&#39;)</sawm:setParam></sawm:messageRef></td></tr></table></form></td></tr><tr><td><div id="@{eid}Selections" class="ValueList" style="width:200px; height:150px" onclick="return GFPMultiSelect.ClickSelection(event, &#39;@{eid}&#39;, false)" ondblclick="return GFPMultiSelect.DoubleClickSelection(event, &#39;@{eid}&#39;, false)"/></td><td><table><tr><td class="ShuttleButtons"><sawm:messageRef name="kuiAnswersBarIdButton"><sawm:setParam name="buttonId"><sawm:param name="eid"/>SelectAll</sawm:setParam><sawm:setParam name="buttonText">&amp;lt;&amp;lt;</sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPMultiSelectMoveAll"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.SelectAll(&#39;<sawm:param name="eid" sawm:use="javaScriptString"/>&#39;, true)</sawm:setParam></sawm:messageRef><p/><sawm:messageRef name="kuiAnswersBarIdButton"><sawm:setParam name="buttonId"><sawm:param name="eid"/>Select</sawm:setParam><sawm:setParam name="buttonText">&amp;nbsp;&amp;lt;&amp;nbsp;</sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPMultiSelectMove"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Select(&#39;<sawm:param name="eid" sawm:use="javaScriptString"/>&#39;, true)</sawm:setParam></sawm:messageRef><p/><sawm:messageRef name="kuiAnswersBarIdButton"><sawm:setParam name="buttonId"><sawm:param name="eid"/>Deselect</sawm:setParam><sawm:setParam name="buttonText">&amp;nbsp;&amp;gt;&amp;nbsp;</sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPMultiSelectRemove"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Select(&#39;<sawm:param name="eid" sawm:use="javaScriptString"/>&#39;, false)</sawm:setParam></sawm:messageRef><p/><sawm:messageRef name="kuiAnswersBarIdButton"><sawm:setParam name="buttonId"><sawm:param name="eid"/>DeselectAll</sawm:setParam><sawm:setParam name="buttonText">&amp;gt;&amp;gt;</sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPMultiSelectRemoveAll"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.SelectAll(&#39;<sawm:param name="eid" sawm:use="javaScriptString"/>&#39;, false)</sawm:setParam></sawm:messageRef></td></tr></table></td><td style="text-align:center"><div id="@{eid}Choices" class="ValueList" style="height:150px;text-align:left" onclick="return GFPMultiSelect.ClickSelection(event, &#39;@{eid}&#39;, true)" ondblclick="return GFPMultiSelect.DoubleClickSelection(event, &#39;@{eid}&#39;, true)"/></td></tr><tr><td class="MultiSelectButtonCell"><sawm:messageRef name="kuiAnswersBarButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgFilterDisplayEditPredicate"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgFilterDisplayEditPredicate"/></sawm:setParam><sawm:setParam name="buttonScript">GFPMultiSelect.Edit(event, &#39;<sawm:param name="eid"/>&#39;)</sawm:setParam></sawm:messageRef></td><td/><td class="XUIPromptCaption"><table><tr><td><sawm:messageRef name="kmsgGFPMultiSelectTotalChoices"/></td><td id="@{eid}Total"/><td class="MultiSelectButtonCell"><sawm:messageRef name="kuiAnswersBarIdButton"><sawm:setParam name="buttonText"><sawm:messageRef name="kmsgGFPMultiSelectMore"/></sawm:setParam><sawm:setParam name="buttonTip"><sawm:messageRef name="kmsgGFPMultiSelectMore"/></sawm:setParam><sawm:setParam name="buttonScript">void(null)</sawm:setParam><sawm:setParam name="buttonId"><sawm:param name="eid"/>More</sawm:setParam></sawm:messageRef></td></tr></table></td></tr></table></HTML></WebMessage>


<WebMessage name="kmsgCFPChoicesBegin"><HTML>&lt;td class=&quot;CFPChoicesCell&quot; valign=&quot;top&quot;&gt;
<sawm:if name="canConstrain"><sawm:choose><sawm:when name="category" op="equals" value="other"><table id="@{viewID}MatchTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><sawm:messageRef name="kmsgFCCConstrainChoicesMatch"/></td><td class="XUIPromptEntry"><select name="Match"><option value="like_upper"><sawm:messageRef name="kmsgFCCConstrainChoicesLikeUpper"/></option><option value="beginsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesBeginsWith"/></option><option value="endsWith"><sawm:messageRef name="kmsgFCCConstrainChoicesEndsWith"/></option><option value="contains"><sawm:messageRef name="kmsgFCCConstrainChoicesContains"/></option></select></td><td class="XUIPromptEntry"><input type="text" name="Constraint" size="15" onkeypress="return CFPKeyPress(&#39;@{viewID}[javaScriptString]&#39;, &#39;@{selectType}[javaScriptString]&#39;, @{numPageValues}, &#39;@{displayType}[javaScriptString]&#39;, &#39;@{filterXml}[javaScriptString]&#39;, &#39;@{callFunction}[javaScriptString]&#39;, event);"/></td></tr></table></sawm:when><sawm:otherwise><table id="@{viewID}BetweenTable" class="XUIPromptTable" cellspacing="0"><tr><td class="XUIPromptCaption"><b><sawm:messageRef name="kmsgFilterEditorShowDatesBetween"/></b></td><td class="XUIPromptEntry"><input type="text" maxLength="12" size="9" name="BetweenA"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="viewID"/>BetweenA</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;id<sawm:param name="viewID"/>CFPForm&#39;].elements.BetweenA</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;id<sawm:param name="viewID"/>CFPForm&#39;].elements.BetweenA</sawm:setParam></sawm:messageRef>&amp;nbsp;<sawm:messageRef name="kmsgFCCBetweenDatesSeparator"/>&amp;nbsp;
<input type="text" maxLength="12" size="9" name="BetweenB"/><sawm:messageRef name="kuiCalendarWidgetLink"><sawm:setParam name="id"><sawm:param name="viewID"/>BetweenB</sawm:setParam><sawm:setParam name="textDateField">document.forms[&#39;id<sawm:param name="viewID"/>CFPForm&#39;].elements.BetweenB</sawm:setParam><sawm:setParam name="basedOn">document.forms[&#39;id<sawm:param name="viewID"/>CFPForm&#39;].elements.BetweenB</sawm:setParam></sawm:messageRef></td></tr></table></sawm:otherwise></sawm:choose>
<table class="XUIPromptTable" width="100%"><tr><td class="XUIPromptLink" align="right"><a href="javascript:void(null)" onclick="CFPRefresh(&#39;@{viewID}[javaScriptString]&#39;, &#39;@{selectType}[javaScriptString]&#39;, @{numPageValues}, &#39;@{displayType}[javaScriptString]&#39;, &#39;@{filterXml}[javaScriptString]&#39;, &#39;@{callFunction}[javaScriptString]&#39;); return false;"><sawm:messageRef name="kmsgFCTRefresh"/></a></td></tr></table></sawm:if></HTML></WebMessage>

</WebMessageTable></WebMessageTables>

Тем самым мы добавляем новый тип поиска – без учета регистра.

Для мульти-язычной системы желательно назвать тип поиска соответствующе. Создадим языко-зависимые названия для RU и EN локалей.

Создадим в папке {OracleBI}\web\msgdb\l_en\customMessages файл XX_filtermessages.xml с содержимым:

<?xml version="1.0" encoding="utf-8"?>
<!--DO NOT MODIFY THIS FILE. THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages. Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable system="ColumnFilterPrompt" table="UIMessages">

<WebMessage name="kmsgFCCConstrainChoicesLikeUpper"><!-- drop down option --><TEXT>contain (no letter case)</TEXT></WebMessage>
</WebMessageTable></WebMessageTables>


А также в папке {OracleBI}\web\msgdb\l_ru \customMessages файл XX_filtermessages.xml с содержимым:

<?xml version="1.0" encoding="utf-8"?>
<!--DO NOT MODIFY THIS FILE. THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages. Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable system="ColumnFilterPrompt" table="UIMessages">

<WebMessage name="kmsgFCCConstrainChoicesLikeUpper"><!-- drop down option --><TEXT>содержит (без учета регистра)</TEXT></WebMessage>
</WebMessageTable></WebMessageTables>


Последний штрих – изменение javascript-файлов.
Найдите 2 синхронных каталога: {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla и {OracleBI}\web\app\res\b_mozilla
(приведен пример для случая установки BIEE в обычном режиме, для работы на OC4J)

Произведите изменения в файлах
.\answers\columnfiltereditor.js
.\prompts\gfpmultiselect.js
.\prompts\columnfilterprompt.js

В columnfiltereditor.js внесите изменения в функцию displayChoices
(изменения кода выделены комментариями вида
//JackCarver start
//JackCarver end
)


ColumnFilterEditor.prototype.displayChoices = function(bLimited)
{
var aColumns = new Array;
aColumns[0] = this.form.Formula.value;

// Interpret match or between entries
var sWhere = null;

if(this.matchTable.style.display == '')
{
if (this.choicesForm.Constraint.value != "")
{
var sValue = null;
switch (this.choicesForm.Match.value)
{
case "beginsWith":
sValue = this.sqlEscape(this.choicesForm.Constraint.value) + "%";
break;
case "endsWith":
sValue = "%" + this.sqlEscape(this.choicesForm.Constraint.value);
break;
case "contains":
sValue = "%" + this.sqlEscape(this.choicesForm.Constraint.value) + "%";
break;
//JackCarver start
case "like_upper":
sValue = "%" + this.sqlEscape(this.choicesForm.Constraint.value) + "%";
break;
//JackCarver end
}

if (sValue != null)
//JackCarver start
//original:
//sWhere = aColumns[0] + " LIKE '" + sValue + "'";

if (this.choicesForm.Match.value == "like_upper")
sWhere = "UPPER(" + aColumns[0] + ")" + " LIKE '" + sValue.toString().toUpperCase() + "'";
else
sWhere = aColumns[0] + " LIKE '" + sValue + "'";

//JackCarver end
}
}
else if(this.betweenTable.style.display == '')
{
var tA = null;
var tB = null;

switch(this.primaryType)
{
case 'date':
tA = tDTP.parse(this.choicesForm.BetweenA.value, 2 | 8 | 16 | 32);
tB = tDTP.parse(this.choicesForm.BetweenB.value, 2 | 8 | 16 | 32);
break;
case 'time':
tA = tDTP.parse(this.choicesForm.BetweenA.value, 4);
tB = tDTP.parse(this.choicesForm.BetweenB.value, 4);
break;
case 'timeStamp':
// convert
tA = tDTP.parse(this.choicesForm.BetweenA.value, 1 | 2 | 4 | 8 | 16 | 32);
tB = tDTP.parse(this.choicesForm.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(tA != null)
{
var sA = DateTimeParser.buildDateTimeClause('>', this.form.Formula.value, this.primaryType, tA);

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

if(tB != null)
{
var sA = DateTimeParser.buildDateTimeClause('<', this.form.Formula.value, this.primaryType, tB);

if(sA != null)
sWhere = sWhere == null ? sA : (sWhere + ' AND ' + sA);

}

}

if(bLimited)
{
var tExistingFilter = this.criteria.selectSingleNode('saw:filter');

var tOuterExpr = tExistingFilter == null ? null : tExistingFilter.selectSingleNode('sawx:expr');

if(tExistingFilter && tOuterExpr)
{
if(this.originalExpr)
{

var aPosition = XUICalculateXmlPosition(tExistingFilter, this.originalExpr);

tExistingFilter = tExistingFilter.cloneNode(true);

tOuterExpr = tExistingFilter.selectSingleNode('sawx:expr');

var tEditedExpr = XUIApplyXmlPosition(tExistingFilter, aPosition);

if(tEditedExpr != null)
{
var tSQLFilter = XUICreateElement(saw.xml.kSawxNamespace, 'expr');

XUISetXsiType(tSQLFilter, saw.xml.kSawxNamespace, 'sql');

var bAnd = true;

if(tEditedExpr.parentNode.getAttribute('op') == 'or')
bAnd = false;

XUISetElementText(tSQLFilter, bAnd ? '1 = 1' : '1 = 0');

tEditedExpr.parentNode.replaceChild(tSQLFilter, tEditedExpr);
}
}

if(sWhere != null)
{
var tNewFilter = XUICreateElement(saw.xml.kSawxNamespace, 'expr');

XUISetXsiType(tNewFilter, saw.xml.kSawxNamespace, 'logical');

tNewFilter.setAttribute('op','and');

tNewFilter.appendChild(tOuterExpr.cloneNode(true));

var tSQLFilter = XUICreateElement(saw.xml.kSawxNamespace, 'expr');

XUISetXsiType(tSQLFilter, saw.xml.kSawxNamespace, 'sql');

XUISetElementText(tSQLFilter, sWhere);

tNewFilter.appendChild(tSQLFilter);

sWhere = tNewFilter;
}
else
sWhere = tOuterExpr;

}
}

this.choicesDiv.innerHTML = '';
VTDisplayValues(this.choicesDiv, aColumns, this.criteria.getAttribute('subjectArea'), 'kmsgColumnFilterValueTableChoice', sWhere, this.sDisplayTZ, this.sDataTZOffset);
}



В gfpmultiselect.js внесите изменения в функцию search


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

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;
//JackCarver start
case "like_upper":
sValue = "%" + GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value) + "%";
break;
//JackCarver end

}

if (sValue != null)
//JackCarver start
//original:
//this.sWhere = this.vColumns[0] + " LIKE '" + sValue + "'";

if (this.searchForm.Match.value == "like_upper")
this.sWhere = "UPPER(" + this.vColumns[0] + ")" + " LIKE '" + sValue.toString().toUpperCase() + "'";
else
this.sWhere = this.vColumns[0] + " LIKE '" + sValue + "'";

//JackCarver end

}
}
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 ' + 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.vColumns[0] + ">=" + tA;
}

if(tB.length > 0)
{
tB = parseFloat(tB, 10);
var clause = this.vColumns[0] + "<=" + tB;
this.sWhere = 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";

var sWhere = this.sWhere;
if (this.sOldWhere && this.sOldWhere != "")
{
if (sWhere && sWhere != "")
sWhere = " ( " + this.sOldWhere + " ) AND ( " + sWhere + " ) ";
else
sWhere = this.sOldWhere;
}

VTDisplayValues(this.choicesDiv, this.vColumns, this.subjectArea, 'kmsgGFPMultiSelectSearchValueTableRow', sWhere, this.timeZone, this.timeZoneOffset,
'scroll', this.sId, null, null, null, null, null, "idGFPMultiSelect" + this.sId);
}


В columnfilterprompt.js внесите изменения в функцию search

function CFPRefresh(sViewID, sType, numPageValues, sDisplay, sFilter, sCallFunction, bNoRefresh)
{
var tForm = document.forms["id" + sViewID+"CFPForm"];

var aColumns = new Array;
aColumns[0] = tForm.column.value;

// Interpret match or between entries
var sWhere = null;

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;
//JackCarver start
case "like_upper":
sValue = "%" + this.sqlEscape(this.choicesForm.Constraint.value) + "%";
break;
//JackCarver end
}

if (sValue != null)
//JackCarver start
//original:
//sWhere = aColumns[0] + " LIKE '" + sValue + "'";

if (this.choicesForm.Match.value == "like_upper")
sWhere = "UPPER(" + aColumns[0] + ")" + " LIKE '" + sValue.toString().toUpperCase() + "'";
else
sWhere = aColumns[0] + " LIKE '" + sValue + "'";
//JackCarver end

}
}
else if(document.getElementById(sViewID + "BetweenTable"))
{
var tDTP = new DateTimeParser();
tDTP.sDateOrder = 'mdy';

var tA = null;
var tB = null;

switch(tForm.primaryType.value)
{
case 'date':
tA = tDTP.parse(tForm.BetweenA.value, 2 | 8 | 16 | 32);
tB = tDTP.parse(tForm.BetweenB.value, 2 | 8 | 16 | 32);
break;
case 'time':
tA = tDTP.parse(tForm.BetweenA.value, 4);
tB = tDTP.parse(tForm.BetweenB.value, 4);
break;
case 'timeStamp':
tA = tDTP.parse(tForm.BetweenA.value, 1 | 2 | 4 | 8 | 16 | 32);
tB = tDTP.parse(tForm.BetweenB.value, 1 | 2 | 4 | 16 | 32);
//convert to data timezone
if (tForm.displayToDataOffset.value)
{
var nOffset = parseInt(tForm.displayToDataOffset.value);
tA.adjustTimeZoneOffset(nOffset);
tB.adjustTimeZoneOffset(nOffset);
}
break;
}

if(tA != null)
{
var sA = CFPBuildDateTimeClause('>', tForm.column.value, tForm.primaryType.value, tA);

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

if(tB != null)
{
var sA = CFPBuildDateTimeClause('<', tForm.column.value, tForm.primaryType.value, tB);

if(sA != null)
sWhere = sWhere == null ? sA : (sWhere + ' AND ' + sA);
}
}

if(sDisplay == "limited")
{
var tExistingFilter = saw.getXmlIsland("idExisting");
if (tExistingFilter.loadXML(sFilter))
{
var tOuterExpr = tExistingFilter == null ? null : tExistingFilter.selectSingleNode('saw:filter/sawx:expr');
if(tExistingFilter && tOuterExpr)
{
if(sWhere != null)
{
var tNewFilter = XUICreateElement(saw.xml.kSawxNamespace, 'expr');

XUISetXsiType(tNewFilter, saw.xml.kSawxNamespace, 'logical');

tNewFilter.setAttribute('op','and');

tNewFilter.appendChild(tOuterExpr.cloneNode(true));

var tSQLFilter = XUICreateElement(saw.xml.kSawxNamespace, 'expr');

XUISetXsiType(tSQLFilter, saw.xml.kSawxNamespace, 'sql');

XUISetElementText(tSQLFilter, sWhere);

tNewFilter.appendChild(tSQLFilter);

sWhere = tNewFilter;
}
else
sWhere = tOuterExpr;
}
}
}
var tChoicesDiv = document.getElementById(sViewID + "idCFPDiv");
//tChoicesDiv.innerHTML = '';

var displayTZ = null;
if (tForm.displayTimeZone)
displayTZ = tForm.displayTimeZone.value;
var dataTZOffset = 0;
if (tForm.dataTimeZoneOffset)
dataTZOffset = tForm.dataTimeZoneOffset.value;

VTDisplayValues(tChoicesDiv, aColumns, tForm.subjectArea.value, "", sWhere,
displayTZ, dataTZOffset,
sType, sViewID, numPageValues, sDisplay, "", sCallFunction, !bNoRefresh);
}


Перегружаем BI Presentation Services. Очищаем Кеш браузера. И получаем возможность поиска без учета регистра.


1 комментарий:

  1. Спасибо за решение!!! Это именно, то что нам нужно.
    Важное замечание: убирайте из XML символы [Tab],[CR],[LF]

    ОтветитьУдалить