Skip to end of metadata
  • Created by Former user, last modified by Former user on Oct 17, 2010
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

Using the Formula Editor

(minus) Important: The Report Designer interface has changed, and the documentation has been updated to reflect that.


The Formula Editor allows you to associate a formula with a text field, date field, number field, and so on. Formulas allow you to define relationships between columns in your report. While they play an important role, formulas can be difficult to create. The Formula Editor makes the process of creating a formula easy and efficient; it also validates the formula thereby minimizing errors.

(warning) Warning: You cannot add a formula through the Properties pane when a field is selected. You must create the formula first, then associate it with a field.

Follow the instructions below to create a formula:

  1. In the Design panel, select a field to which you want to associate a formula.
  2. Click Report Functions in the upper right panel.
  3. Click Add Function to Report in the Properties panel.
  4. Select the newly created function.
  5. In the Properties panel, click Set Properties to open the Formula Editor.
  6. In the Formula Editor dialog box, select a Category from the list.  The Function list is populated with the functions that are most appropriate for the category you selected.
  7. Select the appropriate function. When you select a function, the Report Editor displays the formula expression (the equation and relevant parameters); it also displays the Return Type and Description as shown in the example below:
     

 
You can enter a formula expression directly into the editable text box under Formula; however, it may easier to enter the values in the appropriate text boxes in the Parameters panel.  The formula expression updates automatically as you enter values. You can select and insert a data field (from your data source) into your formula expression. You are not required to know the syntax or remember the names of available fields. Click the icons next to text boxes in the Parameters panel to display existing report fields.

Formula Syntax Description

Formulas start with a "=" or a formula prefix (any letter and number followed by a colon; the prefix is not interpreted yet, but is used later to separate different formula-namespaces. Therefore, the following formulas are equivalent:

=1

report:1

References to external data-fields are expressed using square brackets.

=[yourfield]

If a field's name contains special characters, first surround it with double quotes:

=["[Reseller].[Reseller.Reseller].[All Resellers].[東京店]"]

Possible operators are:

  • Basic computation: +, -, *, /
  • Percentage: %
  • Power: ^
  • String-concatenation: &
  • Comparisons: =, <>, <, <=, >, >=
  • Formulas can have braces.

Example:

=(1+1)*2 / 3

results in 1.3333333

=10%

results in 0.1

String-constants are expressed using double-quotes.

="Your text here"

Strings can be concatenated using the & operator.

="Your text here " & "some more text"

The Range-Operators known from Spreadsheet applications are not supported, as they have no meaning in the reporting. A formula can only work on the current values of the current datarow. There is no way to access previous or next rows of data. (These are the same limitations as with all expressions, so this is nothing new.)

Date/Time Functions

DATE - Provides an internal number for the given date.
DATEDIF - Returns the number of years, months, or days between two dates.
TIME - Returns the current time.
DATEVALUE - Returns date serial number from given text.
NOW - Return the serial number of the current date and time. This returns the current day and time serial number, using the * current locale.
TODAY - Returns the current date, (yyyy-mm-dd).
DAY - Returns the day of the month for a date (1 through 31)
MONTH - Returns the month of a date
YEAR - Returns the year of a date
WEEKDAY - Returns the day of week from a date.
HOUR - Returns the hour (0 through 23) from a time.

Logical functions

AND - Returns TRUE if all arguments are TRUE.
FALSE - Defines the logical value as FALSE.
IF - Specifies a logical test to be performed.
NOT - Reverses the value of an argument.
OR - Returns TRUE if an arguments are TRUE.
TRUE - Defines the logical value as TRUE.
XOR - Returns a value of TRUE only if just one of its operands is TRUE. In contrast, an inclusive OR operator returns a value of TRUE if either or both of its operands are TRUE.

Rounding functions

INT - Returns a number down to the nearest integer

Mathematical functions

SUM - Sum a list of numeric values.
ABS - Returns the absolute (nonnegative) of the value.
AVERAGE - Average a list of numeric values.
ODD - Returns the rounding a number up to the nearest odd integer.
EVEN - Returns the rounding a number up to the nearest even integer.
MOD - Returns the remainder when one number is divided by another number.
MAX - Returns the maximum from a set of numbers.
MIN - Returns the minimum from a set of numbers.

Text functions

LEN - Returns the lengh in characters of the given value.
LOWER - Returns the given text in lower case.
UPPER - Returns the given text in upper case.
TRIM - Returns the given text free of leading and trailing spaces. Internal multiple spaces are replaced by one.
TEXT - Returns the given value as text.
T - Returns the given text value or a zero lenght string for non text type.
FIND - Returns the starting position of a given text.
EXACT - Reports if two text values are exactly equal using a case-sensitive comparison.
REPT - Returns text repeated Count times..
MID - Returns extracted text, given an original text, starting position, and length.
LEFT - Returns a selected number of text characters from the left.
RIGHT - Returns a selected number of text characters from the right.
URLENCODE - Applies URL-Encoding to a text given in the first parameter \
  using the encoding specified in the second parameter. If no encoding is \
  given, ISO-8859-1 is assumed.
REPLACE - Returns text where an old text is substituted with a new text within the start index and length range.
SUBSTITUTE - Returns text where an old text is substituted with a new text. If which is specified, only this occurrance will be replaced otherwise every occurrance will be replaced.

Information functions

HASCHANGED - Checks, whether the columns specified by the given names has changed.
ISBLANK - Checks, whether the value is undefined (null).
NA - Returns the constant error NA.
ISNA - Tells if the parameter is of error type NA.
ISLOGICAL - Checks whatever the value is of type Logical.
ISTEXT - Checks whatever the value is of type Text.
ISERR - Tells if the parameter is of error type but returns false if the error is of type NA.
ISERROR - Tells if the parameter is of error type but returns false.
CHOOSE - Uses an index to return a value from a list of values.
ISREF - Checks whatever the value is a reference.
ISNUMBER - Checks whatever the value is of type Number.
ISODD - Checks whatever the value is an odd number.
ISEVEN - Checks whatever the value is an even number.
ISNONTEXT - Checks whatever the value is not of type Text.

Userdefined
NULL - Returns a NULL-Value

Formula Operators

+ Add
- Subtract
* Multiply
/ Divide
= Equal
<> Not Equal
< Less Than
> Greater Than
<= Equal Less Than
>= Equal Greater Than
& Concatenate
^ Power

Prefix/Suffix Operators

% Percent (Suffix)
+ Positive (Prefix)
- Negative (Prefix)

Note: The formula syntax used in LibFormula is based on the OpenFormula standard. Download the complete documentation from Oasis: OASIS OpenFormula Specifcation

  • No labels