Multidimensional Calculation Formula Editor

In the Destination Dimensions and Source Dimensions areas, non-fixed dimensions of destination cube and source cube are displayed respectively. One of the cube dimensions is active. A full list of elements is shown for this dimension. Other dimensions are displayed under 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.

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

To add a period:

• Click the Add button.

• Select the similarly named 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 similarly named context menu item.

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

Calculation Period Setup Features

If the Formulas Depend on Time checkbox is selected on the Formulas Table page of the multidimensional calculation on DB server creation and editing wizard, and the Calendar destination dimension is not fixed in the source cube, on creating a formula with set actual period, data is extracted by the formula 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:

1. Make multiple selection in the source dimension.

2. 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 Calculate > Save Formulas main menu item.

• Click the Save Formulas button.

• Press CTRL + S.

NOTE. If on creating a formula .NET units functions, that are stored in Foresight Analytics Platform, are used, they will be empty on opening objects in earlier platform versions.

• Select the Calculate > Run main menu item.

• Click the Calculate button.

• Press F9.

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

To search for elements of destination dimension and source dimension:

• Press CTRL+F.

• Select the Find item in the dimension's context menu.

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 field is considered in the search. For example, if "January" is entered into the search field, 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 field 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 field are searched for. For example, if "region" is entered into the search field, 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.

To view formula element in the source dimension, select the View in the Source item in the context menu of the selected expression element.

After the command is executed the corresponding element is shown in the source cube. On executing the command the tree of elements is scrolled to display the searched element in the middle of the list.

To select formula elements in the source dimension, select the Select All Elements in Source item in the context menu of the selected expression element.

NOTE. The item is available if the formula includes terms contained in the current source cube.

After executing this command, elements of the currently expanded tree of the destination dimension are defined and highlighted for all formula elements related to the current source cube.

The Expression box enables the user to work with clipboard:

• To copy the selected formula element to clipboard, select the Copy context menu item.

• To move the selected formula element to clipboard, select the Cut context menu item.

• To paste the expression element from clipboard to the formula, select the Paste context menu item.

If a destination cube has parameters, it can be reopened with other parameters. To do this, select the Calculation > Change Parameter Values main menu item. The Determine Parameter Values dialog box opens. If the destination cube is not parameterized, the cube is reopened.