Measures That Refer to Other Cells
It is often useful to refer to the value in a different cell of the pivot table. To do so, you can use the %CELL and %CELLZERO functions. Each of these functions returns the value of another cell of the pivot table, by position. If the given call has no value, %CELL returns null; in contrast, %CELLZERO returns zero.
These functions have many uses. For one example, you can use %CELL to calculate a running total (in this case, the cumulative inches of rainfall):
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
