Table View Business Rules

Access to Table Views in Spreadsheet and Excel Add-In is limited to the Spreadsheet Business Rule Type.The purpose of the Business Rule is to establish the source data records to be displayed. The ability to save a record or field within a record is also completely defined within the Business Rule. The Table View Business Rules also support Parameters to enable the resulting Worksheet to be included in complex Dashboards.

Spreadsheet Function Types

  • GetCustomSubstVarsInUse Used to define the interaction with OneStream Dashboard Parameters

  • GetTableView Used to define the source(s) for the Table View. 

  • SaveTableView This function defines the table or cell intersection that should be written to a target database table

Processing Order

The Spreadsheet Function Types are designed to manage the processes within a common Dashboard environment.

  1. GetCustomSubstitutionVariables is executed first. 

    1. If the defined Parameter is contained within the Dashboard, the selection will act as a bound parameter and will be passed into the business rule.

    2. If the defined Parameter is not contained within the Dashboard, it will run/prompt the user.

    3. Additional conditional Parameters will be executed.  The Spreadsheet Business Rules can conditionally execute additional Parameters, based on the results of resolved Parameters.

  2. Once all the Parameters are resolved, the GetTableView function will be processed.  This section will generate the results in the Table View.  The Table View will also be evaluated to determine if there will be any writable conditions.  If there a no writeable conditions, which is the default, any refresh of the Spreadsheet/Table View will restart at the GetCustomSubstitutionVariables function.

  3. If the GetTableView is flagged as a writeable table, the SaveTableView process will be executed, writing back only the elements specifically defined in the Business Rule.

Using Parameters

The GetCustomSubstitutionVariables function is used incorporate Parameters into the Table View.  Any parameters required are passed in as a list within the Function Type.  If the Parameter is not included in the supporting Dashboard and resolved, for example as a Combo box, the Parameter will be executed in the Table View to be resolved.

Additional Parameters can be included in the Table View to act as a nested, conditional Parameter using the custSubtVarsAlreadyResolved function. This enables a resolved Parameter to be evaluated to trigger additional Parameters to execute.  The custSubstVarsAlreadyResolved can conditionally evaluate all resolved parameters to determine subsequent parameters to be executed.

Can Modify Data

All Table Views will default to “read only”.  The Table View condition for CanModifyData must be set to True to allow write-back capability.  The CanModifyData object is set in the GetTableView Function Type.  It is only required if any write-back is required based on the current Table View.  The True condition will enable objects to be passed, and enabled, in to the SaveTableView Function Type.  When refreshing a Table View, the SaveTableView Function Type will not be executed unless the CanModifyData property is set to True.

Table View Conditions

A single Spreadsheet Business Rule can contain multiple Table View definitions.  The Table View Name can be called using the Args.TableViewNameto allow conditionally calling rule functions.