Validate and Block Queries in Analyses Using Custom JavaScript

You can develop validation scripts in JavaScript to validate analysis criteria and column formulas, and block invalid queries.

Block Queries in Analyses

Users working with analyses can invoke custom JavaScript to validate analysis criteria and column formulas. The validation allows for queries to be blocked when editing an analysis. The custom JavaScript must be accessible through a web server. To enable this feature, administrators specify the URL of the web server in system settings and register the web server as a safe domain.

Develop your custom validation scripts in JavaScript, store them in a web server, and make a note of the URL pointing to the JavaScript (*.JS) file containing the custom scripts.
For example, you might develop a blocking script that you store in myblocking.js, and the URL might be:http://example.com:8080/mycustomscripts/myblocking.js.
Specify the URL of your web server in system settings:
1. Click Console, then click Advanced System Settings.
2. In URL for Blocking Queries in Analyses, enter the URL that you noted in Step 1.
Register the web server as a safe domain:
1. Click Console, then click Safe Domains.
2. Add an entry for the domain in the URL you specified in Step 2.
  For example, you might add: example.com:8080.
3. For options, select Script and Connect.
Test your validation scripts:
1. Open an analysis.
2. Run the analysis with both valid and invalid criteria.
3. Verify that queries are blocked as expected.

Develop JavaScript to Block Analyses Based on Criteria

Whenever a user tries to run an analysis, Oracle Analytics invokes the function validateAnalysisCriteria. You can customize validateAnalysisCriteria to validate and block queries based on your own specific criteria. If the function returns true, the query runs. If the function returns false or displays a message, the query is blocked.

For example, the following is sample code for a JavaScript program called myblocking.js.

// 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 the function returns anything other than false, the criteria is considered to be valid and the analysis runs. The function is also use to validate criteria for preview and save operations.

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

Validation Helper Functions

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

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.