Highlighting cells: Status cell item

Desktop User Guides > Professional > Table scripting > Table presentation > Highlighting cells: Status cell item

Cell highlighting can be used to create a traffic light style report where cells are colored red, yellow, or green as a quick indication of status. Cell highlighting uses the following features:

Status cell item

A FormatExpression is used to setup the Status cell item with values of “R”, “Y”, and “G”. The Status cell item can be displayed in the table if required. “R”, “Y”, and “G” are the default values; you can change them.

The Status cell item must be set in Reporter by using the “Define conditional highlighting” wizard or in the tables script.

You can define as many status cell items and associated status styles as you need.

See also Stylesheets.

Example

This example uses the Museum data set. The table has visitors’ age across the top and the museum entrance that they used down the side. The target is to interview a similar number of visitors in each age group at each entrance. To do this, the Column Percentage cell item should be about 50% for the Main and Side entrances; you can use different colors to show how far the actual numbers are from the target.

Status cell item

The Status cell item is derived by using a FormatExpression. To use the default status styles and stylesheets, the result of the FormatExpression must be R, Y, G, or empty. If the Status value is empty, the cell is not highlighted.

The FormatExpression for the example is:

IIf(Value.RowType.IsOneOf(1,3,24) Or Value.ColType.IsOneOf(1,3,24), '.', IIf(([Value.ColPercent] <= 30 OR [Value.ColPercent] >= 70), 'R', IIf(([Value.ColPercent] <= 40 OR [Value.ColPercent] >= 60), 'Y', IIf(([Value.ColPercent] > 40 AND [Value.ColPercent] < 60), 'G', '.'))))

The first line ensures that the Base row and column are not highlighted. The rest of the rows set the R, Y, or G value.

The FormatExpression uses the Value identifier to define which Value is being used. The Value identifier can be used to access any of the other CellItems as defined in the CellItemType enumerator.

(See also CellItemType.)

CellItem	Access using Value
Count	[Value.Count] Brackets [ ] are necessary to avoid errors, because Count is a reserved word.
Column base	Value.ColBase
Row base	Value.RowBase
Unweighted column base	Value.UnweightedColBase
Unweighted row base	Value.UnweightedRowBase
Column percentages	Value.ColPercent
Column ranks	Value.ColRanks
Cumulative column percentages	Value.CumColPercent
Cumulative row percentages	Value.CumRowPercent
Expected values	Value.ExpectedValues
Indices	Value.Indices
Maximum	Value.Maximum
Mean	Value.Mean
Median	Value.Median
Minimum	Value.Minimum
Mode	Value.Mode
Percentile	Value.Percentile
Range	Value.Range
Residuals	Value.Residuals
Row percentages	Value.RowPercent
Row ranks	Value.RowRanks
Sample variance	Value.Variance
Standard deviation	Value.StdDev
Standard error	Value.StdErr
Sum	Value.Sum
Total percentages	Value.TotalPercent
Unweighted counts	Value.UnweightedCount

Items that can be referenced

Some items are not available as CellItems to be added, but are created by statistical tests and can be referenced. For example, the following FormatExpression highlights significance level high results as red, and significance level low results as 0, by using the ColPropResults cell item created by the column proportions test:

IIf(Value.ColPropResults.RegExFind('[A-Z]').Len() > 0, 'R', IIf(Value.ColPropResults.RegExFind('[a-z]').Len() > 0, 'Y', '.'))

CellItem	Access using Value
Indices	Value.Indices
AdjustedResiduals	Value.AdjustedResiduals
ValidN	Value.ValidN
ColPropResults	Value.ColPropResults
ProfileResult	Value.ProfileResult
CellChiSquare	Value.CellChiSquare

Accessing the row and column names

To access the row and columns names, use Value.RowName and Value.ColName. These names are usually the category name, so this feature is useful when the FormatExpression is different for each row and/or column.

Checking the type against the ElementType enumerator

To check the type against the ElementType enumerator, use Value.RowType and Value.ColType can be used.

See also ElementType.

The following example avoids highlighting the base row and column. It checks whether RowType is one of eBase (1), eEffectiveBase (3), or eUnweightedBase (24, &H0018), and then sets the Status to . (period or dot).

IIf(Value.RowType.IsOneOf(1,3,24) Or Value.ColType.IsOneOf(1,3,24), '.',

The IIf function is very useful in FormatExpressions.

Referencing CellItems by index

The CellItems can also be referenced by index. For example, if a cell has Counts and Column percentages, Value[0] refers to the Count value.

Simplifying expressions that are different for each row or column

To simplify expressions that are different for each row or column, use Decode function.

The following example uses Decode and Value.ColName to create different expressions for the Interviewer_Idle category and the Interviewer_Wait category.

Decode(UCase([Value.ColName]),
'INTERVIEWER_IDLE', IIf(Value.Mean>=20, 'R', IIf(Value.Mean>=10, 'Y', 'G')),
'INTERVIEWER_WAIT', IIf(Value.Mean>=45, 'R', IIf(Value.Mean>=30, 'Y', 'G')), ' ')

Always included a default value: in this example, if a different category is evaluated, the Status is set to ' ' (empty).

The following script sets up the Status cell item for this example:

Set CellItem = Table.CellItems.AddNew(32 '! Status !')
CellItem.FormatExpression = "IIf(Value.RowType.IsOneOf(1,3,24) Or Value.ColType.IsOneOf(1,3,24), '.', IIf((Value.ColPercent < 30 OR Value.ColPercent > 70), 'R', IIf((Value.ColPercent < 40 OR Value.ColPercent > 60), 'Y', IIf((Value.ColPercent >= 40 AND Value.ColPercent <= 60), 'G', '.'))))"

Errors in FormatExpressions

If the FormatExpression returns an error or one of the value references are invalid, the cell status displays as XXXXX, and the cell is not highlighted.

Displaying the Status cell item value

To display the Status cell item in the table, use the DisplayStatusCellValues export value. For example, to display the value in exporting to HTML, use this statement:

TableDoc.Exports["mrHtmlExport"].Properties["DisplayStatusCellValues"] = True

You can also use this property when exporting to Excel.

StatusStyles

To translate the Status cell item values to styles, use the TableDocument.StatusStyles object, for example:

With TableDocument.StatusStyles
.AddNew("G").Class = "StatusOk"
.AddNew("Y").Class = "StatusWarning"
.AddNew("R").Class = "StatusError"
End With

This statement generates the following HTML for the Main row in the example. The StatusOk, StatusWarning, and StatusError classe names have been added to the table cell definition.

Stylesheets

The Reporter stylesheets contain definitions for the Status styles. These stylesheets are used when displaying tables in Reporter and when exporting to HTML or Excel. The following example is from the Black and White.css stylesheet.

TD.StatusOk
{
background: #8bc34a;
}
TD.StatusWarning
{
background: #ffeb3b;
}
TD.StatusError
{
background: #f44336;
}