Remember that element summation is unavailable for some source cube dimensions.
To execute summation, the following conditions must be satisfied:
For a dimension:
It should be table.
It should have a unique index.
Indexes of dimension blocks must be linked to one attribute only.
For a cube:
Cube values must be stored in the same table.
If any of these conditions is not satisfied, calculation is not executed.
NOTE. It is recommended to use recursive dimensions.
See below examples of various summation methods in formulas of multidimensional calculation on database server for cubes having one or two recursive dimensions.
Multidimensional calculation uses the same cube as the source one and the destination one. This cube consists of a fact and a recursive dimension:
Before calculation the cube contained the following data:
The following formula is defined in the multidimensional calculation formula editor:
| Destination cube coordinate | Specified formula |
| b1 | b1 |
where b1 is an element selected in dimensions. The expression is defined based on the existing cube fact.
The following are various types of summation using the same input data and the defined formula.
Summation with parent. Open the context menu for the term in the formula field and select the Sum Up > Child Elements with Parent checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. As you can see, the result uses the value of the b1 dimension element, which is a parent.
Summation without parent. Open the context menu for the term in formula field and select the Sum Up > Child Elements without Parent checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. As you can see, the result uses the value of the b1 dimension element, which is a parent.
By mask. Open the context menu for the term in formula field and select the Sum Up > By Mask checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. To understand the calculation, look at the dimension element identifiers. The b1 element has the 2 identifier, therefore the elements with the identifiers starting with 2 were used (these are b2 and b4), the value of the element defined in the formula was also used for calculation (this was b1). This implies: 100 + 10 + 30 = 140.
NOTE. Remember that identifiers are checked for all dimension elements.
Multidimensional calculation uses the same cube as the source one and the destination one. This cube consists of a fact and two recursive dimensions (dimension1 is on the left, dimension2 is on the right):
Before calculation the cube contained the following data:
The following formula is defined in the multidimensional calculation formula editor:
| Destination cube coordinate | Specified formula |
| b1|a1 | b1|a1 |
where b1 and a1 are elements selected in dimensions. The expression is defined based on the existing cube fact.
The following are various types of summation using the same input data and the defined formula.
Summation with parent. Open the context menu for the term in the formula field and select the Sum Up > Child Elements with Parent checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. The parent elements are located in the B1:B4 and C1:E1 cells, and the child elements are located in the C2:E4 range. As you can see, the result includes all parent and child values.
Summation without parent. Open the context menu for the term in formula field and select the Sum Up > Child Elements without Parent checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. The parent elements are located in the B1:B4 and C1:E1 cells, and the child elements are located in the C2:E4 range. As you can see, the parent values have not been used to calculate the result.
By mask. Open the context menu for the term in formula field and select the Sum Up > By Mask checkbox. Save the formula. Select the Update Calculating Elements item in calculation options dialog box in the Before and During Calculation box and calculate the cube. After calculation the cube contains the following data:
The calculated value is underlined in red. To understand the calculation, look at the dimension element identifiers. The b1 element has the 2 identifier, therefore elements with identifiers starting from 2 were used for the dimension1 (these are b2 and b4). The a1 element has the 1 identifier, therefore elements with identifiers starting from 1 were used for the dimension2 (these are a2 and a4). The result also includes the values of child elements, which are at the intersection of the parent elements meeting the condition (matched identifiers). The value of the element defined in the formula is used for calculation (b1|a1). This implies: 1000 + 100 + 300 + 10 + 30 + 1 + 1 + 1 + 1 = 1444. The values included in the result except for b1|a1 are underlined in green.
See also: