The Measure Expressions panel allows you to add, edit or remove measure expressions. It is these measure expressions that you see listed on the left side of the Cube.
The simplest expression is just a single measure. To do this, click the Add button and give your measure expression a name. Then in the Expression field enter the name of a measure in square brackets, eg. [Sales]. The square brackets are optional here, but are recommended for all measure references. They are essential for measures where the name includes spaces, or special characters such as -, + etc. which would otherwise be treated as arithmetic operations.
More complex expressions can include mathematical operations such as +,-,*,/ to relate several measures together. Using the example introduced above, the expression string would be [SalePrice]-[CostPrice].
It is also possible to construct more complex expressions using an extension of the measure syntax. Complex measures support the following prefixes:
[ParentCol:SalePrice] [ParentRow:SalePrice] [RootCol:SalePrice] [RootRow:SalePrice] [PreviousCol:Dim:SalePrice] [PreviousRow:Dim:SalePrice]where SalePrice is the name of the measure and Dim is any dimension used higher in the cube (explained below).
To understand how these work, you need to think of how a cube is constructed. A cube consists of two trees. One for each dimension. Every cell references two tree nodes - one from the row tree and one from the column tree. These nodes will be leaf nodes, or parent nodes depending on whether we are looking at totals.
Each tree node defines a set of records. Each cell is therefore the intersection of two sets of records. With simple measures you can perform actions on the selected set of records - eg. Sum, Average, Max, but you can't refer to other cells. With complex measures you can "walk" the tree and compare your values with those of other cells - eg. what is the difference in sales this month and last.
So far, these have been walking up the ancestors of the tree. With time axes it is useful to jump across the siblings in the tree. This is where PreviousCol and PreviousRow come in.
Let us say I have a breakdown of sales by Employee for each [Year][Month][Day][Employee] in the column axis. The row axis may be broken down by Product.
PreviousCol is clever... If you ask for [PreviousCol:Month:Count_unitsales] then we walk back up the tree until we reach Month. We then step across the tree to the previous sibling (which is the previous Month, eg. from March->February). We then walk back down the tree following the same values. Therefore starting with the cell representing [2009][March][1][Jon] we walk up to [2009][March], cross to [2009][February] and then walk down the same values to [2009][February][01][Jon]. This gives us the value of Jon's sales on 1st Feb.
Therefore you can use an expression like: [Count_unitsales] - [PreviousCol:Month:Count_unitsales] to work out the difference in sales from the same day last month. Of course for a monthly value you would just leave out the day dimension. This approach is clever enough to work across years etc. provided they are "previous" - for example the previous column to [2009][January] will be [2008][December] if you are using PreviousCol:Month. If you use PreviousCol:Year then you are comparing with last year.
Finally, you can choose the format that will be used to display the result of all these calculations in the cells of the cube.