The Count and DCount functions total items in a group. For example, the number of contacts that RSVP yes, the number of holiday cards per sponsor, or the number of activities per originator.
The InterAction out-of-the-box Activities by Type report is a good example of DCount. It totals the number of activities by type:
To count a field, select the textbox in the Report Designer and then set the following values for that textbox :
- Set SummaryFunc to Count or Dcount (see When Should I Use DCount or DSum?)
- Set SummaryGroup to GroupHeader1 (or whatever group you are counting)
- Set SummaryRunning to Group
- Set SummaryType to SubTotal
- Set the DataField property to the field that you want to count
- If you are using DCount, also set the DistinctField property to the field that you want to count
When Should I Use DCount or DSum?
Count and Sum are useful functions when you are counting an item that is repeated (for example, the number of activities or relationships for a contact) in the Combined table. (For more information on the Combined table, see What is the Combined Table?.)
DCount and DSum ensure that the same record is not counted multiple times when data is repeated in the Combined table. Use these options when counting at a high level where records are more likely to be counted multiple times. For example, use DCount or DSum to count the number of contacts that are included in a contact activity report.
Example:
You create an activity report that only contains Activity Date, Type, Summary and Originator. You want to group the activities by Originator and count the number of activities per originator. Because each activity will only appear once in the Main table, Count can be used.
However, if you add contacts to this report, each activity will be repeated in the Combined table for each contact on the activity. If an activity has four contacts, the activity is listed four times in the Combined table. Using the Count function would count this activity four times. To avoid this, you can use the DCount function, which counts the number of distinct activities in the report. Thus, the activity with four contacts would only be counted once.
InterAction Reports that Use Count
Following are InterAction out-of-the-box reports that use Count or DCount.
InterAction Out-of-the-Box Reports that Use Count
| Report | Field Counted |
|---|---|
| Event Summary | Attended (total attended) |
| Activities by Type | ID (Activity ID - total activities by type) |
| Activities by Originator | ID (Activity ID - total activities per originator) |
| Appointments by Type | ID (Appointment ID - total appointments by type) |
| Appointments by Originator | ID (Appointment ID - total appointments per originator) |