Working with the Elements of Rules

Built-in 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 built-in 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:

1. The dimension for which you want to calculate the change.

2. The information for which you want to calculate the change.

3. 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.

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.

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.

See FIRST, MATCH.

Logical Operators

A logical operator determines whether a condition is true. The following table describes the logical operators.

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.

SALES > 50000 .AND. ADVERTISING < 10000

CASH_REMAINING < 1000 .OR. PROJECT_DONE

.NOT. IS_FIRST .AND. .NOT. IS_LAST

.NOT. (IS_FIRST .OR. IS_LAST)

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.

CASE(Condition 1 : Result 1, Condition 2 : Result 2,
 #DEFAULT : Default Result)

Mathematical Operators

This table describes Analytic Calculation Engine's operators and their order of execution.

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.

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.

See Understanding Design Time Rule Error Messages.

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.

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 built-in 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)

See Types of Dimension Members.

Variables

When referencing variables in rules, you must always begin the variable reference with the & symbol, followed by the variable name.

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.

Entering a Member Reference into a Rule

To enter a member reference into a rule:

1. Select Start > Programs > PeopleTools 8.5x > Application Designer to access PeopleSoft Application Designer.

2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

3. Select the data cube whose formula you want to define or edit.

4. Place the cursor at the position in the rule where you want to enter a member reference.

5. Select Edit > Paste Member Ref...

   The Choose Member Reference dialog box appears.

6. Click the dimension for which you want to enter a member reference.

   The dimension and a generic member reference appears in the rule bar.

7. Highlight the word member.

8. 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:

1. Select Start > Programs > PeopleTools 8.5x > Application Designer to access PeopleSoft Application Designer.

2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

3. Select the data cube whose formula you want to define or edit.

4. Enter the data cube in the rule.

   For example, SALES, which uses the MONTHS, PRODUCTS, and REGIONS dimensions.

5. 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.

6. 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]