Server Expression Functions and Operators

This appendix provides reference information for the operators and functions that are allowed in server expressions.

This chapter covers the following topics:

Supported SQL Functions

You can use the following SQL functions in a Oracle server expression:

Function Aggregating? Description
     
Avg (column) Yes* Returns the average of the values of a group.
Count (column) Yes*** Returns the number of members of a group.
Decode (expression, value1 [, return1, value2, return2, ...], defaultreturn) No Matches expression to the test cases value1, value2, and so on, and returns the return value (return1, return2, and so on) that corresponds to the matched value.
Get_Max_Date() No Returns the latest sales date loaded as history in the system. This optional parameter passes the user's time zone to the function, and returns the calculated latest sales date that is relevant to the user.
Lower (column) No Returns a string in lower case.
Ltrim (column) No Removes characters from the left side of the string.
Max (column) Yes* Returns the maximum of the values of a group.
Min (column) Yes* Returns the minimum of the values of a group.
Nvl (expression1, expression2) No If expression1 is not null, returns expression1. Otherwise, returns expression2.
Round (number [, m]) No Rounds the given number to the specified number m of decimal places (zero by default).
Rtrim (column) No Removes characters from the right side of the string.
Safe_Division (argument1, argument2, argument3) No Custom function created by Oracle. This function returns argument1 divided by argument2, unless argument2 is null. If argument2 is null, then the function returns argument3.
SubStr (expression, start, length) No Returns the substring of a given length that starts at the given position.
Sum (column) Yes** Returns the sum of the values of a group.
Sysdate() No Returns the current date and time.
To_char (date, [format]) or To_char (number, [format]) No Returns the input, a date or number, converted to a string using the given format.
To_date (date, [format]) No Returns a formatted date.
To_number (date, [format]) No Returns a number.
Upper (column) No Returns a string in upper case.
*If you use this function as the aggregating function for a series, the series should be non-proportional. **If you use this function as the aggregating function for a series, the series should be proportional. ***If you use this function as the aggregating function for a series, the series should be non-editable.

Note: A server expression must be an aggregating expression that returns numeric, date, string, or true/false values.

Note: If a series is going to be used within cached worksheets, its server expression cannot return null or zero-length values. Use the expression to_number(null,0) to express null values.

Note: In these reference sections, square brackets indicate optional parts of the syntax.

For details on these functions, consult the appropriate Oracle database documentation.

Operators in Server Expressions

You can use the following operators in a Oracle server expression:

Calculations follow standard algebraic rules of precedence.

Oracle Tokens

You can use the following special-purpose tokens in a Oracle server expression:

Note: If the engine profile is not designated in the token, the token will default to the batch forecast profile.

Token Allowed in Automatically replaced by
#CONFIDENCE_LEVEL# Server expressions Confidence level associated with the forecast. Not supported in the Web user interfaces.
#FDATE@<Version>@<Profile_id># Series hint messages Date of the specified engine profile's forecast version*. For example, #FDATE@0@25# is replaced by the date on which current forecast for engine profile 25 was generated.
#FORE@<Version>@<Profile_id># Server expressions The specified forecast version*. For example: #FORE@1@25# is replaced by the second most current forecast version generated using engine profile 25.
#POST_EFFECT@<Version>@<Profile_id># PE server expressions The post-promotional effect associated with the specified forecast version generated using a specified engine profile. *
#PRE_EFFECT@<Version>@<Profile_id># PE server expressions The pre-promotional effect associated with the specified forecast version generated using a specified engine profile. *
#SIMULATION_TABLE# Server expressions .
#SW_BRAND@<Version>@<Profile_id># PE server expressions The brand switching associated with the specified forecast version generated using a specified engine profile. *
#SW_CHANNEL@<Version>@<Profile_id># PE server expressions The channel switching associated with the specified forecast version generated using a specified engine profile *
#SW_PRODUCT@<Version>@<Profile_id># PE server expressions The product switching associated with the specified forecast version. *
#SW_STORE@<Version>@<Profile_id># PE server expressions The store switching associated with the specified forecast version generated using a specified engine profile. *.
#UNIT# Server expressions The unit conversion factor that corresponds to the unit used in the worksheet. See “Configuring Units, Indexes, and Update-Lock Expressions”.
#UPLIFT@<Version>@<Profile_id># PE server expressions The uplift associated with the specified forecast version generated using a specified engine profile. *
* The most recent forecast is 0, the previous forecast is 1, and so on.