Out of the box, Dynamics CRM workflows give users the ability to add and subtract days in order to dynamically populate attribute values. If you wanted to set a task due in two days, you could use the dynamic value editor below and click Add. Simple enough but what if you wanted to set the task due within two business days. Unfortunately, the out-of-the-box CRM workflow tool does not provide this functionality. When it adds by days it does not skip weekends and holidays.

 
Because I don’t care to work on holidays and weekends, I’ve created custom workflow activities that can add and subtract business days. After compiling the code and registering the assembly and activities with CRM, just add a step to a workflow and under Date Utilities select Add Business Days.

 
Enter the starting date for the calculation and the number of business days to add.

 
Make sure to give the step a meaningful name such as Calculate Due Date. 

 
You can then use this date to populate a date field in a following workflow step. For example, add a Create Record step and select Task. With the Due field selected, select the Calculate Due Date step and then select the Output Date from the drop down list below. 

 
And that’s it. Now just publish the workflow and try it out.

In order to add the date utilities to the CRM workflow tool, download the assembly and register it with CRM. Learn how to register a plugin on MSDN and then download the plugin registration tool.

Download Assembly

Download Code

(Code is a free download with no warranty and no support. If you send me a question by email, I’ll try to answer when I find time.)

If you prefer to compile the code yourself, create a Workflow Activity Library project in Visual Studio, add the three code files in the download, and add references to the Microsoft.crm.sdk.dll and Microsoft.crm.sdktypeproxy.dll.

I’m a big fan of custom workflow activities for Dynamics CRM. It is the ultimate in code reuse. Just write a custom activity and register it with CRM and then power users can use it as a building block to create powerful workflows.

Share knowledge and collaborate. Salespeople and others involved in forecasting will make better decisions if they have more knowledge available to them. The information may include the factors that went into the quantitative forecasts; information on relevant economic data such as leading indicators; industry trends; market research; and information on past forecasting accuracy including the rationale that led to errors. Marketers may have information to share on plans for pricing and promotions that would influence sales. Operations and logistics may have information available on problems that could limit availability and sales. Clearly, there is much to be gained in forecast accuracy when information is shared.

Document decisions. Requiring documentation for every qualitative adjustment to forecasts services two purposes. First, forecasters will give more thought to a forecast whenever they need to write out a rationale. Second, reviewing the rationales of past forecasts is a great way to learn how to improve forecasting. It is important not to request that a forecaster adjust so many forecasts that they would not have time to explain their decisions at least briefly. When a forecaster has too many forecasts to adjust, they will not be able to devote sufficient focus to each and will start to just enter numbers to get done with it.

Train the forecasters.  The analysts who perform the quantitative analysis obviously need training in quantitative techniques if they do not already have those skills and ongoing training is always helpful to refine skills. It is also beneficial to train salespeople in forecasting so that they better understand the basis for the quantitative forecasts, how best to interpret information that is provided to them, and what to consider when adjusting forecasts.

Include forecasting in job descriptions and performance reviews. Most people involved in forecasting do not consider forecasting an important part of their job and it is generally not part of performance reviews or compensation. Most salespeople consider forecasting an unpleasant distraction from their real work. Obviously, forecasts are unlikely to improve if the work is not taken seriously. That which is measured is rewarded and that which is rewarded gets done.

Integrate systems. Much forecasting and business analysis is performed in spreadsheets. Although convenient, spreadsheets can make collaboration difficult and result in islands of analysis and data inconsistencies.  An enterprise system for performance management such as Microsoft PerformancePoint Server can combine the convenience of spreadsheets with the power of centralized data storage and data warehousing. A CRM system would work well for collecting qualitative adjustments to quantitative forecasts and presenting forecasters with data drawn from an enterprise performance management system.
 
Measure, learn, and improve. To improvement forecasting, measure how well you are doing, learn from mistakes, and continuously refine the process. Learning to improve forecasts not only improves business efficiency but also encourages everybody in the organization to better understand the market environment and why products and services are selling or not. Forecasting is a key to operational excellence and greater insight into the business.

For additional resources on sales forecasting, I recommend the following two titles:

Sales Forecasting Management: A Demand Management Approach by John Tom Mentzer and Mark A. Moon

Excel Sales Forecasting for Dummies by Conrad Carlberg

Sales forecasting is a sore topic in many organizations. In the majority of cases, C-level executives demand sales forecasts from sales managers, who delegate the task to salespeople, who consider forecasting a tedious chore that distracts them from the important work of selling. Unsurprisingly, the salespeople deliver inaccurate and biased forecasts. Knowing the biases, sales managers at each level of the hierarchy adjust the results as do the executives who demanded the forecasts.

Most organizations settle for this result as good enough. Few compare the forecasts against actual sales to evaluate the accuracy. Fewer still calculate the cost of inaccuracy.

The costs of sales forecasting inaccuracy can be huge. Operations may produce either too much or too little of a product resulting in high inventory costs or lost sales. Or operations may under or over invest in plant and equipment. Logistics might deliver products to the wrong distribution sites resulting in higher transportation costs. Finance may misjudge revenue resulting in higher costs for managing cash flow and investments. Marketing may promote products that would have already sold beyond the firm’s capacity to supply or fail to promote products that needed a push to meet goals. The organization might hire too many or too few people to meet demand, a problem that affects service organizations as well as manufacturing.

It is not uncommon for functions such as operations and logistics to so distrust the forecasts that sales produces that they create their own forecasts, resulting in redundant, inconsistent forecasts throughout the organization.

Forecasts require looking into the future and depend on many variables beyond the control of the organization. Certainly forecasts will never be completely accurate but can be made more accurate through better processes and improved collaboration. I offer the following suggestions for organizations ready to tackle the forecasting challenge.

Know why you are forecasting. This is important for two reasons. First, by knowing why you are forecasting, you can decide what to forecast. Are you forecasting sales in revenue or units? Are you forecasting by year, quarter or month? Do you need to forecast by distribution or retail location or just by product line? In many cases, you will need more than one type of forecast because the forecasts serve more than one purpose.  Generally, marketing and finance are concerned with revenues, operations with units, and logistics with units and location. Operations will need short-term forecasts for material purchases and long-term forecasts for plant and equipment.

Second, by knowing why you are forecasting, you can know the value of forecasting. If raising the accuracy of forecasts by five percent could save millions of dollars a year in inventory cost, then it may well be worth investing in accuracy. If the organization runs with great flexibility and adapts quickly and cheaply to changing demand, then an increase in accuracy may not be worth the cost. Keep in mind that forecasting has a cost in time, salaries, and resources. Only make the effort to forecast accurately if there is a value to accuracy.

Do not confuse forecasting with planning and targets. Forecasts are what you believe to be the most likely sales outcome given an expected set of market conditions. Plans are what you intend to do to accomplish those sales outcomes. And targets are stretch goals intended to motivate salespeople. Confusing forecasts and targets will lead to faulty and biased forecasts that will misinform other functions in the business that depend upon the accuracy of forecasts.

Basing the forecast on the plan is just mere thinking without grounding in reality. To the contrary, the forecast should inform the plan. If forecasted sales do not achieve revenue needs, the plan must address through marketing and promotions what the company will do to increase sales. The work of planning and forecasting should be iterative but in no way should the forecasts be based on the wishes of the planner.

Combine quantitative and qualitative approaches. Salespeople have unique insights into the circumstances and plans of customers but are not so good at making large numbers of forecasts, discovering patterns in past sales data, and calculating the effects of multiple variables on sales outcomes. The trick to better forecasts is to use quantitative methods effectively to achieve what those methods can best achieve and then to adjust those results through the rich qualitative knowledge of the salespeople. Generally, salespeople are more effective at adjusting a quantitatively derived forecast than making up a forecast out of nowhere.

There are two types of qualitative techniques for sales forecast: time series and multiple regression analysis. In time series, we look only at past sales data and try to find patterns for trend and seasonality. In multiple regression analysis, we look for relationships between a predicted variable, sales, and a set of predictor variables – price, money spent on advertising, the consumer confidence index, housing starts, a stock index representing the health of business customers. By plugging the values for the predictor variables into an equation, the forecaster can make predictions for sales based on changes in the market environment. While these techniques require some study, they are not rocket science and can be mastered by anybody with reasonable mathematical skills. And they do not require very expensive statistical packages. Excel could do the trick.

Quantitative analysis falls short in that it cannot predict changes from past patters. Time series analysis assumes that the patterns of trend and seasonality will continue unchanged into the future. Multiple regression analysis assumes that the relationship between the predictor and predicted variables will continue unchanged. The value of using qualitative analysis to adjust the quantitative forecasts is that salespeople, marketers, and other knowledgeable people in the organization may well know that changes are underway that are not yet captured in the data.

Because qualitative analysis is the most expensive approach to forecasting because it takes up the most time, it is better to focus qualitative analysis on only the most important forecasts, perhaps forecasting only the leading product lines or the purchases of only the largest customers. This follows the 80-20 rule that a few products or a few customers will account for the bulk of revenue. Indeed, the salespeople will generally only have rich knowledge regarding these few products or customers.

I have often witnessed tension in CRM projects between scope management and innovation.

Organizations start out thinking of CRM as a project with a budget, a deadline, and a set of high-level requirements. These requirements include the demands of the managers who decided to pay for the project so they are generally not flexible. While there may be vague discussions of future phases to encompass a broader wish list, nobody gives that much thought. The focus is exclusively on the project at hand that will bring CRM to the organization.

Upon kick-off, the project manager (or in some cases business analyst, IT manager, or CRM consultant) must flesh out the detailed requirements as quickly as possible and then prevent those requirements from changing. Project managers know that requirements churn poses the greatest risk to the schedule and budget. With deadlines and budget usually tight and the high-level scope aggressive, the only hope for project success is a determined effort to avoid requirements churn.

The problem is that CRM projects are great learning experiences. As participants think through how they do their work, as they verbalize and visualize their processes, as they understand how their processes affect others across the organization, as they collaborate on how a new system could empower them to reach objectives, they start to rethink what they expected of CRM. People wonder whether they might just innovate.

Oh, no. Sounds like requirements churn. No, let’s not go there. That is a later phase for which there is no budget and no timeframe. So just forget it. Let’s squash that innovation right now lest expectations get out of control. Project managers must meet expectations and so they must control expectations.

Since I’m a PMI certified project management professional (PMP), I do know the PMI answer to this dilemma. It is scope management with a documented process for handling change requests. The problem here is twofold. First, few organizations put such change control boards in place. Second, such a rigid process is unlikely to foster innovation. Just tell somebody with a new idea that they must fill out a change request form and submit it to a committee of superiors and you can be sure they will not have any new idea.

Because CRM projects lead to learning and new ideas, agility is essential to respond to innovation as it happens rather than squash new ideas as a threat to the project schedule.  To consider what agile means, let’s take a look at a few of the guiding principles from the Agile Manifesto.

·         Our highest priority is to satisfy the customer through early and continuous delivery of valuable software.

·         Welcome changing requirements, even late in development. Agile processes harness change for the customer's competitive advantage.

·         Deliver working software frequently, from a couple of weeks to a couple of months, with a preference to the shorter timescale.

·         The best architectures, requirements, and designs emerge from self-organizing teams.

Applied to CRM, I would translate these principles into the following bullet points.

·         Break from the notion that CRM is just one big project with a defined beginning and end. The challenge of satisfying customers and meeting market threats never ends so why restrict the opportunity for employees to innovate.

·         Launch CRM with an expansive vision rather than a rigid scope. The vision should include making the organization outwardly focused toward its customers and markets and promoting continuous improvements in processes that add value to customers. And the vision should tie in closely with the organization’s mission statement and strategy. Obviously, the CEO would be a great person to deliver that initial vision at a kick-off meeting.

·         Include the widest range of stakeholders in the kick-off meeting even if initial phases will not directly affect them. Collaboration across the organization is a great catalyst for innovation.

·         Keep each phase, especially the initial phases, short so that the team does not have to push off new ideas far into the future. Do not insist that the first phase include every high-level requirement requested by the executive stakeholders. Some of these requirements may be replaced as new ideas emerge. And once people start using a system, they will have a better understanding of their requirement priorities.

·         Follow proven project management practices within each phase to reliably implement new features on time and within budget.

·         Encourage brainstorming and idea generation on an on-going basis. When new ideas emerge, create ad-hoc teams to pursue them even if the actual implementation is not in scope for the current phase. Do not just say “not now” and leave it at that.

·         Make sure initial phases focus on usability rather than maximizing features. If people hate using CRM, it is unlikely to become a platform for innovation.

·         Select a CRM platform that not only satisfies initial requirements but can adapt to meet evolving requirements. Microsoft Dynamics CRM is a great choice.

·         If you hire consultants to start you on your way with CRM, make sure team members learn as much as possible from the consultants both about how to perform business analysis and how to customize the CRM platform. In that way, the journey of continuous innovation through can continue after the budget for consultants runs out.

In other words, start out with a big vision but get there through small steps.

In a few of our recent CRM implementations, the Chief Operating Officer has played a key role in project success.

In our experience, COOs are good at bringing the right stakeholders to the table and encouraging them to cooperate.  Certainly, CRM brings much more value to organizations when it bridges the long-established divides between marketing, sales, service and operations.  COOs tend to think beyond traditional departmental silos and see the larger picture of where CRM can bring value.

Moreover, COOs think in terms of processes, which is the most critical and complex dimension of CRM implementations. Processes related to CRM are complex in that they involve multiple customer interactions over time, interactions between departments, and interactions between the organization and its vendors.

In addition, COOs help break through status quo thinking. When asked about software requirements, many business users will tell you how things are done now. Even if they realize that the status quo is inefficient, they may not feel as if they have a mandate to change. The status quo is rooted in the minds of employees as well as job descriptions, training manuals, performance appraisals, compensation plans, company culture, and formal and informal power relations. The COO knows the organization’s strategic direction and has the stature to push for change.

Finally, COOs bring effective leadership to a team. While usually too busy to function as a project manager or to make every project decision, a COO can impart a sense of purpose and urgency to a project and encourage a team to work through the difficulties.

This is not to say that COO involvement guarantees success or that the lack of it dooms a CRM project to failure. The job of the COO differs widely across organizations and industries and is sometimes limited to a single function such as manufacturing. Obviously, the question of who should lead a CRM project depends on the circumstances of the specific organization. However, I would recommend that organizations consider giving a leading role to the COO.

Recently I needed to display a CRM view in an IFRAME and built a solution using the approach outlined by Michael Michael Höhne's on his blog. Michael was himself building upon an idea suggested by Stefan on the CRM newsgroup microsoft.public.crm.developer. I've made a slight addition to this technique that I'd like to share with the community.

As is noted in Michael's article, it is fairly easy to display an associated view in an IFRAME, but since each entity supports only one associated view and it is not possible to customize the filter, the use of an associated view will not always meet the requirements.

A more flexible approach is based on Advanced Find, which builds a query by forming an HTTP POST request that returns a set of records in a view. The form elements included in the post define the FetchXML query, which determines which rows get returned, as well as the layout of the columns.

After defining an Advanced Find, you can extract the form elements by placing javascript:alert(resultRender.outerHTML) in the address bar and looking for the resultRender form. Another way to get at the resultRender form is to use the IE developer toolbar. After extracting the resultRender form, insert it into an html page with an onload event that posts it to /AdvancedFind/fetchData.aspx. You can then reference this html page in an IFRAME to display it on a CRM form.

However, you will frequently need to make the view dynamic by displaying only the records related to another entity, such as all of the contacts related to an account. Michael suggests using an ASP.NET page that would accept the parent record id and insert it into the FetchXML query. I took a somewhat different approach and used JavaScript to accomplish the same task.

Here are the steps:

1) Open the Advanced Find window and click Ctrl-N to open the browser with the menu. Define an Advaned Find query for Contact (or any other entity) and set the Parent Customer (or any other field that you need to filter by) to equal a specific customer. Make sure to define the columns by clicking Edit Columns.

2) Click on the icon for the IE developer toolbar. (Download the toolbar from http://www.microsoft.com/downloads/details.aspx?familyid=e59c3964-672d-4511-bb3e-2d5e1db91038&displaylang=en.) Right-click on the element <FORM id=resultRender> and select Element Source.

3) Copy all of the elements between the <FORM> and </FORM> tags.

4) Paste the form elements into the html page below between the FORM tags in the html below.

<html>
<head>
<SCRIPT LANGUAGE="JavaScript" SRC="ViewFunctions.js">
</SCRIPT>
<META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">
</head>

<body onload="setKey('id');resultRender.submit();">
        <FORM id=resultRender action="/AdvancedFind/fetchData.aspx" method="post">

     
        </FORM>
    </body>
</html>

5) Find the Condition element that contains the GUID for the account. Delete the uiname attribute and replace the GUID (including the braces) with __ID.

Before: <condition attribute='parentcustomerid' operator='eq' uiname='Blue Yonder Airlines' uitype='account' value='{D44D44AD-36D0-DC11-AA32-0003FF33509E}'/>

After: <condition attribute='parentcustomerid' operator='eq' uitype='account' value='__ID'/>

6) Save the html file to a subdirectory under the ISV folder on your CRM web server.

7) Save the following JavaScript code into a .js file named ViewFunctions.js and save it to the same directory as the html page.

function PageQuery(q) {
 if(q.length > 1) this.q = q.substring(1, q.length);
 else this.q = null;
 this.keyValuePairs = new Array();
 if(q) {
  for(var i=0; i < this.q.split("&").length; i++) {
   this.keyValuePairs[i] = this.q.split("&")[i];
  }
 }
 this.getKeyValuePairs = function() { return this.keyValuePairs; }
 this.getValue = function(s) {
 for(var j=0; j < this.keyValuePairs.length; j++) {
  if(this.keyValuePairs[j].split("=")[0] == s)
   return this.keyValuePairs[j].split("=")[1];
  }
  return false;
 }
 this.getParameters = function() {
 var a = new Array(this.getLength());
 for(var j=0; j < this.keyValuePairs.length; j++) {
  a[j] = this.keyValuePairs[j].split("=")[0];
 }
 return a;
 }
 this.getLength = function() { return this.keyValuePairs.length; }
}

function queryString(key){
 var page = new PageQuery(window.location.search);
 return unescape(page.getValue(key));
}

function setKey(id){
 var id = queryString(key);
 var fetch = resultRender.FetchXml.value;
 fetch=fetch.replace(/__ID/g,id);
 resultRender.FetchXml.value=fetch;
}

8) Place an IFRAME and your form and set the URL property to the html file. Make sure to check the "Pass record object-type code" check box.

After publishing, the result looks like the screen below.

By way of warning, this is not a supported customization because it depends upon the html structure in Dynamics CRM. When it comes time to migrate to CRM 5.0, these views may break. On the other hand, it is a fast and powerful technique for meeting many business requirements without writing custom Asp.Net pages. When CRM 5.0 is released, keep tuned to the newsgroup and blogs on ways to make this customization work in the new version.

I have finally updated FetchXML Builder with a few new features including IFD authentication support and the ability to auto-generate code for using QueryExpression.

This version was built against the CRM 4.0 web services but I have kept the CRM 3.0 version on the site for those organizations that have not yet upgraded.

FetchXML Builder is an interactive query builder for constructing and testing FetchXML statements. FetchXML is an XML-based query language for retrieving data through the Dynamics CRM web services.

Although FetchXML is not as powerful as SQL, it has certain advantages when used in CRM development. Unlike the QueryExpression class, FetchXML can be used to retrieve attributes from multiple entities. And unlike filtered views, FetchXML makes use of the CRM web services and does not require passing end user credentials to the SQL Server, which in some scenarios requires the configuration of Kerberos.

As a rule of thumb, I'd recommend using the QueryExpression class if you do not need to retrieve attributes from more than one entity. It is faster than FetchXML and it also uses the web services. If QueryExpression will not work, then go with FetchXML. If the query is too complex to form with FetchXML, you will need write a SQL statement to call the filtered views, which are database views in SQL Server that filter results based on the user's identity.

FetchXML Builder is a useful tool regardless of whether you will use QueryExpression, FetchXML or filtered views. Use the controls on the form to formulate a query. Click the Create FetchXML button to generate a FetchXML statement. Click Run Query to test it. Click on Create Code to copy either C# or VB.Net QueryExpression code to the clipboard. If you need to write a SQL statement, use the tool to discover the relationships between entities and the schema names, which will be helpful in writing the SQL.

Download FetchXML Builder