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.

Enabling writeback for a grid

In an XLCubed 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 XLCubedFormats 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 for data entry 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 should now be prevented in the grid.

Formatting and locking cells with the format sheet

The locking of specific slices of data follows the same rules as the standard XLCubed formatting defined in the ‘XLCubedFormats’ sheet. Please read the Format_Sheet page for more details.

The following examples illustrate different cell locking scenarios.

Unlocking all lowest level accounts (LEVEL:LEAF)

Locking all the years (the second level: LEVEL:2)

Note that you should define a white background for these cells to overwrite the yellow one defined for the leaf accounts.

Unlocking a specific column / month

Locking a specific cell

Dynamic formatting and cell locking

You can define additional ranges of cells to control the formatting and locking of cells. 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 an extra custom format area in the Appearance tab of the Properties screen.

In the below example, all the periods (*) are locked from the format sheet while the current and future ones are unlocked in the additional formatting range thanks to Excel formulae.

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.