Desktop User Guides > Professional > Table scripting > Table presentation > Highlighting cells: Status cell item
 
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 Status cell item.
Status styles
The status styles link the Status values (“R”, “Y”, and “G”), to the CSS styles StatusOk, StatusWarning, and StatusError.
When using Survey Reporter, set the status styles by using the “Define conditional highlighting” wizard or in a template .mtd file. These must be set up in the tables script.
See also StatusStyles.
Style sheets
The HTML stylesheets define formatting for the StatusOk, StatusWarning, and StatusError styles. These styles apply to the table when it is viewed in Survey Reporter or UNICOM Intelligence Reporter - Survey Tabulation, and when it exported to HTML or Excel.
The default stylesheets use with red, yellow, and green highlighting.
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.
<tr>
 <td rowspan="2" class="Axis AxisElementSide AxisElementCategory">Main</td>
  <td class="Cell CellTop CellItemCount CellElementBase CellElementCategory">346</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusOk">20</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusWarning">52</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusOk">54</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusOk">103</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusOk">53</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusWarning">33</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusOk">19</td>
  <td class="Cell CellTop CellItemCount CellElementCategory StatusError">12</td>
<tr>
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;
}
See also
Minimum base options
Profile table properties
Creating custom table properties
Examples
Table presentation