Report: Accounts with Relationships Status

Account detail plus relationship map summary fields - these are custom Account roll up fields based on Altify Contact Map Details; note use of row-level formula + summary formulas to count different account 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 accounts with no contacts, 1 for accounts with contacts but no supportive key players and a value of 1000000 for accounts with supportive key players. Dividing the total sum of this field by 1000000 gives the sum of maps with supportive key players; the remainder after division by 1000000 (modulo) gives the sum of maps with no supportive key player; adding these numbers together gives the total number of maps. This assumes that the number of supportive key players across all account maps is less than 1000000. This approach avoids the need for additional formula fields on the Account object to count map types.

Specification:

• Report Type: Standard Accounts Report Type
• Format: Summary
  
• Group By:
  • Account Owner
  • Account > Rel Map Status


• Time Frame Filter:

  Column	Interval
  Created Date	Range: All Time



• 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 accounts with no contacts vs. no supportive key players vs. one or more supportive key players
  • Formula: IF (Account.Altify_Contact_Count__c == 0, 0, IF (Account.Altify_Supportive_Key_Player_Count__c == 0, 1, 100000))


• Summary Formulas:

  Formula	DataType	Calculation
  # Accounts	number	RowCount
  % Accs with Maps	percent	IF (RowCount == 0, 0, (TRUNC(CDF1:SUM / 100000, 0) + MOD(CDF1:SUM, 100000))/RowCount)
  % Accs with Supportive Key Players	percent	IF (RowCount == 0, 0, TRUNC(CDF1:SUM / 100000, 0) / RowCount)
  Avg Key Players per Map	number	IF (Account.Altify_Contact_Count__c:SUM == 0, 0, Account.Altify_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:
  • Account > Name
  • Account > Type
  • Account > Industry
  • Account > Altify Contact Count
  • Account > Altify Key Player Count
  • Account > Altify Supportive Key Player Count
  • Row-level formula field (see definition on this page)