In my role on the Premier/Deluxe Support team for CRM, we manage the support relationships with customers who want to have a designated resource within Microsoft. We have 333 customers today across the Dynamics products with about 72 customers on CRM. While we have systems for tracking support incidents and systems to manage product purchases, we didn’t have a system for tracking our interactions with our customers, our contacts and our support contracts. Therefore, we decided to roll out CRM internally to our team.
One challenge that presented itself was the fact that Microsoft has complicated fiscal periods. They make sense when you think about it, but they don’t follow months. Period 1 is 4 weeks and started June 2. Period 2 is also 4 weeks, but then period 3 is 5 weeks so each quarter is 13 weeks. When it comes time to report on the revenue for each period, we can’t look at the month and determine the period. We needed to define data for the fiscal periods and store them in a way CRM can access it. The first thought was to create a custom table that has everything we need (and this is what a customer of mine decided to do). When you add a custom table to your CRM database, you introduce some complications. First, it’s not supported and there’s no guarantee future upgrades won’t wipe out your table. Second, permissions in CRM are defined on a per object basis, so the ReportingGroup won’t have access to an object not created by CRM. To enable your users to use that custom table, you need to define security to it explicitly.
Based on this evidence, we decided to create a custom fiscal period entity in CRM (schema name: New_FiscalPeriod). This entity stores the period start date, end date, the period number, the quarter and the fiscal year relevant to those dates. For each year, we have twelve records.
Now when we run our reports each period, quarter or year, we just need to add a “between” condition in our where clause to determine the periods into which our contract dates fall. Here’s an example of the where condition for our query:
JOIN FilteredNew_FiscalPeriod FP // name of our fiscal period view
ON
CAST(CONVERT(char(10),new_billingenddate,101) as datetime) BETWEEN CAST(CONVERT(char(10),FP.new_periodstartdate,101) as datetime) AND CAST(CONVERT(char(10),FP.new_periodenddate,101) as datetime) //comparing our billing date to see if it lies between the fiscal period start and end dates
* Note – you need to convert the dates because there’s a timestamp placed on the date field that can be different if you have users from different time zones using the system
With the Fiscal Period entity and some SQL Reporting Services queries, we can now report on our business in a completely supported manner with no custom coding. And once we realized how easy this was to track period dates, we decided to use it to compare our goals for the period. We went back to the Fiscal Period entity and added new attributes where we could input our goals for customer additions and revenue for each period. Once we did this, we could report on our results and measure our success against the goals we had for the period, quarter, year and year over year results.
The moral for this story is before you think about creating a custom table to track data related to CRM, take a moment to think about how it could be done within CRM. It provides you with an easy means for data entry/editing and it can all be done within the CRM security context. It worked for our internal implementation and several of my customers as well.