Reference Type

Notation

Syntax Example

Description

Relative to Current

An integer.

0 = current element,

n < 0: nth previous element,

n > 0: nth next element.

cols(1,m1)

cols(-1,m1)

Refers to the next element.

Refers to the previous element.

Note: Calculated elements cannot refer relatively to data elements and vice versa.  Use data or calculation references instead.

Calculations

‘c’ and a positive integer.

‘c1’: first calculation,

‘c2’: second, etc.

cols(c1,m1)

Refers to the first calculation.

Note: The measure range is not used when referring to calculations.

Data Reference

‘d’ and an integer.

n < 0: ‘backward’, e.g.

‘d-1’: the last element,

‘d-2’: the second to last.

n > 0: ‘forward’, e.g.

‘d1’: the first element,

‘d2’: the second element.

cols(d1,m1)

cols(d-1,m1)

Refers to the column of the first value of the dimensions on the X axis.

Refers to the column of the last value of the dimensions on the X axis.

Default Value

An integer placed as last parameter in a reference.

cols(d1,m1,0)

Refers to the default value displayed when the range is out of reach (e.g., it refers to a column that is not present).

Instead of displaying 'undefined' the default value is displayed.

Dimension value

‘@’ and the dimension values in double quotes.

cols(@”[2008].[Q1]”, m1)

Refers to the 2008 Q1 value, if present under the current criteria.

Note: When referencing absolute dimension values across levels, level scope modifiers are needed.
(e.g., if both totals and member values are needed in the syntax example it looks like this: cols(@”[2008].[Q1]”(l1), m1) ). 

Also notice that the hierarchy must be expanded to the referred level.

Dimension reference

‘@@’ and the dimension references in double quotes.

cols(@@”[Period].[Quarter].&[1]”,m1)

Refers to the value for the member with key value "1" in the Period Quarter level, if present under the current criteria.

Note:  A list of key values may also be referred to in this way: cols(@@”[Period].[Quarter].[1]”;@”[Period].[Quarter].[3]”, m1)