%CELL (MDX)

Returns the value of another cell in a pivot table, by position. This function is an InterSystems extension to MDX.

Returned Type

This function returns a number or a string.

Syntax and Details

%CELL(relative_column_position,relative_row_position)

Where:

relative_column_position is an integer. Use 0 for the current column, –1 for the previous column (the column to the left), 1 for the next column (the column to the right), and so on.
relative_row_position is an integer. Use 0 for the current row, –1 for the previous row (the column above), 1 for the next row (the column below), and so on.

The system returns the value of the given cell, or null if the cell has no value.

The system evaluates %CELL after resolving the rest of the query; this means that you cannot use this function within an expression used by another function.

Example

The following example displays rainfall data and cumulative rainfall data for a given span of time:

SELECT {MEASURES.[Rainfall Inches],%CELL(-1,0)+%CELL(0,-1)} ON 0, {dated.year.1960:1970} ON 1 FROM cityrainfall
 
                     Rainfall Inches           Expression
 1 1960                        177.83               177.83
 2 1961                        173.42               351.25
 3 1962                        168.11               519.36
 4 1963                        188.30               707.66
 5 1964                        167.58               875.24
 6 1965                        175.23             1,050.47
 7 1966                        182.50             1,232.97
 8 1967                        154.44             1,387.41
 9 1968                        163.97             1,551.38
10 1969                        184.84             1,736.22
11 1970                        178.31             1,914.53

Notice that the default label here is Expression. You can use %LABEL to provide a more suitable label. For example:

SELECT {MEASURES.[Rainfall Inches],%LABEL((%CELL(-1,0)+%CELL(0,-1)),"Cumulative Inches")} ON 0, 
{dated.year.1960:1970} ON 1 FROM cityrainfall
 
                     Rainfall Inches    Cumulative Inches
 1 1960                        177.83               177.83
 2 1961                        173.42               351.25
 3 1962                        168.11               519.36
 4 1963                        188.30               707.66
 5 1964                        167.58               875.24
 6 1965                        175.23             1,050.47
 7 1966                        182.50             1,232.97
 8 1967                        154.44             1,387.41
 9 1968                        163.97             1,551.38
10 1969                        184.84             1,736.22
11 1970                        178.31             1,914.53

%CELL (MDX)

Returned Type

Syntax and Details

Example

See Also