Report: Opportunities with Relationships Status

Open opportunities with relationship map summary fields (populated by the Opportunity Completeness batch job); note use of row-level formula + summary formulas to count different opportunity rel map types;

Note: the Map Count Type row-level formula field is used by various summary formulas to count different types of map. This row-level formula has a value of 0 for opportunities with no contacts, 1 for opportunities with Red rel map status and a value of 1000000 for opportunities with Amber/Green rel map status. Dividing the total sum of this field by 1000000 gives the sum of maps with Amber/Green status; the remainder after division by 1000000 (modulo) gives the sum of maps with Red status; adding these numbers together gives the total number of maps. This assumes that the number of Amber/Green opportunity rel maps is less than 1000000. This approach avoids the need for additional formula fields on the Opportunity object to count map types.

Specification:

  • Report Type: AltfRep_Opportunities_with_Rel_Map_Summary
  • Format: Summary
  • Group By:
    • Opportunity > Owner
    • Rel Map Status (bucket field)
    • Opportunity > Stage

  • Bucket Fields:
    • Bucket Name: Rel Map Status
    • Source Field: Opportunity > Altify Opportunity > Relationships Status (type: picklist)
    • Bucket Remaining Values as Other: false
      • Bucket Values
      1. Red Red
      2. Amber Amber
      3. Green Green

  • Time Frame Filter:
    Column Interval
    Opportunity > CloseDate Range: Current FY

  • Other Filters:
    Column Operator Value
    1. Opportunity > IsClosed equals 0

  • Row-level Formula:
    • Name: Map Type Count (type: Double; api name: CDF1)
    • Description: Assigns a numeric tag (0, 1 or 1000000) to support counting of opps with no contacts vs. opps with Red rel map status vs. opps with Amber/Green status
    • Formula: IF (NULLVALUE(Opportunity.ALTF__Altify_Opportunity__c.ALTF__Relationship_Map_Contact_Count__c, 0) == 0, 0, IF (ISPICKVAL(Opportunity.ALTF__Altify_Opportunity__c.ALTF__Relationships_Status__c,'Red'), 1, 100000))

  • Summary Formulas:
    Formula DataType Calculation
    # Opportunities number RowCount
    % Opps with Maps percent IF (RowCount == 0, 0, (TRUNC(CDF1:SUM / 100000, 0) + MOD(CDF1:SUM, 100000))/RowCount)
    % Opps with Amber/Green Maps percent IF (RowCount == 0, 0, TRUNC(CDF1:SUM / 100000, 0) / RowCount)
    Avg Key Players per Map number IF (Opportunity.ALTF__Altify_Opportunity__c.ALTF__Relationship_Map_Contact_Count__c:SUM == 0, 0, Opportunity.ALTF__Altify_Opportunity__c.ALTF__Relationship_Map_Key_Player_Count__c:SUM / (TRUNC(CDF1:SUM / 100000, 0) + MOD(CDF1:SUM, 100000)))
    # Maps number TRUNC(CDF1:SUM / 100000, 0) + MOD(CDF1:SUM, 100000)

  • Report Columns:
    • Opportunity > Name
    • Opportunity > Amount
    • Opportunity > CloseDate
    • Opportunity > Altify Opportunity > Relationship Map Contact Count
    • Opportunity > Altify Opportunity > Relationship Map Key Player Count
    • Opportunity > Altify Opportunity > Rel Map Supportive Key Player Count
    • Opportunity > Altify Opportunity > Rel Map Key Roles Uncovered Count
    • Opportunity > Altify Opportunity > Relationships Status
    • Opportunity > Altify Opportunity > Relationships Score
    • Row-level formula field (see definition on this page)