1. Designing Pixel-Perfect Reports in Oracle Analytics Cloud
  2. Create Pixel-Perfect Reports and Layouts
  3. Create eText Templates
  4. Expressions, Control Structures, and Functions

Expressions, Control Structures, and Functions

This section describes the rules and usage for expressions in the template. It also describes supported control structures and functions.

Expressions

Expressions can be used in the data column for data fields and some command parameters. An expression is a group of XML extract fields, literals, functions, and operators. Expressions can be nested. An expression can also include the "IF" control structure. When an expression is evaluated it always generates a result. Side effects are not allowed for the evaluation.

Based on the evaluation result, expressions are classified into the following three categories:

  • Boolean Expression - an expression that returns a boolean value, either true or false. This kind of expression can be used only in the IF-THEN-ELSE control structure and the parameter of the display condition command.

  • Numeric Expression - an expression that returns a number. This kind of expression can be used in numeric data fields. It can also be used in functions and commands that require numeric parameters.

  • Character Expression - an expression that returns an alphanumeric string. This kind of expression can be used in string data fields (format type Alpha). They can also be used in functions and command that require string parameters.

Control Structures

The only supported control structure is IF-THEN-ELSE. It can be used in an expression.

The syntax is:

IF <boolean_expressionA> THEN
   <numeric or character expression1>
[ELSIF <boolean_expressionB THEN
   <numeric or character expression2>]
...
[ELSE
   <numeric or character expression3]
END IF

Generally the control structure must evaluate to a number or an alphanumeric string. The control structure is considered to a numeric or character expression. The ELSIF and ELSE clauses are optional, and there can be as many ELSIF clauses as necessary. The control structure can be nested.

The IN predicate is supported in the IF-THEN-ELSE control structure. For example:

IF PaymentAmount/Currency/Code IN ('USD', 'EUR', 'AON', 'AZM') THEN      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value * 100   
ELSIF PaymentAmount/Currency/Code IN ('BHD', 'IQD', 'KWD') THEN      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value * 1000   
ELSE      
   PayeeAccount/FundsCaptureOrder/OrderAmount/Value   
END IF;

Functions

Here is the list of supported functions.

  • SEQUENCE_NUMBER - Is a record element index. It's used in conjunction with the Define Sequence command. It has one parameter, which is the sequence defined by the Define Sequence command. At runtime it increases its sequence value by one each time it's referenced in a record.

  • COUNT - Counts the child level extract instances or child level records of a specific type. Declare the COUNT function on a level above the entity to be counted. The function has one argument. If the argument is a level, then the function counts all the instances of the (child) level belonging to the current (parent) level instance.

    Example: If the level to be counted is Payment and the current level is Batch, then the COUNT returns the total number of payments in the batch. However, if the current level is RequestHeader, the COUNT returns the total number of payments in the file across all batches. If the argument is a record type, the count function counts all the generated records of the (child level) record type belonging to the current level instance.

  • INTEGER_PART, DECIMAL_PART - Returns the integer or decimal portion of a numeric value. This is used in nested expressions and in commands (display condition and group by). For the final formatting of a numeric field in the data column, use the Integer/Decimal format.

  • IS_NUMERIC - Boolean test whether the argument is numeric. Used only with the "IF" control structure.

  • TRUNCATE - Truncates the first argument - a string to the length of the second argument. If the first argument is shorter than the length specified by the second argument, the first argument is returned unchanged. This is a user-friendly version for a subset of the SQL substr() functionality.

  • SUM - Sums all the child instance of the XML extract field argument. The field must be a numeric value. The field to be summed must always be at a lower level than the level on which the SUM function was declared.

  • MIN, MAX - Finds the minimum or maximum of all the child instances of the XML extract field argument. The field must be a numeric value. The field to be operated on must always be at a lower level than the level on which the function was declared.

  • FORMAT_DATE - Formats a date string to any desirable date format. For example:

    FORMAT_DATE("1900-01-01T18:19:20", "YYYY/MM/DD HH24:MI:SS")

    produces the following output:

    1900/01/01 18:19:20

  • FORMAT_NUMBER - Formats a number to display in desired format. For example:

    FORMAT_NUMBER("1234567890.0987654321", "999,999.99")

    produces the following output:

    1,234,567,890.10

  • MESSAGE_LENGTH - Returns the length of the message in the EFT message.

  • RECORD_LENGTH - Returns the length of the record in the EFT message.

  • INSTR - Returns the numeric position of a named character within a text field.

  • SYSDATE, DATE - Gets Current Date and Time.

  • POSITION - Returns the position of a node in the XML document tree structure.

  • REPLACE - Replaces a string with another string.

  • CONVERT_CASE - Converts a string or a character to UPPER or LOWER case.

  • CHR - Gets the character representation of an argument, which is an ASCII value.

  • LPAD, RPAD - Generates left or right padding for string values.

  • AND, OR, NOT - Operator functions on elements.

  • AddToVar - Adds the specified value to the current value of the variable, and returns the value added to the variable. If the assignment is unsuccessful, the function returns 0. If the variable doesn't exist, the function creates a new variable and assigns the specified value.

    Usage: AddToVar(var-name,value-to-be-added)

    Example:

    AddToVar('MyVAR', 10) - if MyVar=0, assigns 10 to MyVar, and returns 10.

    AddToVar('MyVAR', 20) - if MyVar=10, adds 20 to MyVar, assigns 30 to MyVar, and returns 20.

  • GetVar - Returns the value of the specified variable. Returns 0 if the variable doesn't exist.

    Usage: GetVar(var-name)

    Example: GetVar('MyVAR')

  • ResetVar - Resets the value of the variable to 0.

    Usage: ResetVar(var-name)

    Example: ResetVar('MyVAR')

  • SetVar - Assigns the specified value to the variable, and returns the assigned value.

    Usage: SetVar(var-name, value-to-be-assigned)

    Example: SetVar('MyVAR', 1200)

  • DISTINCT_VALUES - Returns a sequence in which all but one of a set of duplicate values, based on value equality, have been deleted. Equivalent to the XPATH function DISTINCT-VALUES. Usage: distinct_values(fieldname).

  • INCREASE_DATE - Increments a date by the number of days specified.

    Usage:

    increase_date(.//date, 2)

    returns a date value two days after the value of .//date

  • DECREASE_DATE - Decreases a date by the number of days specified.

    Usage:

    decrease_date(.//date, 2)

    returns a date value two before the value of .//date

  • XPATH - Allows direct injection of pure XPath language to generate the XSL template.

    Example:

    XPATH('sum((../PRE_TAX_DEDUCTIONS/PAYMENT)[position() >= 9])')

    XPATH('sum((../PRE_TAX_DEDUCTIONS/PAYMENT)[NAME = 'NULL'])')

  • Other SQL functions include the following. Use the syntax corresponding to the SQL function.

    • TO_DATE

    • LOWER

    • UPPER

    • LENGTH

    • GREATEST

    • LEAST

    • DECODE

    • CEIL

    • ABS

    • FLOOR

    • ROUND

    • CHR

    • TO_CHAR

    • SUBSTR

    • LTRIM

    • RTRIM

    • TRIM

    • IN

    • TRANSLATE