Difference between revisions of "Writeback Formatting, Cell Locking and Data Validation"

(Using offline data entry mode)
(Using offline data entry mode)
Line 44: Line 44:
 
All changes can then be saved or discarded at once by using the buttons in the ‘Editing’ ribbon.  
 
All changes can then be saved or discarded at once by using the buttons in the ‘Editing’ ribbon.  
 
[[File:10 WBFformattingLocking.png|800px|center]]
 
[[File:10 WBFformattingLocking.png|800px|center]]
 +
  
 
Note that you still need to be connected to the underlying cube / model to use the offline data entry mode.
 
Note that you still need to be connected to the underlying cube / model to use the offline data entry mode.

Revision as of 18:48, 6 February 2023

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.

01 WBFformattingLocking.png


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.

02 WBFformattingLocking.png

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:

03 WBFformattingLocking.png

2. Defining a format for the ‘Writeable Member’ data cell in the XLCubedFormats sheet:

04 WBFformattingLocking.png

Here is the result:

05 WBFformattingLocking.png


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

06 WBFformattingLocking.png

2. Define a format for the changed cells (‘Written Cell’) and the impacted ones (‘Summary written cell’)

07 WBFformattingLocking.png

Here is the result:

08 WBFformattingLocking.png

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.

09 WBFformattingLocking.png


All changes can then be saved or discarded at once by using the buttons in the ‘Editing’ ribbon.

10 WBFformattingLocking.png


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 writebacka is enabled for a grid, any cell can be changed and data entry permissions only rely 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.

11 WBFformattingLocking.png


As cells grid cells are by default locked in the format sheet, any writeback should now be prevented in the grid.

12 WBFformattingLocking.png


13 WBFformattingLocking.png

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)

14 WBFformattingLocking.png


15 WBFformattingLocking.png


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.

16 WBFformattingLocking.png


17 WBFformattingLocking.png


Unlocking a specific column / month

35 WBFformattingLocking.png


34 WBFformattingLocking.png


Locking a specific cell

18 WBFformattingLocking.png


19 WBFformattingLocking.png


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.

20 WBFformattingLocking.png


21 WBFformattingLocking.png


22 WBFformattingLocking.png


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.

23 WBFformattingLocking.png


24 WBFformattingLocking.png


25 WBFformattingLocking.png


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

26 WBFformattingLocking.png

$H$5 returns FALSE if there is at least one negative value, TRUE otherwise

27 WBFformattingLocking.png

and $I$5 contains the message that is displayed if $H$5 returns FALSE.

28 WBFformattingLocking.png


29 WBFformattingLocking.png


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.

30 WBFformattingLocking.png


31 WBFformattingLocking.png


32 WBFformattingLocking.png


33 WBFformattingLocking.png