To work with the tool in Foresight Analytics Platform 10, use the new interface.
In this article:
Adding, Moving and Deleting Calculation Algorithm
Setting Up Properties of Selected Calculation Algorithm
General Settings of Calculation Algorithm
Setting Up Link of Calculation Algorithm and Data Entry Form Parameters
Setting Up Data Entry Forms Binding to Algorithm
To calculate and control table area data, use created calculation algorithms.
NOTE. Selecting a calculation algorithm is available only if the Calculation Algorithms extension is installed.
To select calculation algorithms, use the Calculation Algorithms page of the Table Area Wizard dialog box:
Elements of the Calculation Algorithms page
To determine a calculation algorithm:
Add a ready calculation algorithm.
Set up properties of the selected algorithm:
Determine general algorithm settings.
Link data entry forms to algorithm for detailing formulas used in calculation algorithm.
TIP. To set up displayed columns in tables on the Properties of Selected Algorithm and Binding to Data Entry Forms tabs, see the Setting Up Tab Table section of this article.
After selecting calculation algorithms, finish table area creation by clicking the Finish button.
NOTE. If any errors occur on algorithm calculation, algorithm calculation is stopped. The changed data will not be saved to data consumers.
When working with a ready data entry form, the formula used for cell calculation will be displayed in the formula bar.
TIP. To speed up calculation of the selected algorithms, see the How to Speed Up Algorithm Calculation? section.
After the table area is set up, follow the step if required:
Select the ready calculation algorithm in the Calculation Algorithms area and move it to the Selected Calculation Algorithms area using the Add button or drag it.
Depending on the calculation execution option, move the algorithm to the tab:
To Change. Algorithms added to this tab will be executed when data will changed in data entry form. Calculation is executed in cells that use changed data in formulas. Cell values calculated by means of the connected calculation algorithm are displayed as changed data.
NOTE. To run data recalculation, select the Combine Queries on Calculation checkbox on the General tab of the Parameters dialog box. Recalculation is executed if modified data is input data for another table area, which data is calculated using the algorithm executed on modification. If the checkbox is deselected, data is recalculated only in one table area on the current data entry form sheet. Other table areas will not be recalculated.
To Save. Algorithms added to this tab will be executed only when data will be saved in data entry form; when data changes, the calculation will not be executed. The calculation is executed in sources by all data.
To set up order of calculation algorithms calculation, use the Move Up and Move Down buttons on the tab toolbar.
To delete the selected calculation algorithm:
Click the Delete button on the tab toolbar.
Select the Delete context menu item.
To set up properties of the selected calculation algorithm, select it in the Selected Calculation Algorithms area. Various settings are available depending on the algorithm type. The algorithm type is determined automatically. The type of calculation algorithm is specified in the Selected Calculation Algorithms area table in the Type column:
Calculation. The algorithm contains only calculation blocks. It can be added to the To Change and To Save tabs.
Control. The algorithm contains only control blocks. It can be added only to the To Change tab.
Calculation/Control. The algorithm contains both calculation blocks and control blocks. It can be added to the To Change and To Save tabs.
Determine general settings of calculation algorithm in the Selected Calculation Algorithms area:
If required deselect the checkbox next to the algorithm in the On column to confirm debugging og claculation algorithms work in data entry form. Checkboxes are selected for all algorithms by default.
Set up font, fill and other to format cells where control was applied. To do this, open the Formatting dialog box by clicking the Set Style button. For example, if data did not pass the control, cells with it will not be highlighted by color different from the color used for table area formatting. If formatting settings were set both in calculation algorithm and data entry form, they will be applied together. If the same settings were specified, the settings from data entry form will be applied. It is available on the Control calculation type on the To Change tab.
Set up changed data save locking in table area with applied control. To do this, click the Lock Saving of Changed Data in Table Area if Control Worked button. When saving changed data if the control was applied at least for one table area, the information message indicating that entered data must be changed is displayed. When executing operations resulting in table area refresh: saving, copying, refreshing, changing parameter values, closing, entry on data entry form approval; transposing and editing table area - the warning is displayed informing that changed data will not be saved because it does not satisfy control settings. If you confirm the operation, the changed data will be reset and the operation will be applied. When rejecting the operation the changed data remains in table area. It is available on the Control calculation type on the To Change tab.
Select the checkbox next to the algorithm in the Save Data column to save data to the source. When the checkbox is selected, data in cells calculated by the algorithm is saved to the source on saving data or saving data entry form. If data in cells is calculated by several algorithms, then data calculated by the last algorithm with enabled setting is saved. When the checkbox is deselected, data in cells calculated by the algorithm is not saved to the source but is saved to the form. To display the last saved source data in cells calculated by algorithm without saving data, disable algorithm calculation or delete it.
Select the type of calculation area creation that will be used on algorithm calculation from the Calculation Area drop-down list:
Selection intersection. The intersection of selection from algorithm by all dimensions with selection from table area will be used for calculation. Selected by default.
Calculation Period from Algorithm. The intersection of selection from algorithm by all dimensions, except the calendar one, with selection from table area will be used for calculation.
All Selection from Algorithm. All selection from algorithm without taking into account selection from table area will be used for calculation.
Select the checkbox next to the algorithm in the Expand Selection column to expand the range of loaded data based on the selection specified in the algorithm. It is available on the To Change tab. When the checkbox is selected, all values specified on algorithm setup will be taken into account in calculation and not only those displayed in table area.
NOTE. Selection expansion is not applied if a cube with configured dimension control.
Set up link between calculation algorithm parameters and data entry form parameters on the Properties of Selected Algorithm tab, by selecting:
Value Type. Determine values that are used on calculation algorithms:
Form Parameter. Calculation uses parameter value of data entry form.
Constant Value. Calculation uses a constant value.
Selection Type. Depending on the selected value type:
If the Form Parameter type is selected, choose the selection type in the drop-down list. Available values: initial, first element, last element, initial+children, initial+children (recursive), children, children (recursive)
If the Constant Value type is set, the selection type is unavailable.
Dictionary Schema. Select selection schema of dictionary used as algorithm parameter in the drop-down list. If selection schema is not set for dictionary, the list will be empty.
NOTE. Dictionary selection schema has a greater priority than selection type.
Value. Depending on the selected value type:
If the Form Parameter type is selected, determine parameter or form parameter attribute by selecting it in the drop-down list.
If the Constant Value type is selected, set the constant value.
NOTE. Links can be set up only via the Selection and Hierarchy algorithm parameters.
If calculation algorithm and data entry form are built on the same source, the algorithm parameters will be automatically linked to the data entry form parameters. Default values:
Value type: Form parameter.
Selection type: Source.
Dictionary schema: undefined.
Value: parameter built on the same dictionary.
Link data entry forms to the algorithm to drill down formulas used in calculation algorithm, by using the Binding to Data Entry Forms tab:
NOTE. The Binding to Data Entry Forms tab is not active for the Control algorithm type. If calculation algorithm is of the Calculation/Control type, the tab displays only calculation block and aggregation block consumers.
To do this, specify:
Source. The list displays objects that are consumers in calculation algorithm.
Data Entry Form. Select the form to be bound in the drop-down list or click the Search button and enter the name. The list is represented by filtered repository object tree where data entry forms and folders containing data entry forms are specified. To unbind the form, click the Clear button.
NOTE. Make sure that the Load Only Active Sheet checkbox is selected for bound multisheet data entry form to speed up form opening.
Bound Form Parameters. The list contains parameters of the form selected in the Data Entry Form box.
Source Form Parameters. The list contains source form parameters. For parameters of the bound form specify source form parameters, which values will be sent to the bound form parameters. It is not necessary to bind all parameters. Select the parameter in the drop-down list or click the Search button and enter the name.
The bound form opens on viewing formula description. If source form parameters transfer is not set up for all bound form parameters, the dialog box to enter parameter values opens on opening the bound form.
When editing data entry forms created in previous versions of Foresight Analytics Platform, calculation algorithms added to control entered data will be disabled. When opening such a data entry form for edit, the message is displayed indicating that the Control type calculations are disabled and that changes should be made in the algorithm or that algorithm connection settings in data entry forms must be changed.
The number of displayed columns and the order of their position in table can be set up:
To change one column, select or deselect the checkbox next to the column name in the table context menu, the position order stays the same.
To change several columns and to change their order, open the Select Columns for Display dialog box, by selecting the Select columns table context menu item:
To change the number of displayed columns:
Select or deselect the checkbox next to the column name.
Select all columns by clicking the Select All button.
Deselect all columns by clicking the Deselect All button.
To reorder columns, select the column and use the Move Up and Move Down buttons.
See also:
Getting Started with the Interactive Data Entry Forms in the Web Application | Building Data Entry Form | Working with Ready Data Entry Form