Auto-generating Statistics in Microsoft Dynamics CRM

Auto-generating Statistics in Microsoft Dynamics CRM

Posted By

Although Microsoft Dynamics CRM does a great job of displaying information through views, dashboards and reports, sometimes more complex statistics are required. For example, generating salesperson statistics on a weekly basis that can be integrated into an Enterprise Resource Planning (ERP) system, and then be used to create commissions and bonuses.

This approach involves creating a few custom entities:

  • Week entity, one record per week
  • Workflow Setting entity, with one record to control the automatic scheduling for this process
  • Salesperson entity, with one record for each salesperson
  • Statistics entity, automatically generated with one record per salesperson per week

1. Defining the Week

First, a Week entity must be created so that we can find the start and end dates for each week. Then a record will have to be created for each week.

Defining weeks in Microsoft Dynamics CRM

2. Master Control Record

Since we will be using a Dynamics CRM workflow process to automate the statistics, we need a record to run it from. In this particular example, there is an entity called Workflow Setting that contains records for various workflow processes. The Workflow picklist controls what fields are displayed on the form using JavaScript.For the weekly statistics, we will need a lookup to the current Week record, as well as the Calculated Run Date/Time and Override Run Date/Time. Initially, all the fields will need to be populated manually. After the process has been started, these 3 fields will be updated automatically. At any time, users can change the Override Run Date/Time if necessary like in this example.

Master Control Record

3. Automatic Scheduling

Next, a Dynamics CRM on-demand workflow process is created called “Sales Stats – Calculate All Weekly Stats”. When it is running it will wait for the Override Run Date/Time, perform some custom code, and then call itself to start the process over again automatically for the next week. Initially, this workflow will have to be run manually from the Workflow Setting record.

The custom code (BDO Activities:SalesStatsCalculateRuntime) will:

  • Update all Salesperson records with the Next Run Week and Override Week Run Date
  • Find the next Week record to run statistics against
  • Calculate the next Run Date
  • Update the 3 statistics fields on the Workflow Setting record with the new values



4. Triggering the Individual Processes for Each Salesperson

Once the Run Week and Run Time fields are updated on a Salesperson record, another workflow is triggered immediately. The reason for this is to split the processing into many asynchronous system jobs instead of one large one. Also, this workflow process can also be run on demand for one individual salesperson from the Salesperson record.

Auto-generating Statistics in Microsoft Dynamics CRM_4


5. Creating the Statistics Records

A second workflow process called “Sales Stats – Calculate Weekly for Salesperson” is created. This will execute some custom code (BDO Activities:SalespersonStatsWeekly) that will:

  • Perform validations
  • Get the start and end times for the statistic period
  • Check that a Salesperson Statistic record doesn’t already exist for that salesperson in that week
  • Calculate various statistics
  • Create a Salesperson Statistic record

Auto-generating Statistics in Microsoft Dynamics CRM_5

The resulting Salesperson Statistic records can be reported on, or integrated into another system for further processing.


Free eBook!
Download our free eBook to see why you should consider taking your CRM to the cloud!

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing