Calculated Elements

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

To edit calculated element formula, execute the same operations as when creating a calculated element.

The following operations are available for express reports and analytical areas:

To add a new calculated element in express reports and analytical areas:

Select the Element < Add New Element item in the context menu on the side panel or in the Total Dimension list in the Edit Dimension dialog box.
Click the Add button in the Edit Dimension dialog box.
Click the Add New Element button in the Element group on the Hierarchy ribbon tab.

After the command is executed, the Expression Editor dialog box opens.

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

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

To display the Calculation Chain dialog box, click the Calculation Chain button on the Designer ribbon tab. The button becomes active when calculated elements and/or formulas are added to cells.

The dialog box has the Aggregation Calculation and Totals Calculation sections. 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.
When 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, when 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 formula 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.

Expression Editor

To set up calculated element formulas in regular reports and information panels, use the expression editor:

Operands are displayed on the operands panel in the expression editor:

All dimensions of the cube selected as a data source are used as operands. Elements of any of the dimensions can be inserted into the formula.

To insert an operand into the formula area, execute one of the operations:

Using the operands panel:

Select the operand and click the Insert into Formula button.
Double-click the operand.

Using the formula area:

Enter operand name in the formula area.
Click in the formula area where the operand should be inserted, press CTRL+SHIFT and select the required operand on the popup panel.

To quickly search for an operand, start typing part of its name or full name in the search string. After executing the operation, the operands, which names satisfy the search conditions, are displayed on the operands panel.

To hide the operands panel, click the Hide Operands Panel button; to display the panel, click the Show Operands Panel button.

Functions and statements are displayed on the functions and statements panel on corresponding tabs:

To insert a function or statement into the formula area, select one of the operations:

Using the functions and statements panel:

Select the function or statement. The description panel opens:

Click the Insert into Formula button.

Double-click the function or statement.
Drag the function or statement to the formula area.

Using the formula area:

Insert the function or statement into the formula area.
Click in the formula area where the function or statement should be inserted, press CTRL+SHIFT and select the required function or statement on the popup panel.

To quickly search for a function or statement, start typing part of its name or full name in the search string. After executing the operation, the functions or statements satisfying the search conditions are displayed on the functions and statements panel.

To hide the functions and statements panel, click the Hide Functions and Statements Panel button; to didplay the panel, click the Show Functions and Statements Panel button.