On working with table it is available to set up calculation by edited data without storing the data to the source. When the setting is enabled, data can be edited without saving to the source.
Setting of edited and not saved data calculation is used for:
It is available to enable calculation by edited data in ribbon:
In regular report for data slice on the Slice tab.
In express report for sheet on the Data tab.
In dashboards for block on the Data tab.
The following menu items are available in the Formula Calculation drop-down menu item:
For the current sheet/block/slice.
For all sheets/blocks/slices.
NOTE. By default setup of formula calculation by edited data is disabled. When the setting is disabled, calculation is performed only after data is saved to the source.
The selection of the For the Current Sheet/Block/Slice menu item enables setting of the calculation by edited and not saved data for the current sheet/block/slice.
If the For the Current Sheet/Block/Slice checkbox is not selected for all sheets/blocks/slices, then the intermediate state will be selected for the For All Sheets/Blocks/Slices checkbox.
The selection of the For All Sheets/Blocks/Slices menu item enables settings of the calculation by edited and not saved data for all sheets/blocks/slices and the For the Current Sheet/Block/Slice checkbox is automatically selected for all sheets/blocks/slices.
When the For All Sheets/Blocks/Slices checkbox is deselected, the calculation by edited data is automatically disabled for all report sheets/blocks/slices and the For the Current Sheet/Block/Slice checkbox is deselected.
Formula calculation by edited data setup determined in dashboards affects the added reports too. If calculation by formulas on edited data is enabled in dashboards and data is changed in the table or in the chart, the element formulas are recalculated.
The example of the formula calculation setup is created basing on express report. On creating a regular report or a block in the dashboards, actions will be analogous.
To use formula calculation setup, as shown in the example, without preliminary data saving in express report, follow the steps:
Create an express report, select data source and determine selection.
Click the Calculation by Formulas item in the Data ribbon tab and select the For the Current Sheet item in the drop-down menu.
Select the Column Totals item on the Home ribbon tab, select the Sum item in the drop-down menu. After the step is executed, the table will contain totals by hierarchies - Amount (District), totals by levels - Amount (level) and the grand total. Data from table is summed in the row cells.
Add a new calculated element named Mean Value for Volga Federal District for the Territories dimension with formula:
Average({Republic Chuvachiya[t]},{Perm Krai[t]},{Administrative Region Nizhny Novgorod[t]},{Administrative Region Orenburg[t]},{Administrative Region Penza[t]},{Administrative Region Samara[t]},{Administrative Region Saratov[t]})
To edit the formula of dimension calculated element, the Expression Editor dialog box opens. After the step is executed, the Mean Value for Volga Federal District new calculated element which calculates mean total value for several regions of Volga Region for all years in the Territory dimension is added.
See also:
Working with Data Table | Working with Table Data | Calculated Elements