Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

151

152

153

154

155

156

157

158

159

160

Chapter 10 Creating Rules, Formulas, and User Functions

If an aggregate member and detail/leaf member share the same name, use the following syntax to reference the

desired member:

• [DIMENSION_N

AME:NODE.Member]

Access an aggre

gate member.

• [DIMENSION_NA

ME:DETAIL.Member]

Access a detai

lorleafmember.

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 m embers.

See Chapter 9, “Creating Hierarchies,” Understanding Dimension Members, page 73 and Chapter 9, “Creating

Hierarchies,” Lo gic for Determining the Order of Members, pag e 78.

Blank Member R

eferences

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)

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.

See Chapter 9,

“Creating Hierarchies,” Types of Dimension Members, page 73.

Variables

When referencing variables in rules, you must always begin the v ariable 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 c haracter of an account number to a variable, and then uses that variable to

set the account category:

&AcctCode := LEFT(MBR2TEXT(ACCOUNTS), 1);

CASE(&AcctCod

e = "1" : [ACCT_CAT:Assets],

&AcctCode = "2" : [ACCT_CAT:Liabilities],

&AcctCode = "3" : [ACCT_CAT:Income],

#DEFAULT : [ACC

T_CAT: Expense]

)

Setting the val

ue 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:

INC(&Index);

DEC(&Index);