Validate and Block Queries in Analyses

Store JavaScript Files

This section explains how to use JavaScript to check conditions and validate analyses.

You write your own JavaScript programs for performing these tasks and other similar ones. Oracle Analytics Server doesn't install any JavaScript programs. As you write JavaScript programs, you can store them in the singleton data directory (SDD), which you first deploy as a virtual directory:

SDD/components/OBIPS/analyticsRes

Where SDD is the Singleton Data Directory for example, DOMAIN_HOME/bidata.

See Set Up Shared Files and Directories.

To place JavaScript programs in a directory other than this one, then you can do so, if you specify the full path name in the code that calls the program. For example, you can use code such as the following:

<script type="text/javascript" src="http://example/mydir/myblocking.js" />

Block Analyses Based on Criteria

When a user attempts to execute an analysis that your code blocks, you can display an error message, and the analysis isn't executed.

The answerstemplates.xml file includes a message named kuiCriteriaBlockingScript that can be overridden to either define or include JavaScript that defines a validateAnalysisCriteria function. By default, this message contains a function that always returns true.

Answers calls your validateAnalysisCriteria function when the user tries to execute the analysis. The function can return true if the analysis isn't blocked, or false, or a message if the analysis is blocked. If a message or a value other than false is returned, then the message is displayed in a popup window. In either case, the query is blocked.

The following code example shows the blocking of a query. First, place the following XML code in the answerstemplates.xml file.

<?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 type="text/javascript" src="fmap:myblocking.js" />
      </HTML>
   </WebMessage>
   </WebMessageTable>
</WebMessageTables>

This XML code calls a JavaScript program called myblocking.js. Ensure that you place this file in the following directory:

SDD/components/OBIPS/analyticsRes

SDD is the Singleton Data Directory.

The following is sample code for the myblocking.js program.

// This is a blocking function. It ensures that users select what 
// the designer wants them to.
function validateAnalysisCriteria(analysisXml)
{
   // Create the helper object
   var tValidator = new CriteriaValidator(analysisXml);
   // Validation Logic
   if (tValidator.getSubjectArea() != "Sample Sales")
      return "Try Sample Sales?";
   if (!tValidator.dependentColumnExists("Markets","Region","Markets","District"))
   {
      // If validation script notifies user, then return false
      alert("Region and District are well suited, do you think?");
      return false;
   }
   if (!tValidator.dependentColumnExists("Sales Measures","","Periods","Year"))
   return "You selected a measure so pick Year!";
   if (!tValidator.filterExists("Sales Measures","Dollars"))
   return "Maybe filter on Dollars?";
   if (!tValidator.dependentFilterExists("Markets","Market","Markets"))
   return "Since you are showing specific Markets, filter the markets.";
   var n = tValidator.filterCount("Markets","Region");
   if ((n <= 0) || (n > 3))
      return "Select 3 or fewer specific Regions";
   return true;
}

If you don't override the function using the template as described previously, or if the function returns anything other than false, then the criteria is considered to be valid and the analysis is issued. The criteria is validated using this same mechanism for preview and save operations as well.

After making this change, either stop and restart the server for Presentation Services, or click the Reload Files and Metadata link on the Administration page.

Block Analyses Based on Formula

Answers provides a hook that lets you incorporate a JavaScript validation function that's called from Answers when a content designer enters or modifies a column formula. If the call fails and returns a message, then Answers displays the message and cancels the operation.

Additionally, helper functions are available so the query blocking function can check for filters, columns, and so on, rather than traversing the Document Object Model (DOM) manually. The DOM is a way of describing the internal browser representation of the HTML UI page that's currently being displayed in Answers. See Validation Helper Functions.

The criteriatemplates.xml file includes a message named kuiFormulaBlockingScript that can be overridden to include JavaScript that defines a validateAnalysisFormula function. By default, this message contains a function that always returns true.

Answers calls validateAnalysisFormula before applying changes made by the content designer. If the function returns true, then the formula is accepted. If the function returns false, then the formula is rejected. Otherwise, the return value from the function is displayed in the message area beneath the formula, as it does currently when an invalid formula is entered.

The content designer has the option to click OK to ignore the error. To display your own alert and allow the content designer to continue, your function should return true. To block the query, return false or a message. Your function should investigate the formula passed to it using a JavaScript string and regular expression techniques for validation.

The following code example shows a sample custom message.

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

The following code example shows blocking based on the formula entered.

// This is a formula blocking function. It makes sure the user doesn't enter an unacceptable formula.
function validateAnalysisFormula(sFormula, sAggRule)
{
   // don't allow the use of concat || in our formulas
   var concatRe = /\|\|/gi;
   var nConcat = sFormula.search(concatRe);
   if (nConcat >= 0)
      return "You used concatenation (character position " + nConcat + "). That isn't allowed.";
   // no case statements
   var caseRe = /CASE.+END/gi;
   if (sFormula.search(caseRe) >= 0)
      return "Don't use a case statement.";
   // Check for a function syntax: aggrule(formula) aggrule shouldn't contain a '.'
   var castRe = /^\s*\w+\s*\(.+\)\s*$/gi;
   if (sFormula.search(castRe) >= 0)
      return "Don't use a function syntax such as RANK() or SUM().";
    return true;
}

After making this change, either stop and restart the server for Oracle BI Presentation Services, or click the Reload Files and Metadata link on the Administration page.

Validation Helper Functions

Several validation helper functions are available in a JavaScript file for you to use.

These functions are defined within a JavaScript file named answers/queryblocking.js. This table contains the list of helper functions and their descriptions.

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 wasn't 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 wasn't 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 isn't 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` isn't 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 isn't 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 can't be determined.