Cell Format Expression
| Release: |
4.6 • 5.0 • 5.1 • 5.2 • 5.3 • 5.4 • 6.0 • 6.1 • 6.2 • 6.3 • 6.4 • 6.5 |
|---|
New to Analytica 5.0
Attribute CellFormatExpression
The Cell Format Expression attribute, whose identifier is CellFormatExpression, is used to create Computed cell formats, in which the colors, borders, fonts and other properties of a table (usually result table, not as commonly edit tables) are computed. The formats can be determined based on the variable's value, the indexes, or even other computed values which differentiates Analytica's formatting from, for example, Excel's Conditional Formatting.
The page on Computed cell formats provides an overview of how you created expressions to compute cell formats.
It is not necessary to use the Cell Format Expression for most cell formatting. Any formatting that involves simple, static changes to the table can be done using the Cell format dialog in the Analytica user interface. The Cell Format Expression is only used when you want to compute formats using code.
Any formats computed by the Cell Format Expression take precedence over any formats set using the Cell format dialog.
Setting that are set using the Cell format dialog are stored in the attribute Att_CellFormat, which is not expected to be edited by a model builder or user.
Showing the Cell Format Expression attribute
Before you can create expressions that compute cell formats, you must make the Cell Format Expression attribute visible. To do this from the Object window, right-click in the left column where the attribute names appear and select it in the context menu. Or, to turn it on globally, go to the Attributes dialog on the Object menu, and with the Variable class selected, click on Cell Format Expression to put a check in front.
After doing so, the Cell Format Expression attribute will be visible and can be edited from the Attribute panel or Object window.
Auxiliary variables
Your Cell Format Expression can reference several values that provide information about the view being shown, as follows.
Self: The value in each cell of the result or edit table. (For edit tables, these might be expressions).HorizIndex: Synonymous with the horizontal index in the current pivot.VertIndex: Synonymous with the Vertical index in the current pivot.ComparisonIndex: Synonymous with the comparison index, if any.ViewMode: The view type, one of:'Mid','Mean','Sample','PDF','CDF','Statistics','Bands','Edit'.VertIndexes: A list of row indexes (currently either 0 or 1 handles in length).HorizIndexes: A list of column indexes (currently 0, 1, or 2 handles in length).SlicersSetToTotal: A list of handles to slicer indexes that are currently set to Total.
Finally, the list of slicer index settings can be obtained from the top level of your logic, but not necessarily from within nested expressions, using the GetEvaluationContext function. For example, GetEvaluationContext( ) returns the list of slicer indexes, except those set to Totals. Then, for any of these, GetEvaluationContext(h) returns the selected position along that index.
Using UDFs
If you develop interesting cell formatting logic, you should consider encapsulating it in as a User-Defined Function, to make it easy to use the same formats in other result variables. If your logic makes use of auxiliary variables, your function will need to accept them as parameters.
Edit Tables
By default, computed formats apply only to result tables, but static formats set from the Cell format dialog apply to both edit tables and result tables.
To apply your computed format to edit tables, set the CellFormatFlags attribute for your variable to 1. This must be done from Typescript:
- Press F12 to open the Typescript Window
- Type:
CellFormatFlags x : 1
where x is the identifier of your table.
Computed formats should be applied to edit tables only in certain safe cases. Many problems can result, so you should take great care before doing so. First, when your logic references Self, in many cases the values might be expressions in the edit table view, not numbers as they would be in the result table. This is likely to cause errors if your logic does even very common operations on the value, such as computing a Sum or Max, or using the value where a number is expected. Hence, you usually only want to consider computed formats that are restricted to numeric entries only.
Another consideration is the computation time required to re-evaluate your cell format logic. The expression will need to be re-evaluated often, including after individual cell changes. In a huge table, an operation over the entire array can take a substantial amount of time, and cause your edit table to be unwieldy.
Your computed expression can use the value of ViewMode, which is set to 'Edit' when an edit table is showing, as a way to alter your logic for the edit table view.
Enable comment auto-refresher