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.

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.

Limitations

1. Expressions are not supported for use on repeating row fields within your data dictionary
2. Text Area, HTML Area, Print Text and Log Area fields are limited in that they can only display (approximately) the first 4,000 characters of any entry.  In many circumstances this is sufficient, but if you have really long entries, they will be truncated with report expressions.  This is because these field types are actually concatenated in 4,000 chunks in the database and special logic is used to handle word boundaries so that words are always searchable as keywords in the database.

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. Date fields within the ExtraView database are stored as timestamp type fields, which is done for internal efficiency and to facilitate a wide number of internal computations. This does not make them very simple to work with in expressions, but the following examples should help significantly. The session variable must have been defined with a compatible display type, such as DATE, DAY, or TEXTFIELD for use within the expression.

Date Calculations

A frequently use of expressions is to calculate the difference between two date fields. The internal database representation of date fields use the TIMESTAMP data type. This data type does not lend itself to simple addition and subtraction to calculate differences between dated. The date_diff function is an ExtraView function that simplifies date calculations. It is used in this way:

    date_diff($$DATE_FIELD_1$$, $$DATE_FIELD_2$$)

This returns the difference as a whole number, between the two date fields.  This is a Number type expression as the result is returned as a whole number.

Examples

1. Compute the elapsed time, in number of days, from when an issue was created, to when it was last updated, and place the result in a field named DAYS_ELAPSED:
   
   DAYS_ELAPSED = date_diff($$TIMESTAMP$$, $$DATE_CREATED$$)
   
   Note that you only enter the right-hand side of the expression into the Default Value in the data dictionary, or into the Expression field in the report editors
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. Add seven days to the current date:
   
   Oracle and SQL Server – $$SYSDATE$$ + 7
   MySQL – DATE_ADD($$SYSDATE$$, INTERVAL 7 DAY)
    
   
   
5. This is a more complex example, which concatenates several fields together, and places the results in a single output column on a report.  The example takes the fields SALUTATION_TITLE, FIRST_NAME, LAST_NAME as the first line of the output.  The chr(10) provides a linefeed so that the field ADDRESS appears on the second line.  The next line contains ADDRESS_2, then followed in the next line by ADDRESS_3.  The next line contains a concatenation of the CITY, STATE and POSTAL_CODE.  The final line contains the PHONE field.  The decode statements are standard SQL that allow the suppression of the field, if there is no value returned from the query.
   
   decode($$SALUTATION.TITLE$$,'','',$$SALUTATION.TITLE$$ || ' ') ||
decode($$FIRST_NAME$$,'','',$$FIRST_NAME$$) || chr(10) ||
decode($$LAST_NAME$$,'','',$$LAST_NAME$$) || chr(10) ||
decode($$ADDRESS$$,'','',$$ADDRESS$$ || chr(10)) ||
decode($$ADDRESS_2$$,'','',$$ADDRESS_2$$ || chr(10)) ||
decode($$ADDRESS_3$$,'','',$$ADDRESS_3$$ || chr(10)) ||
decode($$CITY$$,'','',$$CITY$$ || ', ') ||
decode($$STATE$$,'','',$$STATE$$ ||' ') ||
decode($$POSTAL_CODE$$,'','',$$POSTAL_CODE$$) || chr(10) ||
decode($$PHONE$$,'','',$$PHONE$$)
   
   The end result is that you have composed a single field that contains the address in the expected format, although the components were each stored as individual fields.