Cross dimensional member references

Cross-dimensional member references enable you to qualify line references with references to members from other dimensions. This is the syntax:

[line name] of [dimension name]:[member name] of [dimension name]:[member name] …

These rules apply:

  • The line name, the dimension names, and the member names must all be enclosed in square brackets. These items are all space- and case-sensitive.
  • The Of clauses ("of [dimension name]:[member name]") are optional. If none are given, the reference is a simple line reference.
  • A line name must be included in every reference and it must be the first item in the reference. The line name is not preceded by a dimension name.
  • You can specify as many Of clauses as necessary to qualify a reference. Any given dimension name can be displayed only once in a reference. For example, [Util. Exp.] of [Unit]:[MI] of [Unit]:[CA] is not allowed.
  • The following dimensions are not allowed in cross-dimensional references: Accum Method, Currency, Rate, RateSet, and AdjustedState. All other dimensions are allowed.
  • A formula can reference any member in any hierarchy.
  • Cross-dimensional references cannot be used on the left side of an equation.
  • You can specify relative offsets for period and year dimension members in a cross-dimensional reference. A relative offset is an integer that specifies a member relative to the one currently being processed. Only backward references such as a previous period or year are allowed. Thus, if you use a relative offset, it must be less than zero. For example, to reference a specific period of the previous year, use:

    Of [period]:[December] of [year]:[-1]

    To reference the previous year, use:

    Of [year]:[-1] // period is not given

    To reference the previous period of the current year, use:

    Of [period]:[-1] // year is not given

    If a relative reference references a period or year that does not exist on the database, then the result of the reference is null and is treated as zero in the calculation. Because P0 is considered to be the first period on the database, any attempt to reference a period before P0 will return null.

  • Using the Of function in a formula causes the consolidation engine to get that value from translated periodic data. To avoid extra translations occurring, these formulas often require an If not cons condition on them instead of If rpt.