Writeback Formatting, Cell Locking and Data Validation
When writeback is enabled for a grid, it is possible to use the format sheet as well as additional Custom Format Areas to format and lock the grid cells.
Contents
[hide]Enabling writeback for a grid
In an FluenceXL grid, data entry mode is turned on by ticking the ‘Enable Writeback’ box in the Grid Properties window.
If you’re using FluenceXL, you also need to enable data entry mode at the workbook level from the FluenceXL\Options\Workbook Options menu item.
Please refer to the writeback page for further details.
Basic data entry formatting
Highlighting cells
You can highlight writeable data cells by:
1. Ticking ‘Highlight Data’ box in the Grid Properties window:
2. Defining a format for the ‘Writeable Member’ data cell in the FluenceXLFormats sheet:
Here is the result:
You can also provide visual feedback for the user during the data entry process by highlighting the cells that were changed and the ones that were impacted:
1. Select ‘Show Updates’ under refresh type after writeback
2. Define a format for the changed cells (‘Written Cell’) and the impacted ones (‘Summary written cell’)
Here is the result:
Using offline data entry mode
Using the offline data entry mode is recommended for performance reasons. When this mode is activated, the journal of pending changes can be viewed by clicking the ‘Manage…’ button in the grid's properties window.
All changes can then be saved or discarded at once by using the buttons in the ‘Editing’ ribbon.
Note that you still need to be connected to the underlying cube / model to use the offline data entry mode.
Formatting and locking cells
When writeback is enabled for a grid, any cell can be changed which means that data entry permissions are based on the security defined in the source cube/model. You can change this default behaviour and control which cells should be allowed at the grid level by ticking the ‘Cells must be unlocked’ box in the Grid Properties window.
As cells grid cells are by default locked in the format sheet, any writeback is then prevented in the grid.
Formatting/Locking scenarios using the format sheet
The locking of specific slices of data follows the same rules as the standard FluenceXL formatting ones defined in the ‘FluenceXLFormats’ sheet. Please read the Formatting Grids page for more details.
The following examples illustrate different cell locking scenarios.
Unlocking all the lowest level accounts
Please refer to the Level based formatting page for more details.
Locking all the years
Note that you should define a white background for these cells to overwrite the yellow one defined for the leaf accounts.
Please refer to the Level based formatting page for more details.
Unlocking a specific column / month
Locking a specific cell
Dynamic Formatting/Locking of cells using custom formatting areas
You can define additional ranges of cells to control the formatting and locking. These ranges use the same rules as the format sheet (Dimension /Member / data cell) but don’t have spacer cells between each cell.
These additional rules will be applied to the grid after the ones of the format sheet. Any change in these will be tracked by the grid independently of its refresh status. The highlighting and locking of cells can thus be dynamically driven from Excel formulae or from an external data source such as a SQL query.
You can add custom formatting areas from the Appearance tab of the grid's properties window.
In the below example, all the periods (*) are locked from the format sheet...
...while the current and future ones are unlocked in the custom formatting range thanks to Excel logic.
Validating data entry
If you’re using FluenceXL and the offline data entry mode, you can add validation rules to restrict the values allowed. You can set up hard and soft validation rules from the FluenceXL\Options\Workbook Options menu item:
- A hard validation rule prevents from saving unvalid changes to the database
- A soft validation rule alerts the user but lets him decide whether the changed values should be saved
Whatever its type, a validation rule is based on a boolean cell and a message. Data entry can only be saved when the value of the Boolean cell returns TRUE.
Hard Validation
In the following example, cascading Excel formulae are used to build a logic preventing the user from entering negative values for revenue accounts:
$G$5 counts the number of negative values
$H$5 returns FALSE if there is at least one negative value, TRUE otherwise
and $I$5 contains the message that is displayed if $H$5 returns FALSE.
Soft Validation
In the following example, cascading Excel formulae are used to build a logic alerting the user than forecast revenue is lower than 80% of the previous year.
