Search

Dynamic Cell Access

Note: this is a workspace administrator feature.

Dynamic Cell Access provides the ability to control access to cell data in modules and dashboard grids at a very granular level. For any combination of dimensions, you can make the contents of individual cells or entire rows and columns read-only, editable, or invisible although the cell value still exists.

For example, you can restrict the visibility of cell data for individual time periods, or for a combination of lists such as product sales in specific regions.

Note: the terms "cell data" and "line item data" can be used interchangeably.

For even more control, use Dynamic Cell Access together with the Users list to control access by user as well as other dimensions. This is useful if you want to allow users to enter data but not view data entered by others.

Dynamic Cell Access is comprised of:

  • access drivers — Boolean-formatted line items — and their dimensions.
  • the Read Access Driver and Write Access Driver settings in Blueprint view.

To use Dynamic Cell Access, you must first create access drivers with the dimensions you want. Any line item in your model can be controlled by any valid access driver. Depending on the granularity of access you need, you then apply these access drivers to target cell data at line item or module level. Access to the data defined by the access drivers can then be controlled, either manually or through simple formulas, For example, you can use a Time function to make cell values in the Current Period editable.

Note: Dynamic Cell Access is additional to Selective Access, which restricts per-user access to lists and list items, but not to cell data. If both mechanisms are used together, the most restrictive level of access applies. Depending on your needs, you can use Selective Access to restrict access driver modules to workspace administrators only.

The rest of this article explains the different components of Dynamic Cell Access in more detail. For step-by-step examples, see Examples of Dynamic Cell Access

Note: See the feature in action by installing the Dynamic Cell Access Learning App.

Access drivers

Access drivers are Boolean-formatted line items — in other words, regular line items formatted as checkboxes. They can have any dimensions — including the Users list — or none. They're an important component of Dynamic Cell Access because they define the dimensions for which you want to dynamically control cell data access.

 

Access drivers:

Image: Access driver module

Target cell data with access drivers applied:

Image: Target module on which dynamic cell access is applied.

For simplicity, we recommend storing access drivers in dedicated access driver modules named according to their dimensions. For example, Access Drivers - Time or Access Drivers - Cities, Time. Use a dedicated Functional Area to separate these modules from other modules. The Show All Users setting must be enabled in the access driver module in order to control access to cell data by user — for more information, see Modules.

You apply access drivers to target cell data in either or both the Read Access Driver and Write Access Driver columns in Blueprint view. These columns determine whether cell data is read-only, editable, or invisible as described in Cell data access combinations.

Once an access driver is applied, access to the target cell data is controlled by selecting or deselecting the checkboxes for any given combination of dimensions. This can be performed manually or automated using line item formulas.

Global access drivers

Access drivers with no dimensions are known as global access drivers; they can be used to control access to cell data regardless of the dimensionality of the target module or line item.

Access drivers in model history

Changes involving access drivers are recorded in the change history of a model as Security Changes.

Valid and invalid access drivers

To serve as an access driver, a Boolean-formatted line item must be valid for the target cell data it's applied to. Here are the requirements for a valid access driver.

  • An access driver's dimensions must be compatible with those of the target module or line item it's applied to. For more information, see Compatible Access Driver Dimensions.
  • To control access to cell data by user, an access driver's dimensions must include the Users list.

A message is displayed if you try to apply an access driver that isn't valid.

Compatible access driver dimensions

Use the following tables to help you understand the requirements for compatible access driver dimensions.

One-to-one match

In the simplest scenario, the access driver and target module or line item have matching dimensions. For example:

Access driver's dimensionsTarget dimensionsAccess driver is valid
Cities listCities list

Target data has non-matching dimensions

If the target module or line item has additional dimensions that don't match those of the access driver, the access driver is still valid. For example:

Access driver's dimensionsTarget dimensionsAccess driver is valid
Cities listCities list, Offices list, Time
TimeTime, Versions, Cities list

Access driver has non-matching dimensions

If one or more of the access driver's dimensions don't match those of the target module or line item, the access driver is only valid if the following conditions are true.

  • The access driver has a summary method of All, Any, or Formula. These summary methods are designed for Boolean totals — see Summary Methods for more details. Be aware that Boolean line items have a summary method of None by default.
  • Every list dimension that doesn't match must have a Top Level Item, set on the Configure tab of the list.

The following table illustrates this scenario.

Access driver's dimensionsTop Level ItemTarget dimensionsAccess driver is valid
Cities list

No

Cities list, Offices list
Inventory list with a Summary Method of All, Any, or FormulaYes
Tip: If you want totals to display in the target module, set a Summary Method of All or Any.

Create access drivers

Follow these steps to create a Read and a Write access driver.

  1. Create a dedicated access driver module.
    • Use the dimensions for which you want to dynamically control access to the target cell data. For example, Time on columns.
    • Insert a Read and a Write line item.
  2. Switch to Blueprint view and change the format of both line items to Boolean.
  3. Exit Blueprint view and select every line item checkbox. (Use Copy Across.)
  4. Note: to control access to cell data for individual users, add the Users list as a dimension of the access driver module.

Note: if you only want to control read-only and edit access to the target cell data, write a NOT function on each access driver. For example, on the Read access driver, add the line item formula NOT Write and vice-versa. For examples, see the Dynamic Cell Access Learning App.

Apply access drivers to target cell data

To apply access drivers to target cell data, use either or both the Read Access Driver or Write Access Driver columns and dropdowns in Blueprint view. Every Boolean-formatted line item in the model is available to select from the dropdowns. You can also apply access drivers on the Line Items tab in Model Settings > Modules.

You can choose whether to apply access drivers at module or line item level.

To apply an access driver at module level, select it from your chosen Access Driver column in the first row of Blueprint view. For example:

Similar to a module's default dimensions, access drivers applied at module level cascade to every line item in the module (as long as a hyphen is entered for the line item). In this case, the same level of access applies to all cell data in the module.

To control both read-only and write access to cell data, select different access drivers in the Read Access Driver and Write Access Driver columns.

If you want a very fine level of granularity, apply separate access drivers to each line item.

Note: the Show All Users setting must be enabled in the target module in order to control access to cell data by user. For more information, see Modules.

Cell data access combinations

If only a Read Access Driver is applied to the target line item, access to the target cell data can be controlled as follows.

 Read Access Driver
Access Driver StatusEnabled Disabled
Target Line Item VisibilityRead-onlyInvisible

If only a Write Access Driver is applied to the target line item, access to the target cell data can be controlled as follows.

 Write Access Driver
Access Driver StatusEnabled Disabled
Target Line Item VisibilityEditableInvisible

If both a Read Access Driver and a Write Access Driver are applied to the target line item, access to the target cell data can be controlled as follows.

 Read Access DriverWrite Access Driver
Access Driver StatusEnabledDisabledEnabledDisabled
Target Line Item VisibilityRead-onlyInvisibleEditableReverts to Read Access Driver*

* In this scenario, the Write Access Driver takes precedence over the Read Access Driver. As a result, write access is granted if the Write Access Driver checkbox is selected (enabled). If the Write Access Driver checkbox is deselected (disabled), cell access is determined by the status of the Read Access Driver.

To control read-only and write access, both a Read Access Driver and a Write Access Driver must be applied to the target line item.

Dynamic Cell Access and Breakback

Be aware of the following behavior when Breakback is enabled on one or more line items controlled using Dynamic Cell Access.

If you change a line item total, such as a quarter total:

  • read-only and invisible cells are held for the current user and session; held cells are not updated in a Breakback operation.
  • editable cells are updated as usual by the Breakback operation.

To trigger a Breakback, the line item total you want to change must be editable.

If Dynamic Cell Access is applied so that editable child line items have a read-only total, updating cells for the child line item will update the total value.

Use a formula to drive Dynamic Cell Access

You can use a line item formula on an access driver to automatically enable or disable the access driver checkboxes depending on an external factor, such as the start of a new time period. For examples, see the Dynamic Cell Access Learning App.

Importing and exporting cell data controlled using Dynamic Cell Access

In modules and dashboard grids where access to cell data is controlled using Dynamic Cell Access:

  • workspace administrators can import data into any cell, including read-only and invisible cells.
  • end users can only import data into editable cells, not into read-only or invisible cells.
  • only read-only and editable cell data is included in exports.