Overview:
The ExecutiveDashboard rules define computations for the Executive Dashboard grid. These are advanced rules. Computed fields might be used to display a value in a column, or the value might be a step in a larger calculation (for example, cmpNetCashDemand uses cmpCashConversion). The rules for formulas are the same as those for bookmark template’s computed bookmarks. Relationships and aggregation are of limited use on the Executive Dashboard.
The ExecutiveDashboard rule group is found in the Rules Maintenance tool on the System Admin Dashboard. Each rule in the group consists of a rule name and a result value.
Filter Value:
None
The ExecutiveDashboard rules to not use filter values.
Result Value:
Varies
Result values for the ExecutiveDashboard rules depend on the rule and are indicated within the description of each rule. Some rules are enabled and disabled through a checkbox in the result value, for example:
Other rules accept a formula
Some rules accept a formula for the result value. ComputeList takes a list of computed values. Default formulas are listed in the chart below. Note: there is a 196-character limit in the result value field. If your text is longer than 196 characters, you will need to
- Split your text into smaller segments, each of any length up to 194 characters,
- Append $+ to the end of each segment, except the last one,
- Add a rule row for each segment. These rules should have names that indicate they are a continuation of the first rule, for example: cmpCashFunding, cmpCashFunding.01, cmpCashFunding.02, etc.
Rules:
Note: sfPMS first reads the ComputeList rule to get the names of the computed values. In turn, each computed value must have a rule entry with the computed value name and the result value containing the formula. The order of this list is important; the names of fields referenced by other computations must appear prior to the dependent calculation.
ComputeList
Specifies (in the result value) the list of names of computed values. The default is shown below:
PromptFirst
[V23+] Specifies (in the result value checkbox) whether the Executive Dashboard should prompt for filters before attempting to load data. If the Executive Dashboard times out when loading, this rule should be turned on.
- unchecked = No (default).
- checked = Yes.
Computed value name
Specifies (in the result value) the formula for each computed value on the Executive Dashboard. The default formulas are listed here in alphabetical order.
| Rule | Result Value Formula |
| cmpActual | ISNULL(ex_act_amt,0) |
| cmpActualCommit | ISNULL(ex_act_amt,0) + ISNULL(ex_com_amt,0) |
| cmpAprvPR | ISNULL(ApprovedPRTotalAmount,0) |
| cmpBTD | ISNULL(BTD,0) |
| cmpCashConversion | TotalAR + Retention + IIF(cmpOverUnderBill > 0, cmpOverUnderBill,0) |
| cmpCashFunding | ISNULL(TotalAP,0) + ISNULL(APRetention,0) + IIF(cmpOverUnderBill < 0, -1.0 * cmpOverUnderBill,0) |
| cpmCCO | ISNULL(ApprovedSCAmount,0) |
| cmpContract | ISNULL(rv_Contract,0) |
| cmpCORev | ISNULL(ApprovedCO,0) |
| cmpCurrCommit | cmpOrigCommit + cmpCCO |
| cmpEarnedIncome | IIF(cmpExEAC <= 0, 0, (cmpContract * IIF( cmpActual > cmpExEAC, 1, ( cmpActual / cmpExEAC)) )) |
| cmpEstimatedProfit | ISNULL(rv_contract,0) – ISNULL(ex_eac_amt,0) |
| cmpExEAC | ISNULL(ex_eac_amt,0) |
| cmpExFAC | ex_fac_amt |
| cmpEXOrig | ex_bud_amt |
| cmpFutureProfit | ISNULL(rv_Contract,0) – ISNULL(ex_eac_amt,0) – ISNULL(cmpEarnedIncome,0) |
| cmpNetCash | (BTD – (TotalAR + Retention)) – (ISNULL(TotalCost,0) – (ISNULL(TotalAP,0) + ISNULL(APRetention,0)) + IIF(ISNULL(ex_act_amt,0) – ISNULL(TotalCost,0) > 0,ISNULL(ex_act_amt,0) – ISNULL(TotalCost,0),0)) |
| cmpNetCashDemand | cmpCashConversion – cmpCashFunding |
| cmpOrigCommit | ISNULL(OriginalSCAmount,0) |
| cmpOrigContract | ISNULL(rv_Contract,0) – ISNULL(ApprovedCO,0) |
| cmpOverUnderBill | -1.0 * (ISNULL(BTD,0) – ISNULL(cmpEarnedIncome,0)) |
| cmpPerComplete | ((cmpActualCommit) / IIF((ISNULL(ex_eac_amt,0)) = 0,1,(ISNULL(ex_eac_amt,0)))) |
| cmpRvEAC | rv_eac_amt |
| cmpUCO | rv_ppco_amt |
See also KBA-01230 for more information about these values.
Example:
Here’s an example changing the Earned Income formula to use Actual Expense (instead of Actual + Committed) when calculating Earned Income.
- First, the cmpActual rule is defined.
- Then, the cmpEarnedIncome formula is edited to use the cmpActual in place of the cmpActualCommit value.
- Lastly, the cmpActual formula is added to the ComputeList. Notice that it is added before the cmpEarnedIncome formula.
Additional Comments:
How do the computed values map to grid columns? The UI Configuration tool displays the name of the associated computed value when editing each column customization. For example:
KBA-01227; Last updated: April 4, 2024 at 13:39 pm;