Counting records

Counting records is one of the basic requirements for Microsoft Dynamics CRM customers. There are a number of situations where obtaining a count of records can help you make some important business-related decisions.

The following list shows scenarios where a count of records may be required:

• A sales manager wants to know the count of active opportunities for each owner.
• A customer service representative wants to know the total number of closed activities in the queue.
• A marketing manager requires the total number of contacts created in a particular month.

This article describes how to obtain a count of records in Microsoft Dynamics CRM using:

• A marketing list
• The Report Wizard
• The Export Data to Excel feature

On This Page Count records using marketing lists Count records using Reports Count records by exporting data to Excel

Count records using marketing lists

You can obtain a count of total records for any entity in Microsoft Dynamics CRM by adding the required entity records to a marketing list.

To count the number of records:

1. Add the records that you want to count to a marketing list. For more information about adding records to a marketing list, see Work with Marketing Lists.
2. Open the marketing list and under Details, click Marketing List Members.

   The total of the number of records is displayed on the Marketing List Members page.

   Note

   Because you can add only accounts, contacts, and leads to marketing lists, you can use this option to count records for accounts, contacts, and leads only.

Top

Count records using Reports

You can create reports to group records based on a specific criteria and provide a count of records in each group using the Report Wizard. The count option in the Report Wizard groups the records by a count of matching records. For example, if you have 10 contacts created in the month of July and another 20 created in August, and you configure the Report Wizard to group contacts by month, the report will show a count of 10 contacts under July and a count of 20 contacts under August, but it will not show the total count of contacts as 30.

To obtain a count of records using reports, group items so that they are organized together in the report by some common properties:

1. In the Navigation Pane, click Workplace, and then under My Work, click Reports.
2. To add a new report, click New, and then click Report Wizard.
3. Select Start a new report, and click Next.
4. Enter the name of the report, and specify which record types (primary and related record types) the report will use. To obtain a count of contacts, use contacts as the primary record.
5. Define which records to include by selecting a filter criteria.
6. For each grouping level you need, click Click here to add a grouping.
7. Define the grouping Organizing the data in a report so that related data is grouped together. For example, you could group data by week the record was created or by the amount of revenue.:
   • Record type. You can select records from the primary record type you selected, and from any related record types. If you selected a secondary record type, you can also select records from the secondary record type and its related record types.
   • Column. The name of the Microsoft Dynamics CRM field that contains the data to define the grouping.
   • Time interval. Specify the time interval to group the data by Month, Day, Week, or Year.

     This field is enabled only when the value specified in Column is of datetime type, for example, Records Created On, Created On, or Modified On.

   • Sort order. Sort order for displaying the groups.
   • Summary type. Select Count to group records by a count of matching records.

   For example, if you select Contacts as Record type, Created On as Column, and Month as Time interval, the data in the report is grouped by Created On. The report provides a count of contacts created in any particular month.

8. To reorder the grouping levels, use the up and down arrows in the Common Tasks section.
9. Click OK.

Top

Count records by exporting data to Excel

In Microsoft Dynamics CRM, there are two ways to obtain a count of records using the Export Data to Excel feature:

• Use a static worksheet
• Use a dynamic PivotTable

Count total records using a static worksheet

1. In any view, click Export Data to Excel .
2. Select Static worksheet with records from this page, and then click Export.
3. In the File Download dialog box, click Open.
4. Use the ROWS(array) function to count the number of rows.

   Syntax: ROWS(array)

   Array is the reference to a range of cells for which you want the total number of rows.

   1. Click the cell in which you want to enter the formula.
   2. To start the formula with the function, click Insert Function on the formula bar .
   3. Select the ROWS(array) function.
   4. To enter cell references as an argument, click Collapse Dialog , which temporarily hides the dialog box. Select the cells on the worksheet, and then click Expand Dialog .
5. When you complete the formula, press ENTER.

You can also view the count of number of rows by selecting the primary record column (usually the first column). Excel shows a count of total rows that have data in it on the Status Bar.

Note

The count shown on the status bar also includes the header row.

Count total records using a dynamic PivotTable

1. In any view, click Export Data to Excel .
2. Select Dynamic PivotTable, and then click Export.
3. From the PivotTable Field List area, drag the ID field to the Drop Data Items Here area. For example, if you have exported accounts data to Excel, drag the accountid field to the Drop Data Items Here area.

   A count of total records is displayed.

   Note

   By default, the Export Data to Excel feature can export only 10,000 records at a time.

Top

Related Links

Work with Marketing Lists

Create and Work with Reports

Export Data to Excel