Formula Syntax for Aggregate Storage Databases

When you create member formulas for aggregate storage outlines, observe the following rules:

• Enclose member names in brackets ([]) if they meet any of the following conditions:

  • Start with a number or contains spaces; for example, [100]. Brackets are recommended for all member names, for clarity and code readability.

  • Are the same as an operator or function name.

  • Include a nonalphanumeric character; for example, a hyphen (-), an asterisk (*), or a slash (/).

  Note:

  In formulas, member names starting with $ or & must be enclosed in quotation marks as well as brackets. For example, $testmember would be expressed in the formula as ["$testmember"]/100

• Use the IIF function to write conditional tests with a single else condition. The syntax for the IIF function does not require an ELSEIF keyword to identify the else condition nor an ENDIF keyword to terminate the statement. You can nest IIF functions to create a more complex formula.

• Use the Case, WHEN, THEN construct to write conditional tests with multiple conditions.

• Be certain that tuples are specified correctly. A tuple is a collection of members with the restriction that no two members can be from the same dimension. Enclose tuples in parentheses; for example, (Actual, Sales).

• Be certain that sets are specified correctly. A set is an ordered collection of one or more tuples. When a set has multiple tuples, the following rule applies: In each tuple of the set, members must represent the same dimensions as do the members of other tuples of the set. Additionally, the dimensions must be represented in the same order. In other words, all tuples of the set must have the same dimensionality.

  See also MDX Sets and Tuples.

  Enclose sets in braces, for example:

  { [Year].[Qtr1], [Year].[Qtr2], [Year].[Qtr3], [Year].[Qtr4] }