Develop JavaScript to Block Analyses Based on Formula

Whenever a user tries to enter or modify a column formula, Oracle Analytics invokes the function validateAnalysisFormula to verify the operation. You can customize validateAnalysisFormula to validate and block formulas based on your own specific criteria. If the function returns true, the formula is accepted. If validation fails the function returns false, the formula is rejected and your custom message displays.

To display a message and allow users to continue, your function must return true. To block the query, your function must return false or display a message. You can use a JavaScript string and regular expression techniques in your function to investigate and validate the formula.

Helper functions are available so the query blocking function can check for filters, columns, and so on. See Validation Helper Functions.

For example, the following code shows how to block a query if a user enters an unacceptable formula.

// 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;
}