Report Expressions

Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of days between two events.

Report expressions are first defined by creating a field in the data dictionary that is placed on the report. The actual calculation or expression is defined in the default value of the field, or in an attribute that is stored with the field within a report. Using the default value allows you to define a calculated field that may be shared across many reports without further configuration. However, you can use a single Expression type field defined in the data dictionary and reuse this on many reports for many purposes. You achieve this by selecting the field for use on any report, then using its Alternative Title and Expression attributes to modify its purpose for that report.

Syntax for Report Expressions

The general syntax of an expression matches a subset of that in ANSI-99 SQL expressions, with arithmetic, string, and date data types. This includes infix and prefix operators, with standard precedence rules. Also, parentheses may be used to group sub-expressions.

Note that although the expressions utilize ANSI-99, the different databases upon which ExtraView may be installed do use different syntax for many operations, and the syntax you use within your expressions must be correct for the underlying database. To make matters even more complex, the syntax of some expressions varies across different versions of the same database.

Literals of each type are supported and although full ANSI syntax is not promised, it should be sufficient to allow specification of a specific value for each data type.

Variable names may refer to fields in the database. Each variable is in the form $$DD_NAME$$ and may refer to a standard ExtraView field name or special variable. Usually, the specification of a variable name refers to its report value (viz., its title or rendered form). However, inside an expression, the value of the variable is used, not its rendered form. Also, see the note on enumerated types below.

Limitation

Expressions are not supported for use on repeating row fields within your data dictionary.

Variable References to Enumerated Types

Enumerated types (fields with a display type of List, Popup, Checkbox, and Tab) may be referenced only as string variables, in which case their title is used in the expression. Only single-valued list type fields may be referenced.

Evaluation of Expressions

As much as possible, report expressions are evaluated by ExtraView by processing at two levels:

  • Select list construction within the detail report query; and
  • Retrieval of the detailed expression values for rendering in the report row

Syntax checking includes tests for single-quoted strings, double-quoted strings, parentheses and the “cast” function. Syntax checking is done before the attribute is entered and stored as part of the report.

Null Values

The existence of a null value in an expression renders the result of the entire expression value to be null.

Expression Error Handling

Each report expression is syntax-checked and variable references are validated before allowing the containing report to be updated or run. There may be opportunity for the user to create erroneous expressions, as not all expressions can be determined without reference to the values in the database.

Report Field Titles

To support report-specific titles on the expression fields, the ALTERNATE FIELD TITLE attribute is supported. This compensates for the difficulty for users to create column headings when they are sharing a data dictionary entry, such as the EXPRESSION type entries. The ALTERNATE FIELD TITLE attribute appears within the GUI of the report builder to allow the user to define a title for a report through a simple selection mechanism.

Use of Session Variables

Session variables defined in the data dictionary may be used as substitutable variables in a report expression. Each referenced session variable value must be set using a runtime filter for the report. Session variables may also be used within runtime filters to provide a mechanism to provide the same value for the occasions when the same field is used multiple times within filter queries. This can happen with advanced queries that use multiple and and or conjunctions.

The session variable must have been defined with a compatible display type, such as DATE, DAY, or TEXTFIELD for use within the expression.

Examples

  1. Compute the elapsed time, in number of days, from a timestamp in the issue to the current time:

    Oracle 9 - round($$sysdate$$ - $$date_field$$)

    Oracle 10 - extract(day from date1 - date2)

  2. Compute a total sale value with sales tax based on a rate of 8%:

    $$currency_field$$ * 1.08

  3. Count the number of selections of a value in a field named my_field with a value of Yes, when another field named other_field has a specific value of val:

    case when ($$my_field$$ = 'Yes' and $$other_field$$ = 'val') then 1 else null end

  4. With a runtime filter referencing REPORT_AS_OF session variable with display type of DATE, the following expression computes the number of days between the current date and the specified runtime filter value for REPORT_AS_OF:

    $$SYSDATE$$ - $$REPORT_AS_OF$$