Scripts in SQL

Intellicus allows dynamic SQL generation using executable script blocks embedded directly within the query. These blocks are written in JavaScript and are evaluated at runtime to produce query fragments based on current context (such as parameter values or report layout). This enables conditional clauses, table name substitutions, and optimized query construction.

Script Block Syntax

Start tag: <@%
End tag: %@>

Each script block must return a string, which is inserted into the SQL query during report execution.

Example:

SELECT * FROM emp WHERE 1=1
<@%
if (params("prmEmpNo") != null && params("prmEmpNo").getValue() !== "") {
  return " AND empno IN (<%prmEmpNo%>)";
}
return "";
%@>

Accessing Runtime Objects

Parameters

Use the params("parameterName") object with supported methods:

Method	Description

Method	Description
`getValue()`	Returns the parameter value as a string.
`getDataType()`	Returns the parameter data type (`NUMBER`, `CHAR`, `DATE`, `BOOL`, or `NULL`).

Examples:

params("prmCity").getValue();       // Get value of prmCity
params("prmAge").getDataType();     // Get data type of prmAge

Note
For system parameters or parameters without definitions, only getValue() is supported.

Report Layout (Ad hoc, Studio)

Use rpt.layout to access the layout structure when the SQL is being executed in the context of a report.

var arl = rpt.layout.getArl();
if (arl != null) {
  var fields = arl.getFields();
  // Access layout fields
}

Layout access is only available during report execution (e.g., Preview, Run) and not during SQL verification in the SQL Editor.

Script Block Examples

Example 1: Conditional WHERE clause based on parameter

SELECT * FROM emp WHERE 1=1
<@%
if (params("prmEmpNo") != null && params("prmEmpNo").getValue() !== "") {
  return " AND empno IN (<%prmEmpNo%>)";
}
return "";
%@>

Example 2: Dynamic table selection

SELECT * FROM
<@%
if (params("prmSelectTable") == null) {
  return " emp ";
}
return params("prmSelectTable").getValue();
%@>

Example 3: Dynamic procedure execution

<@%
if (params("p_EmpNo") != null) {
  return "EMP_DETAILSWITHARG <%p_EmpNo%>";
}
return "EMP_DETAILS";
%@>

Example 4: Optimizing SELECT clause for Ad hoc report

This script dynamically constructs the SELECT clause based on fields used in Ad hoc components like filters, groups, charts, matrix, etc.

SELECT
<@%
var arl = rpt.layout.getArl();
if (arl != null) {
  var fields = arl.getFields();
  var myArray = {};
  var str = "";

  for (var i = 0; i < fields.getCount(); i++) {
    var field = fields.get(i);
    if ("TRUE" === field.getDisplayEnabled()) {
      myArray[field.getName()] = field.getName();
    }
  }

  // Add fields from groups
  for (var i = 0; i < rpt.layout.getGroupsCount(); i++) {
    var grp = rpt.layout.getGroup(i);
    myArray[grp.getFieldName()] = grp.getFieldName();
  }

  // Add fields from filters
  for (var i = 0; i < rpt.layout.getFiltersCount(); i++) {
    var fltr = rpt.layout.getFilter(i);
    myArray[fltr.getFieldName()] = fltr.getFieldName();
  }

  // Add fields from sort
  for (var i = 0; i < rpt.layout.getSortParamsCount(); i++) {
    var sortParam = rpt.layout.getSortParam(i);
    myArray[sortParam.getFieldName()] = sortParam.getFieldName();
  }

  // Add fields from conditions (highlighting)
  var conditions = arl.getAConditions();
  if (conditions != null) {
    for (var i = 0; i < conditions.getCount(); i++) {
      var clause = conditions.get(i).getAClause(0);
      myArray[clause.getFieldName()] = clause.getFieldName();
    }
  }

  // Add fields from chart
  var chart = arl.getChart();
  if (chart != null) {
    for (var i = 0; i < chart.getChartXAxisCount(); i++) {
      var x = chart.getChartXAxis(i);
      myArray[x.getFieldName()] = x.getFieldName();
    }
    var seriesEnum = chart.getSeriesEnum();
    for (var i = 0; i < seriesEnum.size(); i++) {
      var series = seriesEnum.get(i);
      myArray[series.getFieldName()] = series.getFieldName();
    }
  }

  // Add fields from matrix
  var matrix = arl.getMatrix();
  if (matrix != null) {
    var summaries = matrix.getMatrixSummaries();
    for (var i = 0; i < summaries.size(); i++) {
      myArray[summaries.get(i).getFieldName()] = summaries.get(i).getFieldName();
    }

    var xAxes = matrix.getMatrixXAxes();
    for (var i = 0; i < xAxes.size(); i++) {
      myArray[xAxes.get(i).getFieldName()] = xAxes.get(i).getFieldName();
    }

    var yAxes = matrix.getMatrixYAxes();
    for (var i = 0; i < yAxes.size(); i++) {
      myArray[yAxes.get(i).getFieldName()] = yAxes.get(i).getFieldName();
    }
  }

  // Add any additional formula fields
  var formulaFields = ["Ccy1amount1", "Ccy1amount2"];
  for (var i = 0; i < formulaFields.length; i++) {
    myArray[formulaFields[i]] = formulaFields[i];
  }

  // Construct final SELECT string
  for (var key in myArray) {
    str += myArray[key] + ",";
  }
  return str.slice(0, -1); // remove trailing comma
}
return " * ";
%@>
FROM Call_Log

Inserting Script Block in SQL

You can add a script block in the SQL of:

Standard reports
Query Objects