Element Sets

The content of the parenthesis given after the function name, is the collection of cells, called the ‘element set’, that is fed to the aggregation function to produce the result.

There are three cellset functions that can be specified here, two of which are just a shorthand for the generic one. The cellset functions take an X range, a Y range and a measure range. The measure has been split out to make it easier to select which measure should be included, when the number of cube measures or custom measures added varies. (More on this later, under Ranges -- Measure references).

Cell Set Function	Cell Set Function Description
General Cells Function: cells([x range] , [y range], [measure range])	Cells is the generic cell-set function which specifies a range on the x-axis and one on the y-axis in the grid plus which measure range to use.
Column Cells Function: cols([x range], [measure range])	Cols is a specialized and simplified cell-set function that only needs two arguments since the ‘same row’ is implied. Cols is a shorthand function for: Cells([x range], [same row], [measure range])
Rows Cells Function: rows([y range], [measure range])	Rows is a specialized and simplified cell-set function that only needs two arguments since the ‘same col’ is implied. Rows is a shorthand function for: Cells([same column], [y range], [measure range])

Rounded Rectangle: Comment, Special Shorthand Rules for Element Set Expressions:
§ If only one cells() function is needed to specify the cell range, its name and parenthesis can be omitted.
§ Similarly, if the sum of an element set is provided by only one cells(), cols() or rows() function is desired, the sum() part can be omitted. However, You cannot omit both sum() and cells().

Example formulas using Aggregation functions and Element Set (cells) functions are shown below.

Example Formulas using Aggregation & Element Sets	Example Description
avg(cols(d1,m1), cols(d-1,m1))	This formula Averages the first measure in the first and the last columns of the cross-tab table on the same row as where this expression will return its resulting average value.
cols(d-1,m1) - cols(d-2,m1)	This formula computes the Difference between the measure-value in the last two columns for the same row.
sum(d-1,0,m1) - sum(d-2,0,m1)	This formula computes the Difference between measure-value in the last two columns for the same row. Note: The above example illustrates these two special shorthand rules: § If only one cells() function is needed to specify the cell range, its name and parenthesis can be omitted. § Similarly, if the sum of an element set is provided by only one cells(), cols() or rows() function is desired, the sum() part can be omitted. However, You cannot omit both sum() and cells().