Cell and table status in interviewing reports
To highlight cells in the interviewing reports, see
Cell status in interviewing reports. For more information about how to highlight cells, see
Highlighting cells: Status cell item. See
Cell status in interviewing reports.
Table status can be used to highlight an table in UNICOM Universal Gateway (UniGW) and third‑party dashboard applications. To use table status in interviewing reports, see
Table status in interviewing reports. For more information, see
Indicating table status in UNICOM Universal Gateway (UniGW) and
OnAfterPopulate event.
Cell status in interviewing reports
To set cell status in interviewing reports, use one of the following methods:
▪When there are many cells that all require different limits, use hardcoded FormatExpressions.
Creating parameterized reports
If the report uses a small number of limits, you can create a parameterized report. For example, the “Busy, Wait, Idle” report uses the following FormatExpression:
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')), ' ')
This expression sets idle values that are greater than 20 to red, and those that are greater than 10 to yellow. It sets wait values similarly.
To make it easy to change the values, they are stored in a Parameters collection under the report name in DPM. In this example, the Parameters collection is under Site | Servers | <ServerName> | Applications | CatiReports | ApplicationSettings | BusyWaitIdle. The Parameters collection contains four properties: InterviewerIdle_ErrorLimit, InterviewerIdle_WarningLimit, InterviewerWait_ErrorLimit, and InterviewerWait_WarningLimit. To use the parameters, change the FormatExpression as shown in the following example:
"Decode(UCase([Value.ColName])
'INTERVIEWER_IDLE', IIf(Value.Mean>=" + CText(Parameters["InterviewerIdle_ErrorLimit"]) + ", 'R', IIf(Value.Mean>=" + CText(Parameters["InterviewerIdle_WarningLimit"]) + ", 'Y', 'G')), " + _
'INTERVIEWER_WAIT', IIf(Value.Mean>=" + CText(Parameters["InterviewerWait_ErrorLimit"]) + ", 'R', IIf(Value.Mean>=" + CText(Parameters["InterviewerWait_WarningLimit"]) + ", 'Y', 'G')), " + _
"' '" + _
")"
Put the FormatExpression in the ApplyStatusParameters subroutine in the report script. The ApplyStatusParameters subroutine should also add the Status cell item. If you are using the default R/Y/G values, the ApplyStatusParameters subroutine does not need to add the translation of Status cell items to StatusStyles, because this is done by the calling script. You can override the default translation from value to style by setting the StatusStyles in the ApplyStatusParameters function. The following example is the full ApplyStatusParameters subroutine from the “Busy, Wait, Idle” report.
Sub ApplyStatusParameters(TableDoc, Parameters)
Dim Table, StatusCellItem, FormatExpression
FormatExpression = "" + _
"Decode(UCase([Value.ColName]), " + _
"'INTERVIEWER_IDLE', IIf(Value.Mean>=" + CText(Parameters["InterviewerIdle_ErrorLimit"]) + ", 'R', IIf(Value.Mean>=" + CText(Parameters["InterviewerIdle_WarningLimit"]) + ", 'Y', 'G')), " + _
"'INTERVIEWER_WAIT', IIf(Value.Mean>=" + CText(Parameters["InterviewerWait_ErrorLimit"]) + ", 'R', IIf(Value.Mean>=" + CText(Parameters["InterviewerWait_WarningLimit"]) + ", 'Y', 'G')), " + _
"' '" + _
")"
Set Table = TableDoc.Tables[0]
Set StatusCellItem = Table.CellItems.AddNew(32 '! itStatus !')
StatusCellItem.FormatExpression = FormatExpression
End Sub
Using hardcoded FormatExpressions
When there are many cells that need different limits, do the same set up in the CreateReport script. For an example of this type, see the “Queue Status” report. The ApplyStatusParameters subroutine is called only if a Parameters collection exists, so the Queue Status report isolates the cell highlighting into an ApplyStatusParameters subroutine, but calls the subroutine from CreateReport.
ApplyStatusParameters(TableDoc, Null)
The following example is an ApplyStatusParameters function from the Queue Status report follows. It uses the
Decode function to avoid extra levels of
IIf.
Sub ApplyStatusParameters(TableDoc, Parameters)
Dim Table, StatusCellItem, FormatExpression
' Table 1 - Counts - Similar to SampleUsage
FormatExpression = "" + _
"Decode(UCase([Value.RowName]), " + _
"'COMPLETED', IIf(Value.ColPercent=0, 'R', IIf(Value.ColPercent<10, 'Y', 'G')), " + _
"'SILENT', IIf(Value.ColPercent>3, 'R', IIf(Value.ColPercent>0, 'Y', 'G')), " + _
"'CHECK_NUMBER', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'UNUSABLE', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'OVERQUOTA', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'REFUSED', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'STOPPED', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'ACTIVE', IIf(Value.ColPercent>=20, 'R', IIf(Value.ColPercent>=10, 'Y', 'G')), " + _
"'FRESH', IIf(Value.ColPercent<10, 'R', IIf(Value.ColPercent<30, 'Y', 'G')), " + _
"' '" + _
")"
Set Table = TableDoc.Tables[0]
Set StatusCellItem = Table.CellItems.AddNew(32 '! itStatus !')
StatusCellItem.FormatExpression = FormatExpression
' Table 2 - Calculations
FormatExpression = "" + _
"Decode(UCase([Value.RowName]), " + _
"'COMPLETESOVERTOTALATTEMPTSRECORDS', IIf([Value.Count]<0.1, 'R', IIf([Value.Count]<0.2, 'Y', 'G')), " + _
"'COMPLETESOVERRESOLVED', IIf([Value.Count]<0.2, 'R', IIf([Value.Count]<0.3, 'Y', 'G')), " + _
"'RESOLVEDOVERTOTALATTEMPTRECORDS', IIf([Value.Count]<0.2, 'R', IIf([Value.Count]<0.3, 'Y', 'G')), " + _
"' '" + _
")"
Set Table = TableDoc.Tables[1]
Set StatusCellItem = Table.CellItems.AddNew(32 '! itStatus !')
StatusCellItem.FormatExpression = FormatExpression
End Sub
Table status in interviewing reports
The Table.Status is set by default to “error” if any cells are red; “warning” if any cells are yellow; and “ok” if any cells are green. To change the default values, set the
OnAfterPopulate event in the ApplyStatusParameters or CreateReport subroutines.
See also