Working with the Elements of Rules
This topic provides an overview of the elements of rules and discusses how to work with these rules.
This topic discusses the various elements that are included in rules.
Builtin Functions
Many useful calculations are difficult or impossible to perform with simple arithmetic. You can perform many such calculations by using Analytic Calculation Engine's builtin functions.
Most functions have one or more arguments that supply the information that the function needs to perform the calculation. Arguments are enclosed within parentheses after a function name.
When a function contains more than one argument, the arguments are always separated by commas. For example, the following formula uses the MIN function to calculate the minimum of CASH_NEEDED and CREDIT_AVAILABLE to determine the values of the CASH_ADVANCE data cube:
MIN(CASH_NEEDED, CREDIT_AVAILABLE)
Some functions do not take any arguments because they do not require additional information to calculate a result. For example, the PI function returns the mathematical constant pi. Because this function does not require any information, it does not take any arguments. Nevertheless, you must still follow the function name with parentheses. For example, the following formula calculates the circumference of a circle using the PI function:
PI( ) * DIAMTER_OF_CIRCLE
The parentheses following PI indicate that the name is a function rather than a data cube reference.
Many functions have one or more optional arguments. If you leave out an optional argument, the analytic calculation engine supplies a default value for the argument. For example, the CHANGE function calculates the change between members of a dimension and takes these arguments in order:
The dimension for which you want to calculate the change.
The information for which you want to calculate the change.
How many members back to look.
Note: The third argument is optional; if you do not include it, the analytic calculation engine assumes you want to calculate the change from only the previous member.
For example, suppose you want to calculate the monthly change in sales. You can use the CHANGE function and leave out the third argument, as shown in the following formula:
CHANGE(MONTHS, SALES)
For each month, the analytic calculation engine calculates the change in sales from the previous month.
Now suppose you want to calculate the yearly change in sales. You can use the CHANGE function and supply 12 as the third argument, as shown in the following formula:
CHANGE(MONTHS, SALES, 12)
For each month, the analytic calculation engine calculates the change in sales from 12 previous months. To summarize:
You must always place parentheses after a function name.
If a function contains arguments, place the arguments inside the parentheses.
If a function contains more than one argument, separate the arguments with commas.
You can leave out an optional argument if the default value for the argument is satisfactory.
See CHANGE.
Conditions and Conditional Formulas
A condition is an expression that evaluates as true or false. A conditional formula returns different values for different conditions. The most simple conditional formula returns one value if a specified condition is true, and a different value if the condition is false. A complex conditional formula may return many different values based on many different conditions. These are types of conditions and conditional formulas:
Comparison operators.
Truth functions.
Compound conditions.
See the Comparison Operators, Order of Precedence, and Compound Condition sections for more information.
Comparison Operators
You can compare the values of two expressions using one of Analytic Calculation Engine's comparison operators.
A comparison returns either a True value (1) or a False value (0), depending on the values of the two expressions.
Note: The analytic calculation engine always interprets a nonzero value as True and a zero value as False.
The expressions in a comparison can contain mathematical operators, parentheses, and functions, as well as data cubes and numbers. The analytic calculation engine evaluates the expressions on both sides of the comparison operator before it evaluates the truth of the comparison. Following are some examples of comparisons:
ADVERTISING >= 10000
ADVERTISING + PROMOTION < 0.5 * (MARKETING_EXPENSE  MARKETING_SALARIES)
The following table describes Analytic Calculation Engine's comparison operators.
Comparison Operator 
Example of Comparison 
Meaning of Comparison 

= 
A = B 
A is equal to B. 
<> 
A <> B 
A is not equal to B. 
> 
A > B 
A is greater than B. 
< 
A < B 
A is less than B. 
>= 
A >= B 
A is greater than or equal to B. 
<= 
A <= B 
A is less than or equal to B. 
Truth Functions
A truth function is a function that returns 1 (True) or 0 (False), depending on whether the arguments of the function satisfy a condition. The analytic calculation engine uses truth functions to evaluate conditions that are too complex to express easily with comparison operators.
For example:
IF(FIRST(MONTH), 0, SET(&RunningTotal , &RunningTotal + THISCUBE())
In this example, if the current month that is calculated is the first month, the function returns 0. If the current month that is calculated is not the first month, the function returns the running total.
Logical Operators
A logical operator determines whether a condition is true. The following table describes the logical operators.
Logical Operator 
Meaning 
Syntax 

.NOT. 
Condition is not True. 

.AND. 
Condition1 is True and Condition2 is True. 
Condition1 
.OR. 
Condition1 is True or Condition2 is True. 
Condition1 
Compound Conditions
A compound condition tests whether some combination of conditions is true by combining two or more comparisons or truth functions using logical operators.
The analytic calculation
engine evaluates the .NOT.
operator before the .AND.
and .OR.
operators,
and evaluates the .AND.
and .OR.
operators from left to right. You can override the
precedence of the logical operators with parentheses, just as you
can with the mathematical operators. The following table provides
some examples of compound conditions.
Example of Compound Condition 
Meaning of Compound Condition 


Returns True if SALES is greater than 50000 and ADVERTISING is less than 10000. 

Returns True if CASH_REMAINING is less than 1000 or if PROJECT_DONE is True. 

Returns True if IS_FIRST is not True and IS_LAST is not True. 

Returns True if the condition (IS_FIRST or IS_LAST) is not True. Note: This condition has the same effect as the previous condition. 
Predefined Constants
Analytic Calculation Engine provides several predefined constants that you can use in rules. You can use constants in the same way that you use numbers in rules. For example, you can test whether a data cube equals the constant, or you can return the constant as a result.
The following table describes predefined constants.
Predefined Constant 
Definition 


Use this predefined constant as the last argument of the CHILDCOUNT or FORCHILDREN functions to return all of a dimension member's children, including grandchildren. If you do not specify a dimension member, this constant returns all of the children and grandchildren of the dimension member that is attached to the data cube that is currently being calculated. Note: You can also use the #DETAILS or #DIRECT predefined constants as the last argument for the CHILDCOUNT or FORCHILDREN functions. See CHILDCOUNT, FORCHILDREN. 

A blank value. Use this constant to test whether a value in a data cube is blank or to return a blank value as a result. 

Use this predefined constant with trees as the last argument of the CHILDCOUNT or FORCHILDREN functions to return only the dimension members that are details. If you do not specify a dimension member, this constant returns only the details of the dimension member that is attached to the data cube that is currently being calculated. Note: You can also use the #ALL or #DIRECT predefined constants as the last argument for the CHILDCOUNT or FORCHILDREN functions. See CHILDCOUNT, FORCHILDREN. 

Use this predefined constant as the last condition in a CASE function to return a default result when all other conditions are false. For example:
See CASE. 

Use this predefined constant with trees as the last argument of the CHILDCOUNT or FORCHILDREN functions to return a dimension member's direct children only. If you do not specify a dimension member, this constant returns only the direct children of the dimension member that is attached to the data cube that is currently being calculated. Note: You can also use the #ALL or #DETAILS predefined constants as the last argument for the CHILDCOUNT or FORCHILDREN functions. See CHILDCOUNT, FORCHILDREN. 

The value of e (2.7182818285), which is the base of natural logarithms. 

A false value. Use this constant to test whether a data cube is false or to return a false value as a result. 

Use this predefined constant as the second argument in the FORMEMBERS function to loop through the dimension members in a forward direction. See FORMEMBERS. 

Use this predefined constant to test whether a value in a data cube is not available, or to return N/A as a result. 

The value of (3.1415926536), which is the ratio of a circle's circumference to its diameter. 

Use this predefined constant as the second argument in the FORMEMBERS function to loop through the dimension members in a reverse direction. See FORMEMBERS. 

A true value. Use this predefined constant to test whether a data cube is true or to return a true value as a result. 
Mathematical Operators
This table describes Analytic Calculation Engine's operators and their order of execution.
Symbol 
Mathematical Operation 
Order of Execution 

^ 
Exponentiation 
1 
* 
Multiplication 
2 
/ 
Division 
3 
+ 
Addition 
4 
 
Subtraction 
5 
Order of Precedence
If you use more than one kind of operator in a rule, you must understand the precedence that the analytic calculation engine follows with the operators. Precedence refers to the order in which the different operators are evaluated.
For an example of precedence,
the rule 3 + 2
* 4
evaluates as 11, not as 20. The analytic calculation
engine performs the multiplication of 2 and 4 before it adds the number
3 because multiplication has a higher precedence than addition.
You can use parentheses
to override the precedence of operators. For example, the rule (3 + 2) * 4
evaluates
as 20, because the analytic calculation engine first evaluates the
operation within parentheses. You can nest parentheses to exercise
more control of precedence; the operations within the inner sets of
parentheses are evaluated first. For example, the analytic calculation
engine calculates the rule (8 + (3 + 2) * 4) * (6
+ 7)
in the order described in this table.
Order of Execution 
Operation 
Resulting Value 

1 
3 + 2 
= 5 
2 
5 * 4 
= 20 
3 
8 + 20 
= 28 
4 
6 + 7 
= 13 
5 
28 * 13 
= 364 
The analytic calculation engine performs the multiplication of 5 * 4 before the addition of 8. The analytic calculation engine performs multiplication before addition unless you override this order of execution with parentheses.
Note: If you use parentheses, you must balance each opening parenthesis with a closing parenthesis. If you do not balance the parentheses, the analytic calculation engine generates an Unbalanced parentheses error. When this situation occurs, you must correct the rule.
Values
A value is a number or a text string. For example, the NET_PRESENT_VALUE data cube contains this rule: NPV(MONTHS, ANNUAL_DISCOUNT_RATE / 12, NET_REVENUE_BY_PRODUCT). In this rule, the value is 12.
Data Cube References
Use a data cube reference to refer to a specific data cube. For example, you can use data cube references to multiply the values of two data cubes and place the calculation totals in a result data cube. Using this example, the PROD_SALES data cube contains the following rule: UNIT_COST * UNITS_SOLD.
Member References
Use a member reference to refer to a dimension member to access its data or to perform a calculation. Use the following syntax to refer to a member:
[DIMENSION_NAME:Member]
For example, you could use this member reference to refer to the Hard Drives member from the PRODUCTS dimension:
[PRODUCTS:Hard Drives]
If an aggregate member and detail or leaf member share the same name, use the following syntax to reference the desired member:
[DIMENSION_NAME:NODE.Member]
Access an aggregate member.
[DIMENSION_NAME:DETAIL.Member]
Access a detail or leaf member.
Note: Navigation related functions such as PREV, NEXT, and PREVSELF operate on detail member names that are persisted in the main record. These functions do not use trees to determine the order of members.
See Understanding Dimension Members, Logic for Determining the Order of Members.
Blank Member References
You reference blank members in user functions by using the MBR2TEXT or TEXT2MBR builtin function with this string: " " (two quotation marks). Do not include spaces between the quotation marks. For example:
AT(Product, TXT2MBR(""), SALES)
Or
IF(MBR2TXT(Product) = "", X, Y)
Note: When blank members are mapped to date fields, they are written to the database as values of 1/1/1900.
Variables
When referencing variables in rules, you must always begin the variable reference with the & symbol, followed by the variable name.
Note: The variable name can only contain letters or numbers; it cannot contain spaces.
To set a value to a variable, use the following:
&Index := 1
The following formula sets the first character of an account number to a variable, and then uses that variable to set the account category:
&AcctCode := LEFT(MBR2TEXT(ACCOUNTS), 1);
CASE(&AcctCode = "1" : [ACCT_CAT:Assets],
&AcctCode = "2" : [ACCT_CAT:Liabilities],
&AcctCode = "3" : [ACCT_CAT:Income],
#DEFAULT : [ACCT_CAT: Expense]
)
Setting the value to a variable makes it unnecessary to repeat the expression for each condition of the CASE function, or to create an intermediate cube to hold the account code.
You can increment or decrement a variable with the INC statement:
INC(&Index);
DEC(&Index);
INC(&Profit, REVENUE);
DEC(&Profit, EXPENSE)
The lifetime of a variable is a single evaluation of the rule; the value of a variable is not preserved across multiple evaluations.
To insert a builtin function into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Select the place in the rule where you want to add the function.
Click the Paste Builtin Function button.
The Choose Builtin Function dropdown list box appears.
Use the scroll bar to scroll through the list of builtin functions.
Click the desired builtin function.
Analytic Calculation Engine pastes the builtin function and argument names into the rule bar.
For each argument:
Highlight the argument.
Replace the highlighted argument with the argument value.
Complete your work on the rule, and then:
Click the Accept Changes button to accept the changes.
Click the Exit Formula Mode button to keep the changes without validating the rule.
Click the Cancel Changes button to cancel the changes.
To insert a user function into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Select the place in the rule where you want to add the user function.
Click the name of the user function in the part browser.
Analytic Calculation Engine pastes the user function into the rule bar.
Note: If you enter a user function name that does not exist, the analytic calculation engine returns an error when validating the analytic model.
To insert a numeric or a text value into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
To insert a numeric value in a rule, enter the value (for example, 12).
To use a text value in a rule, enter the value and enclose it in double quotes (for example, "Smith").
Note: PeopleSoft recommends that you do not enter an assumption directly into a rule. Instead, you should create a data cube for the assumption and refer to the data cube in the formula. For example, do not calculate TAXES by multiplying INCOME by 0.38. Instead, create a data cube called TAX_RATE and enter 0.38 as its value. Then calculate TAXES by multiplying INCOME by TAX_RATE. Performing the procedure in this fashion simplifies the process of changing the assumptions and makes the analytic model easier to understand and audit.
To insert a data cube reference into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Place the cursor at the location of the rule into which you want to insert the data cube reference.
Perform one of these actions:
In the part browser, click on the data cube to which you want to refer.
Enter the name of the data cube.
Note: If you enter a data cube reference for a data cube that does not exist, the analytic calculation engine returns an error when validating the analytic model.
Select
and click on the data cube to which you want to refer.
To insert a dimension reference into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Place the cursor at the location of the rule into which you want to insert the dimension reference.
Perform one of these actions:
In the part browser, click the dimension to which you want to refer.
Enter the name of the dimension.
Note: If you enter a dimension reference for a dimension that does not exist, the analytic calculation engine returns an error when validating the analytic model.
Select
and click on the dimension to which you want to refer.
This topic discusses how to:
Enter a member reference into a rule.
Refer to one slice of a data cube.
Entering a Member Reference into a Rule
To enter a member reference into a rule:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Place the cursor at the position in the rule where you want to enter a member reference.
Select
The Choose Member Reference dialog box appears.
Click the dimension for which you want to enter a member reference.
The dimension and a generic member reference appears in the rule bar.
Highlight the word member.
Replace the word member with the name of the dimension member.
Referring to One Slice of a Data Cube
When you want to access particular values within a data cube, use member references to refer to a slice of the data cube.
To refer to one slice of a data cube:
Select
to access PeopleSoft Application Designer.After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the data cube whose formula you want to define or edit.
Enter the data cube in the rule.
For example, SALES, which uses the MONTHS, PRODUCTS, and REGIONS dimensions.
Enter a member reference.
For example, the East region from the REGIONS dimension.
The rule bar now displays
SALES [REGIONS.East]
. This rule returns SALES for the East region for all PRODUCTS and all MONTHS.Repeat step 3 to make the slice as small as you want.
A single value from the data cube is the smallest possible slice.
For example, to access SALES for the East region for the Hard Drives product for 2004/03, use the following rule:
SALES [REGIONS.East] [PRODUCTS.Hard Drives] [MONTHS.2004/03]
You can insert a blank line into a rule to enhance legibility. To insert a blank line into a rule, press Ctrl + Enter.
To insert a comment
into a rule, use the symbols <<
and >>
. This is an
example of a comment in a rule:
<< Loop through all products >>