怎样让新建的Answer必须对某个字段进行过滤？

来源：互联网发布：More 查看sql文件乱码编辑：程序博客网时间：2024/05/17 04:41

考虑如下的需求，当用户在使用Answer做即席查询的时候，怎么让他必须对某一个字段加过滤条件呢？
比如时间，如果不加则不让该请求执行，并同时给出一个提示信息。

下面我就以10G官方的SH demo为例，阐述实现步骤：

11G实现方法类似，具体请参见文末链接。

BIEE本身是提供了该功能的，我们需要做的就是使用javascript去实现一个validateAnalysisCriteria方法，并把我们的验证逻辑
添加进去即可！

默认情况下，该方法始终返回true，即通过验证(执行请求)。如果该方法返回false或者一条消息，则不执行该请求，同时在弹出的窗口中显示返回的消息！

1、新建一个QueryBlock.xml文件，将其放到 {OracleBI}\web\msgdb\custommessages目录(如果没有该目录请手工创建)下

<?xml version=”1.0″ encoding=”utf-8″?>
<WebMessageTables xmlns:sawm=”com.siebel.analytics.web.messageSystem”>
<WebMessageTable system=”QueryBlocking” table=”Messages”>
<WebMessage name=”kuiCriteriaBlockingScript” translate=”no”>
<HTML>
<script language=”javascript” src=”fmap:myblocking.js”/>
</HTML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

复制代码

注意：放在customMessages下意味着该文件是与语言无关的，如果想和具体语言相关，请将该文件放到{OracleBI}\web\msgdb\目录对应的语言文件夹下，如中文：l_zh

2、创建myblocking.js 代码如下：

function validateAnalysisCriteria(analysisXml)
{
// Create the helper object
var tValidator = new CriteriaValidator(analysisXml);
// Validation Logic
if (tValidator.getSubjectArea() != "SH") // 只对SH主题域进行验证
return true;
if(!tValidator.tableExists("Calendar"))//只有当引用了Calendar表中的任意字段才进行验证
return true;
//如果引用了Calendar表的任意字段，则必须对Calendar Month Desc字段做过滤
if (!tValidator.filterExists("Calendar","Calendar Month Desc"))
return "Please filter the Calendar Month Desc.";
//获取过滤值的个数
var n = tValidator.filterCount("Calendar","Calendar Month Desc");
//只能指定一个值
if (n!=1)
return "Please select 1 and only one Month";
return true;
}

复制代码

将该脚本拷贝至{OracleBI}/web/app/res/b_mozilla 和{OracleBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res/b_mozilla
两个目录下(注，官方文档中关于此目录描述有误)

重启OBPIS和OC4J。

下面来测试一下吧！

Case 1 ,不选任何时间维上的字段，比如只选Prod Category，同时不设置过滤器
结果：正常执行

Case 2,选时间维上的任意字段，同时不设置过滤器
结果：提示“Please filter the Calendar Month Desc”
如下图所示：

Case 3：在Case 2的基础上，添加Calendar Month Desc过滤器，同时指定两个值
结果：提示“Please select 1 and only one Month ”
如下图所示：

Case4：在Case 3的基础上，将过滤值只指定为一个。
结果：执行成功

附相关函数：18.5.4 Validation Helper Functions

Validation Helper Function

Description

CriteriaValidator.getSubjectArea()

Returns the name of the subject area referenced by the analysis. It generally is used in a switch statement within the function before doing other validation. If the analysis is a set-based criteria, then it returns null.

CriteriaValidator.tableExists(sTable)

Returns True if the specified folder (table) has been added to the analysis by the content designer, and False if the folder was not added.

CriteriaValidator.columnExists(sTable, sColumn)

Returns True if the specified column has been added to the analysis by the content designer, and False if the column was not added.

CriteriaValidator.dependentColumnExists(sCheckTable, sCheckColumn, sDependentTable, sDependentColumn)

Checks to ensure that the dependentColumn exists if the checkColumn is present. It returns True if either the checkColumn is not present, or the checkColumn and the dependent column are present. If checkColumn and dependentColumn are null, then the folders are validated. If any column from checkTable is present, then a column from dependentTable must be present.

CriteriaValidator.filterExists(sFilterTable, sFilterColumn)

Returns True if a filter exists on the specified column, and False if no filter is present.

CriteriaValidator.dependentFilterExists(sCheckTable, sCheckColumn, sFilterTable, sFilterColumn)

Checks to ensure that the dependentFilter exists if the checkColumn is present in the projection list. It returns True if either the checkColumn is not present, or the checkColumn and the dependent filter are present.

CriteriaValidator.filterCount(sFilterTable, sFilterColumn)

Returns the number of filter values that are specified for the given logical column. If the filter value is "equals," "null," "notNull," or "in," then it returns the number of values chosen. If the column is not used in a filter, then it returns zero. If the column is prompted with no default, then it returns -1. For all other filter operators (such as "greater than," "begins with," and so on) it returns 999, because the number of values cannot be determined.

参考资料：
10G: Presentation Services Administration Guide 之Blocking Requests in Answers on page 56
11G：System Administrator's Guide 之 18.5.2 Blocking Analyses Based on Criteria