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
• Operators in Server Expressions
• Oracle Tokens

Supported SQL Functions

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

Function	Aggregating?	Description	Supported on
			Yes	Yes
Avg (column)	Yes*	Returns the average of the values of a group.	Yes	Yes
Count (column)	Yes***	Returns the number of members of a group.	Yes	Yes
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.	No	Yes
Lower (column)	No	Returns a string in lower case.	Yes	Yes
Ltrim (column)	No	Removes characters from the left side of the string.	Yes	Yes
Max (column)	Yes*	Returns the maximum of the values of a group.	Yes	Yes
Min (column)	Yes*	Returns the minimum of the values of a group.	Yes	Yes
Nvl (expression1, expression2)	No	If expression1 is not null, returns expression1. Otherwise, returns expression2.	No	Yes
Round (number [, m])	No	Rounds the given number to the specified number m of decimal places (zero by default).	Yes	Yes
Rtrim (column)	No	Removes characters from the right side of the string.	Yes	Yes
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.	Yes	Yes
SubStr (expression, start, length)	No	Returns the substring of a given length that starts at the given position.	No	Yes
Sum (column)	Yes**	Returns the sum of the values of a group.	Yes	Yes
Sysdate()	No	Returns the current date and time.	No	Yes
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.	No	Yes
To_date (date, [format])	No	Returns a formatted date.	No	Yes
To_number (date, [format])	No	Returns a number.	No	Yes
Upper (column)	No	Returns a string in upper case.	Yes	Yes
*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:

• +

• -

• *

• /

• ()

• <

• <=

• <>

• =

• >

• >=

• And

• Else

• In

• Not

• Or

• Then

• When

Calculations follow standard algebraic rules of precedence.

Oracle Tokens

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

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.