KBA-01227: Overview of the ExecutiveDashboard Rules

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:

Formula

The ExecutiveDashboard rules accept a formula for the result value of all rules except ComputeList, which 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:

KBA01227_01

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:

KBA01227_03


KBA-01227; Last updated: March 12, 2017 at 15:14 pm;