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.
2. 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.
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
The resulting Salesperson Statistic records can be reported on, or integrated into another system for further processing.