Calculated Elements

Calculated elements use the user-defined formula to get the value, which can be based on dictionary source elements.

There are the following calculated element types:

Aggregating element.
Calculation of subtotals and totals.
Calculation of MDX expression to measure ADOMD cubes.

NOTE. Adding calculated elements is not available when global cache is used.

Any dimension element can be made calculated one. To do this, select the element on the side panel and select the Edit Formula item in the element's context menu. After the command is executed, the Expression Editor dialog box opens.

To edit a calculated element formula, select it on the side panel, table or in the Total Dimension list of the Edit Dimension dialog box:

Select the Element > Edit Formula item or the Edit Formula item in the element's context menu.
Click the Edit Formula button in the Edit Dimension dialog box.
Click the Edit Formula button in the Element group on the Hierarchy ribbon tab.

To edit the formula of dimension calculated element, the Expression Editor dialog box opens.

To set up calculation by edited data without preliminary data saving to the source, use setting up formula calculation.

NOTE. Calculated elements are calculated automatically by defined formulas, without the ability to edit them manually.

Calculation Chain

To change the order of formula calculation, use the Calculation Chain dialog box.

To display the Calculation Chain dialog box

The dialog box has the Aggregation Calculation and Totals Calculation sections. The formula calculation order can be changed within the Aggregation Calculation and Totals Calculation sections. The Totals Calculation section is always below the Aggregation Calculation section. Calculation of calculated elements can be set before these elements and between them. Calculated element formulas are added before the Aggregation Calculation section. Formulas are calculated as they are added, by default.

NOTE. Changing of formula calculation order in the Totals Calculation section may cause changing of totals result.

If the Aggregation Calculation section is not the first one, if the From Source Dimensions aggregation type is used, it will become the first in the calculation chain; the user is informed about changing the order of elements in the calculation chain, aggregation is executed first, then all the other elements based on aggregation results are calculated. Clicking the OK button applies the set aggregation type and changes the order of elements in the calculation chain.

The dialog box can be used to execute the following operations with formulas:

Use the and buttons to edit the order of formulas calculation.
On calculating formulas, empty values may be considered as zeros. To do this, select the Treat Empty Values as Zeros checkbox. This is important, for example, on calculating average values.
To calculate formulas by elements, which contain data, select the Calculate by Data Only checkbox.

TIP. Selecting the Calculate by Data Only checkbox speeds up formula calculation due to excluding empty cells from calculation.

To set up a formula, click the Edit button. The Expression Editor dialog box opens.
To delete the selected formula, click the Delete button.
To change formula calculation order taking into account their dependency, click the Default button. If calculation of one element depends on calculation of other element, calculation of the dependent element is located before in the calculation chain, clicking the Default button changes the order of these elements calculation: the dependent element will be calculated later.