Multidimensional Calculation Formula Editor on Database Server

To edit multidimensional calculation formulas on DB server, select the Edit Formulas item in the context menu of multidimensional calculation in the object navigator. The formula editor opens:

The dialog box of multidimensional calculation formula editor at database server includes several areas, fields, and lists:

The Destination Dimensions and Source Dimensions areas display non-fixed dimensions of destination cube and source cube respectively. One of the cube dimensions is active. A full list of elements is shown for this dimension. Other dimensions are displayed below the tree of active dimension's elements. Each dimension has a drop-down elements list with the button, clicking which activates the dimension.

NOTE. Hold down the CTRL or SHIFT keys to select multiple elements in active dimensions. Multiple selection is used to select only those cube coordinates that are to be calculated. The detailed description is given in the Calculation Options section. Multiple selection in active destination dimension is used to add to expression a sum of coordinates corresponding to selected elements.

Specify the expression for the selected destination cube coordinate in the Expression box.

The Functions list includes all functions, which can be used to define formulas.

The Period list includes all formula actual periods for the selected destination cube coordinate.

NOTE. The Period list is displayed if time-dependent formulas are enabled for multidimensional calculation.

Creating a Formula

IMPORTANT. Before creating a formula make sure that source cube contains data. If multidimensional calculation formula does not depend on the source data, and the source cube does not contain data, calculation is not executed. To execute calculation, the table must contain at least one row.

To create a multidimensional calculation formula, execute the following operations:

NOTE. Formula actual period can be set if time-dependent formulas are enabled for multidimensional calculation.

The Period box includes all formula actual periods for the selected destination cube coordinate. For each period, the concrete formula for value calculation is specified.

To add a period:

Click the Add button.
Select the Open context menu item.

A dialog box opens where a period can be created:

NOTE. If the specified period overlaps the current ones, it is prompted to correct the selected period.

To create a new period with the formula already specified for the period, copy an existing period. To do this, select the Create Copy context menu item. Set new start and end period dates in the dialog box that opens.

To edit the selected period, select the Edit context menu item. A dialog box equal to that used to create a period opens.

To delete the selected period:

Click the Delete button.
Select the Delete context menu item.

After one of these actions is executed, the action confirmation dialog box opens.

If the Formula Table page of the multidimensional calculation of database server wizard contains the Formulas Depend on Time checkbox selected, and the Calendar destination dimension is not fixed in the source cube, data is obtained by formula actual period on creating a formula with configured actual period. For example: if the 2016 element is selected in the Calendar dimension, and the 2014-2015 actual period is set in the formula, data that correspond to 2014-2015 years is extracted. Values for 2016 are not calculated, though calculation formula is set for this year.

Thus, to correctly extract data:

If it is required to calculate by different periods with different formulas, before calculating fix the Calendar dimension and set calculation period.
If it is required to add different formulas for each point of the Calendar dimension, before calculating fix the Calendar dimension and use calculation period.

To add a source cube coordinate to the expression, for destination cube coordinate:

Double-click an element of active source dimension.
Select the Add into Formula item in the context menu of the active source dimension.

After this the current position of the Expression box displays expression element corresponding to source cube coordinate.

To add a sum of source cube coordinates into the expression:

Make multiple selection in the source dimension.
Select the Add into Formula item in the context menu of the selected source cube coordinates.

After executing all the actions, a sum of coordinates that correspond to the selected dimension elements, is added to the expression.

To add special functions:

Select a function in the Functions list and double-click its name. The function is added to the current position of the expression.
Enter the function name from the keyboard to the Expression box.

Select the Calculation > Save Formulas main menu item.
Click the Save Formulas button.
Press CTRL+S.

Select the Calculation > Calculate main menu item.
Click the Calculate button.
Press F9.

Executing any of these actions opens the Calculation Options dialog box.

Operations in Formula Editor

To search for elements of destination dimension and source dimension:

Select the Find item in the dimension's context menu.
Press CTRL+F.

The standard search dialog box opens:

Click the More button to adjust advanced search parameters. Advanced search parameters are available:

Match Case. If the checkbox is selected, letter case specified in the search string is considered in the search. For example, if "January" is entered into the search string, the elements containing the text "January" or "JANUARY" are not displayed.
Match Whole Word. If the checkbox is selected, only the elements, corresponding attributes of which contain the whole string matching search string, are displayed.
All Word Forms. If the checkbox is selected, word declension is ignored when searching elements, that is, whole words defined in the search sting are searched for. For example, if "region" is entered into the search string, the search results show all elements which have attribute values containing the word "region". If the checkbox is deselected, the elements, attribute values of which include the words "region", "regions", and so on.

NOTE. When the Match Whole String checkbox is selected, the All Word Forms checkbox is disabled.

Attributes. In the Attributes area select checkboxes next to attribute names, by which search should be executed. By default, all attributes are selected. It is also possible to select and deselect all attributes using the Select All and Deselect All buttons.

Click the Find Next button to navigate the found elements one by one.

Click the Find All button to display all search results.

Summation of element values by all free (not fixed) dimensions can be set up for coordinates added to the formula. The summation is executed in the course of calculation. To execute setup, select the Sum Up item in the context menu of expression element and select one of the radio buttons:

Never. Values are never summed.
Child Elements with Parent. This option sums up child elements, including the value of the parent. If the checkbox is selected for the expression element, it is marked with the "∑" summation symbol in the formula.
Child Elements without Parent. This option sums up child elements, excluding the value of the parent. If the checkbox is selected for the expression element, it is marked with the "ς" summation symbol in the formula.

NOTE. When summing child elements, only values of direct child elements are summed, that is, of the elements positioned one level down. Elements positioned two or more levels down are ignored.

By Mask. This option sums values of the elements that have matching identifiers (codes) in the dimension. Matching means that the elements have the same code value and position. If the checkbox is selected for an expression element, this element is marked with the "M" symbol in the formula. Example of summation by mask.

Dimension:

a1 (code 1 - parent)

a2 (code 10 - child a1)

a3 (code 11 - child a1)

a4 (code 21 child a1)

b1 (code 2 - parent)

b2 (code 20 child b1)

b3 (code 12 child b1)

If the following formula is written for the a1 element:

M(a1)(fact)

Calculate the cube and get the sum for the following elements:

a1 + a2 + a3 + b3

NOTE. These context menu items are not available, if this operation is prohibited for the calculation on the server: the Disable Summation checkbox on the Structure wizard page is selected.

When the Sum menu item is applied to several selected coordinates, this summation type is enabled for all selected coordinates for which summation is available (if summation is not disabled in respective source cubes).

For details see the Summation Features and Examples section.