Customer Effective Blog

"Poor Man's" Business Intelligence for Microsoft Dynamics CRM

-This is a post that I wrote for the Microsoft Dynamics CRM Team MSDN Blog.  

Business Intelligence (BI) is getting a lot of attention these days. It makes sense that companies want to use the data collected by business systems like Microsoft Dynamics CRM to make better decisions, be more effective and impact the bottom line. Technologies from Microsoft, such as SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and Performance Point have made BI more accessible and more affordable.

But where should a company that is just starting out with Microsoft Dynamics CRM start to “dig deeper” into their data? BI is more of a strategy than a technology, and there are several easy, inexpensive ways to analyze CRM data before spending the time and money to roll out a more complex BI platform.

1. “Advanced” Advanced Find. Anyone who has spent much time in Microsoft CRM is familiar with Advanced Find; however, from my experience, most users just tap the surface. Sure, Advanced Find can give you a list of Accounts where the Address 1: State/Province = “NY,” but it also can filter the list by virtually any related entity, so advanced find can be a pretty powerful BI tool. For example, with one advanced find you can get a list of all accounts in NY that have had a phone call in the current month where a contact is named “Frank.”

Advances Find is even more useful now that CRM 4.0 can display fields from other entities in the search results, and take your query into report builder to further enhance the output.

Advanced Find Considerations

As CRM users gain more experience with CRM, they typically come across some questions that Advanced Find alone cannot answer.

a. “NOT” questions—these are queries where you are looking for records where certain data does NOT exist. For example:

• Which contacts have NOT been called in the past 3 weeks?
• Which accounts ordered in 2007 but NOT in 2008?

The issue with “NOT” questions and Advanced Find is that Advanced Find queries the data that is in CRM. With a NOT question, you are looking for data that does NOT exist in CRM. In the case of a one-to-many relationship (such as orders related to accounts), if you search by “does not equal,” you will return any record that has a related record that does not equal the search criteria, even if another related record exists that does equal the search criteria.

For example, say I want to see which accounts ordered in 2007 but did not order in 2008, and I have an account that has two orders in history, one in 2007, and another in 2008. If I do the following advanced find:

This account would be returned because it has a related order that is NOT in 2008, even though it also has an order that IS in 2008.

b. Multi-step ad-hoc analysis—this is where you want to get a data set and slice and dice based on additional criteria. Once you perform an Advanced Find, you can save the query and run it again, or use it as the starting point for additional advanced find queries; however, sometimes there is a need to be able to get a data set and then further refine it based on additional criteria. For example, a sales manager may want to see everybody that has purchased product X, but then remove everyone who has also purchased product Y.

So where do we go when advanced find doesn’t get us there?

2. Marketing Lists—they’re not just for campaigns anymore.

Marketing list functionality in Microsoft CRM takes advanced find to the next level. One really powerful feature of a CRM Marketing List is the ability to not only add members to a list based on an advanced find, but also being able to use additional advanced find queries to further refine the members of the list. You can:

• Add Members
• Remove Members
• Evaluate Members

Say that you want to see a list of Accounts that have ordered in 2007 but have NOT ordered in 2008:

Step 1: Create a new marketing list, selecting Account as the member type

Step 2: Click the Marketing List Members navigation bar link and click the “Manage Members” button.

Step 3: On the Manage Members form, select “Use Advanced Find to Add Members.” In your advanced find, select Accounts where related Order submitted date is after 1/1/2007 and before 12/31/2007. This will return a list of all accounts that ordered in 2007.

Step 4: Click the “Manage Members” button and select “Use Advanced Find to Remove Members.” In your advanced find, select Accounts where related Order submitted date is after 1/1/2008 and before 12/31/2008

The resulting list will include all accounts that ordered in 2007 but did NOT order in 2008. So now say that the sales manager sees the list, and he wants to further refine it to only include those companies that have purchased product X. He can now further refine the marketing list to only include accounts that meet those criteria:

Step 5: Click the “Manage Members” button and select “Use Advanced Find to Evaluate Members.” In your advanced find, select Accounts where the related Order’s related Order Product equals Product X

Evaluate Members compares the Marketing List Members to the results of an advanced find and removes any members from the list that are not returned by the advanced find results. Now the list will include only the accounts that ordered in 2007 but did NOT order in 2008, where the ordered product equals Product X.

Marketing list provide very powerful, yet easy to use out of the box functionality to slice and dice your customer data.

Marketing Lists Considerations

The main limitation of marketing list functionality is that marketing list members can only include Accounts, Contacts, and Leads. You cannot create marketing lists of custom entity records.

3. “Excel”-erate your CRM data

There is good reason why the most widely used business intelligence application in the world in Microsoft Excel. It is affordable, it is ubiquitous, and it is easy to use. And it is also very easy to interface Excel with your CRM data. You can connect directly to the CRM data source and do very complex queries from Excel; however, you don’t have to go that far to analyze your CRM data with Microsoft Excel.

The most basic interface between CRM and Excel is the export to Excel button that is found on any view in Microsoft Dynamics CRM (even on the marketing list we built in #2).

As with Advanced Find and Marketing lists, I have found that many users have a basic understanding of the export to Excel functionality, but don’t see some of the real power of taking your CRM data into Excel.

For example, take the process of territory rationalization. Company X has grown from a small company with 2 salespeople to a larger sales staff. When they started, they didn’t really have well defined territories, but now that they have grown, the sales manager wants to organize the sales rep’s territories by state. To do so, she wants to see where each rep has the biggest concentration of accounts to figure out the best way to draw the territory lines.

She wants to find out which sales representatives have accounts in North Carolina who also have accounts in South Carolina.

Step 1: Export the “Active Accounts” view to Excel, selecting the “Dynamic Pivot Table” option and selecting the following attributes:

• Account Owner
• State/Province

Step 2: Create the pivot table, selecting owner for the rows, State/Province for the columns, and accountid for the Value.

Step 3: Filter the Pivot Table data using Excel’s filtering capabilities. This is somewhat tricky—at first it appears that you cannot filter the columns in the pivot table; however, it can be done.

a. In Excel 2007, select the data tab

b. Click on a cell directly outside of the pivot table

c. Click the advanced filter button. The cell range of your pivot table should auto-populate. If it does not, you can fill it in.

d. Click the filter button—you should now see the excel drop-down filters on each of the pivot table column headers.

See a screencast of how to use advanced filter with pivot tables.

Now the sales manager can filter both the SC and NC column to NOT show blanks, and return a list of every sales rep with accounts in NC and SC. She can then continue to slice and dice the list based on geographic criteria. This is just a simple example—once you get your data into Excel, you can perform very complex analysis of your data.

The point of my post is that you do not have to delay implementing a business intelligence strategy with Microsoft Dynamics CRM if you don’t have the time or resources to invest in a BI platform. I would encourage you to become more familiar with the out of the box analytical capabilities of Microsoft Dynamics CRM. These tools will not satisfy every BI requirement, but they are a great way to quickly dig into CRM data and get some quick wins without having to invest a lot of time, money or resources. They can give you an easy, affordable way to determine what questions you should be asking so you will be ready for a more complex business intelligence platform when the time is right.

 

Technorati Tags: business intelligence,Microsoft CRM,Excel

How to stay up to date when hotfixes and knowledge base articles are released

Occasionally someone will ask me how to best stay informed when Microsoft releases new hotfixes and knowledge base articles for Microsoft Dynamics CRM.

Jim Glass from Microsoft has a great suggestion on his fabulous A CRM  Riff blog:  Register at www.kbalertz.com.

From the web site:

_____________________________________________________________________________________________

What is kbAlertz.com?
kbAlertz.com is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies you subscribe to. Since we scan the entire knowledge base, we also have a pretty good search system for you to use on the left menu.

Subscribing is easy. Simply fill out your e-mail address and password below. Then, select the technologies that you're interested in. That's it. Now you'll be kept up to date when Microsoft publishes updates to your favorite technologies!!!

______________________________________________________________________________________________

This is a fantastic, easy way to stay up-to-date every time that there is an kb article.

Technorati Tags: Microsoft CRM,KB,tips

Microsoft Dynamics CRM 4.0 List Web Part Released

The Microsoft Dynamics CRM team has announced that they have released the List Web Part for Microsoft Dynamics CRM 4.0. List Web Part can be installed on Windows SharePoint Services 3.0 SP1+ and Microsoft Office SharePoint Server 2007 SP1+ . The List Web Part is free for download and use.

From the description on the download page:

The List Web Part for Microsoft Dynamics CRM 4.0 provides a way to view and update Microsoft Dynamics CRM records using a Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 Web site. Microsoft Dynamics CRM users can create shared or personal List Web Parts of Microsoft Dynamics CRM records from a SharePoint Web site, open records in Microsoft Dynamics CRM 4.0 from the List Web Part, and create connected List Web Parts.

In addition to the features in the previous release, the new enhancements are

• Multi-tenancy : Allows to choose the CRM organization to view data from
• Supports CRM Internet-facing deployment
• Supports SSL configurations
• Supports both 32-bit and 64-bit environments
• Editing of CRM Server URL : You can now edit CRM server url at anytime
• Ability to open the current view in CRM to perform additional actions in CRM such as running reports, Quick Campaigns.
• Supports personalized SharePoint pages
• Enhanced Field-Field connections between two CRM List Web Parts. Subscriber CRM List Web part would automatically filter data by matching provider's GUID with all attributes of type provider, making it very easy to establish connections like Accounts - Opportunities
• Supports Row-Row connection with 3rd party web parts
• Supports SharePoint solution deployment ( useful in server farms ) and UnInstall
• Will soon be available in all 25 languages supported by CRM 4.0.

System requirements:

• Microsoft Dynamics CRM® 4.0
• Microsoft Windows® SharePoint® Services (WSS) 3.0 SP1+
• Microsoft® Office SharePoint® Server (MOSS) 2007 SP1+

Download Link: http://www.microsoft.com/downloads/details.aspx?FamilyID=3B6EB884-EC15-4288-A2A3-D0B47E057458&DisplayLang=en

Note: All list web part users should have EntityRead, AttributeRead and RelationshipRead privileges granted in their security roles to enable List Web Part to fetch CRM metadata.

Technorati Tags: SharePoint,Microsoft CRM,web part,list

6 New Videos on the CRM Accelerators

If you're doing any Microsoft Dynamics CRM 4.0 development, the CRM Accelerators up on CodePlex are pure gold. The CRM team just added 6 more videos to the series.

• An Introduction with Reuben Krippner
• Enterprise Search
• Notifications (RSS)
• Event Management
• eService (ASP.NET Portal)
• Analytics

I haven't seen all of them yet but of the ones I've seen, they're excellent (Analytics, Enterprise Search and Notifications (RSS)).

Technorati:
Dynamics CRM, CRM Accelerator, Codeplex

A cool way to use Email Templates

Recently, we had a project that entailed sending out an email via Exact Target every time a request for information came in. The email that was sent out was rather complex and was a form with about 50 fields that needed populated.

The first thing that crossed my mind was doable but very monontonous.  What I was going to do was query the CrmService to retrieve a specific instance of an Account, and then build the HTML form, adding in the specific fields where warranted.  This was really awkward b/c manually writing out HTML (particular HTML that's this long) is very error prone. It's not very flexible (every change to the layout would entail changing the code and redeploying it). And of course, it's error prone (it's really easy to miss a single tag).

What I ended up doing was creating an email template based on an account. I pasted a copy of the form the client had provided us into the template and just substituted the value fields with slugs.  The whole process took about 20 minutes. 

Now, whenever a new request came in, I had the Guid of the account in question. I stored the Guid of the template in the configuration.  So all I needed to do was effectively 'apply' the template to the account and viola - I'd have the formatted HTML I needed. Going forward, if the template needed to change, one of two things could happen. They could simply use CRM's built in editor and make the changes they needed, or they could create a new template, and then just replace the old template's Guid (which was stored in the configuration XML) with the new template's Guid.  Using the following code, you'll get back the HTML for the body (although depending on which section you grab, you could get other values). Just insert this HTML inside the Body tags and viola', you're done:

InstantiateTemplateRequest TempRequest = new InstantiateTemplateRequest();
TempRequest.TemplateId = new Guid("TemplateGuid");
TempRequest.ObjectId   = new Guid("AccountGuid");
TempRequest.ObjectType = EntityName.account.ToString();
InstantiateTemplateResponse CurrentResponse = (InstantiateTemplateResponse)ServiceInstance.Execute(TempRequest);
email theEmail = (email)CurrentResponse.BusinessEntityCollection.BusinessEntities[0];
HtmlBody = theEmail.description;
           

HtmlBody is simply a String property which I use when building the rest of the email. The description property of theEmail is the formatted HTML complete with all the HTML and values from the entity filled in correctly. And that  Html is simply  <HTML><BODY>  HtmlBody</BODY></HTML>. This saved hours of time and resulted in a very flexible solution that was very user friendly.

Configuration Wizard Cannot Connect to CRM

Technorati Tags: Microsoft CRM,Dynamics CRM,Outlook

I recently set up a CRM 4.0 test environment, copying an existing CRM database and importing in via the deployment manager.  Everything went smoothly until I tried to connect to the environment with Outlook.

The Outlook configuration wizard was able to see the environment and give me a list of the organizations, but after selecting an organization, it gave me an error that configuration could not connect to the Microsoft CRM 4.0 server.

After checking that all of the services were running correctly and that my client was connected to the network, I found a helpful tip from Robert MacLean in South Africa.  While his post was specifically about changing the port of a CRM web site, it pointed me in the right direction.

The problem was that the server address of the CRM Discovery Service was still set to the old server name and port in the DeploymentProperties table of the MSCRM_Config database.  After updating that row in the database to the new server name and port, Outlook configured beautifully.

See Robert's post here.

Disable Contact Synchronization For All Users

Microsoft Dynamics CRM 4.0 for Outlook allows users synchronize CRM contact and calendar events with their local Outlook contacts and calendar. Each user can configure their settings to determine which pieces will synchronize with Outlook, and using the local data settings.  In a previous post I described the ins and outs of configuring the contact synchronization rules.

So what if you want to prohibit users from synchronizing contact data to their local machines?  In industries where data is sensitive, there can be a requirement to restrict having local copies of contact data.  If you wish to prohibit ALL data synchronization with Outlook, you can disable synchronization with Outlook in security roles.  Keep in mind that this will prohibit all data synchronization with Outlook--not just contacts, but also appointments, tasks and phone calls.

So what do you do if you want to ONLY prohibit synchronization of contact data?

If you want to restrict contact synchronization for all users, yet allow them to synchronize calendar events, run the following SQL against your MSCRM database:

Update UserQueryBase set StateCode = 1 where QueryType = 256 and ReturnedTypeCode = 2

This database change will prevent any user from synchronizing contacts from Microsoft CRM to Outlook.

Thanks to Alex Farquharson for bringing this to my attention.

 

Technorati Tags: Microsoft CRM,Dynamics CRM,synchronization

Cannot Access Report in SSRS Report Manager After Installing CRM SSRS Data Connector

Running reports directly from the Report Manager is a useful method of troubleshooting.  However, after installing the SSRS Data Connector reports are not accessible with standard domain credentials.  You will be able to browse to the datasource and select the report but will then be prompted for credentials.  The standard format one would expect to use is domain\username and password which will produce the following error that references 'An error has occurred during report Processing', 'Cannot create a connection to datasource ...', and 'Guid should contain 32 digits with 4 dashes' as seen below:

The login credentials after installing the SSRS Data Connector are your systemuserid for the name and organizationid for the password.

To obtain these run the following query in SQL Mgmt Studio against your 'org'_MSCRM database:

SELECT firstname,lastname,systemuserid,organizationid from systemuserbase

Within your row, the SystemUserID GUID is the username, and the OrganizationID GUID is the password.

Copy/paste those values directly into the login area when accessing the report and you're back in business.

Two important notes Joel Lindstrom brought to my attention regarding this post:

1.  This only applies to reports published through CRM 4.0--other reports on your report server won't be affected.

2.  If you don't want to have to use the GUIDS, you can use the "publish for external use" option in the report record under more actions.  This creates a copy of the report that has the report name as the rdl name and will authenticate with Windows authentication.

Integrating the MSN Search Service with CRM

Once you start thinking, pardon the cliche', out of the box, you'll quickly realize there are virtually endless applications of CRM. In my last post I showed how to use the Virtual Earth Service to get Latitude/Longitude coordinates for an address and how to plot directions with those coordinates.  That's a pretty common thing people may want to do.

That got me thinking about my last two books.  When they first came out, I neurotically checked the Amazon.com rankings and comments on a daily basis.  It was a profound waste of time in many regards b/c there wasn't much I was going to do with that information, but because the ratings were quite high at first, it weighed heavily on my mind.

Think about your company though... chances are that for one reason or another, you care about the stock price if it's publicly traded. And it's very common for companies to post their stock price in various public places and have it updated daily (I'll be covering that soon too ;-).

Similarly, you may care about your rankings on the various search engines.  I'm going illustrate how to check for your company's position using the MSN Search service. Although this particular implementation is a bit simplistic, it can be very easily modified to let you know all sorts of other information. This particular code will check for a company name (enclosed in quotes to filter out noise) and then iterates through the top 10 results and checks whether or not your company's URL is listed in the top 10. Instead of using your company name, you might want to use something else that you want your company to be identified with.  You might want to use filtering criteria or use several different search terms.  All of that is quite simple once you get the concept down.

To run this, I just used the code provided as an example at the MSN Search site and tinkered with it a bit.

try
{
     MSNSearchService s = new MSNSearchService();
     SearchRequest searchRequest = new SearchRequest();
     int arraySize = 1;
     SourceRequest[] sr = new SourceRequest[arraySize];

     sr[0] = new SourceRequest();
     sr[0].Source = SourceType.Web;

     searchRequest.Query = \"ourAccount.name\";
     searchRequest.Requests = sr;
     searchRequest.AppID = "YOURAPPIDHERE";
searchRequest.CultureInfo = "en-US";
     SearchResponse searchResponse;

     searchResponse = s.Search(searchRequest);
     Boolean AreWeInTop10 = false;
     foreach (SourceResponse sourceResponse in searchResponse.Responses)
     {
          Result[] sourceResults = sourceResponse.Results;
          if (sourceResponse.Total > 0)
          {
            ourAccount.TotalMSNResults = sourceResponse.Total;
            Console.WriteLine();
          }
          foreach (Result sourceResult in sourceResults)
          {
            if ((sourceResult.Url != null) && (sourceResult.Url != String.Empty))
            {
              if (sourceResult.Url.IndexOf(ourAccount.websiteurl) > -1)
              {
                Console.WriteLine(sourceResult.Url);
                Console.WriteLine("You're in the top spot");
               AreWeInTop10 = true;
              }
           }
         Console.WriteLine("*****************************************************");
        }
      }
     Console.WriteLine(AreWeInTop10 ? "We are in the top 10" : "We're not in the top 10");

     Console.WriteLine("Press any key to exit.");
     Console.ReadLine();
}
catch (System.Web.Services.Protocols.SoapException fault)
{
     Console.WriteLine(fault.Detail.InnerText.ToString());
     Console.WriteLine("Press any key to exit.");
     Console.ReadLine();
}
catch (System.Net.WebException webx)
{
     Console.WriteLine(webx.ToString());
}




So what did we do?  I added an attribute named TotalMSNResults to the Account entity.  I just pulled down my company's account record and used its Name property as the Search criteria. Again, this could be anything that we wanted and we're not just limited to the company name.  Just substitute whatever you want to search for there. Customer Effective has two words in it which would generate extra noise in the results so I wrapped it in Quotes so that it's treated as a literal (I highly encourage this b/c you don't want results back that are unrelated to your company.  After the routine runs you'd want to call the CrmService's Update method so you could save this information.  Then, I just loop through the results and check the URL of each of them.  If I find my company's URL in the results, I know that I'm in the top 10.  A more realistic application might be to record which position we were in and store that as well.  If you're using your company name then it's likely you'll be in the 1st position most of the time, but if you were searching for something your company does so you can see where you fall on the search results, you don't know for sure where you'll end up (hopefully in the top 10). So I just have some simple logic to echo whether or not you made the top 10 to the console window.

What might be a more realistic application?  You could use a list of all of your primary competitors and each evening, run a workflow that goes out and runs this query with each competitor name. You'd find there ranking and store it in an attribute you created called CurrentMSNRanking.  You would probably want to do the same for other search engines (all of the big ones have API's).  From there you could build a simple report that shows where everyone is. 

Similarly, you could run the query on search terms you hope to be associated with and record each of the top X results using that output to develop intelligence about your competitors. Your sales and marketing department will look for its own metrics but these are things that most companies care about.

Another application would be to tie this to a Campaign you created.  You could use search results as a way to guage that campaign's effectiveness.  You could use the same type of mechanism to find customers too. The biggest problem you'll run into in all likelihood is getting back too much noise but if you familiarize yourself with the respective search engine's advanced features, it's not too hard to filter out most of the noise.  Again, you could add an attribute called SearchCriteria to your Campaign entities and then you could modify those terms easily through the CRM UI. I used an Account entity here but you can use any entity you want within CRM (or none at all although that sort of defeats the purpose). By attaching it to a campaign, you'll easily be able to change the words you search for based on that campaign which would be key to tracking the reach and effectiveness of a given campaign.

In summary, the main purpose of this illustration is to just show how easy it is to integrate different features, such as a search engine, into your CRM system to gather critical information for your business.  You could use it to gather information about your company, your company's accounts/customers, campaigns or whatever else you can think of. And as simple as this is to implement, it's probably worth some experimentation.

Extending CRM with Microsoft LIVE Services

Microsoft's Dynamics CRM is an amazing tool. As I learn more about it, I increasingly realize it's an even more amazing platform.  My background has been exclusively in development and I still catch myself leaning toward performing tasks outside of CRM even though the platform has been more than capable of doing anything I've needed to accomplish (and in most cases, making it a whole lot easier). Because CRM's integration story is such a good one, one way I've taught myself more about the product is by integrating other technologies with it to come up with cool implementations (at least I think they're cool) that aren't the sorts of things we typically think of when we think of CRM. Ultimately,this means that "we can't do it" is seldom an answer you should hear with respect to CRM. If CRM doesn't support something out of the box, you almost certainly can extend CRM to do it   So I'm planning a 5 part series (I suspect it will grow much larger but that's all I'm promising for now) on doing things with CRM that illustrate how easy it is to extend functionality to handle several non-traditional scenarios.  Here's the series breakdown (I'll change the text to hyperlinks as the articles are posted):

1. CRM and Windows Live Part I
2. CRM and Windows Live Part II
3. CRM and Sharepoint
4. CRM and Twitter
5. CRM and Facebook

So without further ado...

One of the stated purposes of a CRM system is tracking relationships of various sorts. These might be customers, suppliers, leads, distributors or all of the above.  The point is, most of us use CRM to manage critical business relationships.  While telecommuting and LiveMeeting scenarios are increasingly popular, there's still a lot of face to face contact involved in the sales process and course of operations for most companies.

Tracking addresses is one of the most basic functions we perform with CRM.  As such we have most of the information we need to figure out how to visit a client. But knowing an address is only part of the story. Having directions is often the other part. 

To get directions, we often use our vehicle navigation systems if we have them, but not everyone has a navigation system and more often than not, people turn to Virtual Earth, Google Maps, Mapquest etc to get directions.  I'm going to show you a basic implementation of getting directions from my office to a client site (or several client sites) using Windows Live and Virtual Earth.

In order to do much with mapping software, you'll typically need Latitude and Longitude coordinates of the address.  While we normally keep addresses stored in CRM, we don't typically keep Latitude/Longitude  in there, although I'm going to show you an easy way to get that done too.

The first thing you need to do if you haven't already done so is register for a Live developer account. Parts of the Virtual Earth API can be accessed exclusively through web services, other parts require certain libraries. For this example, you only need the Web Services portion of the Virtual Earth API.

To avoid reinventing the wheel, please take a look at this Getting Started article on Virtual Earth which I'll use parts of for this article.  First, add a web reference to the Geocoding service, the Routing Service and the Staging Token service. Next, create a Web reference to the .Crm Service (CrmService). For convenience, I typically make a Services class and hold each instance of my web services inside of it coupled with any authentication information that's needed and anything that needs retrieved from configuration settings. That way it's easy to reference them and all calls to the service are treated consistently. For the Crm Service, a typical implementation looks like this:

public staticCrmSdk.CrmServiceServiceInstance
{
  get
  {
     if (_serviceInstance == null)
     {
     _serviceInstance = new CrmService();
     _serviceInstance.Url = "http://YOUR_IP:YOUR_PORT/MSCrmServices/2007/CrmService.asmx";
     CrmAuthenticationTokensessionToken = new CrmAuthenticationToken();
     sessionToken.AuthenticationType = 0;
     sessionToken.OrganizationName = "YOUR_ORG"; 
     _serviceInstance.Credentials = new NetworkCredential("USERNAME", "PASSWORD", "DOMAIN");
     // Can also use DefaultCredentials instead, which will cause the user to be prompted for their credentials
   // Simply uncomment the line below and comment out the line above where .Credentials are set      to use Default Credentials
   // _serviceInstance.Credentials = CredentialCache.DefaultCredentials;

     _serviceInstance.CrmAuthenticationTokenValue = sessionToken;

     }
    return_serviceInstance;
   }
}
.....

Now, you'll need to create the following methods (again, for brevity I'm going to leave them out here but they are all available at the Getting Started link above 

1. SetToken()
2. CreateRoute()
3. GeocodeAddress()

I wrote the following method to simply go to the CrmService and pull back all of my existing accounts. Depending on exactly what you want to accomplish, this method will obviously change:

public static ListGetAccounts()
{
     ListCurrentAccounts = null; 

     //Collection that holds the name of the columms that should be retrieved
     ColumnSet columnsToInclude = new ColumnSet();
     columnsToInclude.Attributes = new String[] { "accountid", "name", "address1_line1",  "address1_city", "address1_stateorprovince", "address1_postalcode" };

     //Create a query expression to retrieve the fields
     QueryExpression mainQuery = new QueryExpression();
     mainQuery.EntityName = CrmSdk.EntityName.account.ToString();
     mainQuery.ColumnSet = columnsToInclude;

     //Retrieve opportunites and store in BusinessEntityCollection
     BusinessEntityCollection allAccounts = null;
     try
     {
       allAccounts = ServiceInstance.RetrieveMultiple(mainQuery);
     }
     catch (SoapException soapError)
     {
       MessageBox.Show(soapError.ToString());
     }
     catch (WebException ex)  
     {
       //Just eat it for now
       MessageBox.Show(ex.ToString());
     }
     if (allAccounts != null && allAccounts.BusinessEntities.Length > 0)
     {
       CurrentAccounts = new List();
     }
     foreach (BusinessEntity eachAccount in allAccounts.BusinessEntities)
     {
       CurrentAccounts.Add((eachAccount as account));
     }

     return CurrentAccounts;
}
....
This gives me a list of all of my current accounts.  In the original implementation, I'd use this to get an account name, use the account name to extract an address, and pass the address to the GeocodeAddress method which would return the Latitude and Longitude coordinates found. The only problem is that it inserts the text Literals "Latitude: " and "Longitude: " which get in the way (when you pass them to the CreateRoute method, they need to be in Latitude, Longitude format. If you pass in multiple addresses, you need to separate each Lat,Lon group with a semicolon.Anyway, I used a simple  call to the Replace method to get rid of the extraneous text:

GeocodeAddress(textInput.Text).Replace("Latitude: ", String.Empty).Replace("Longitude: ", ",").Replace("\n", String.Empty));

I put this in a textbox control and for each addition, I'd just add a semicolon to it. In the end, I'd have a semicolon delimmited list of Latitude and Longitude groups. At that point, I'd simply pass that value into the CreateRoute method and I'd get a String value returned which was step by step directions to each location I included. (A nice option is to include return directions.  To do this, just append the Latitude/Longitude for the starting point at the end of your list. You already have this since it's the first item in your group)

     StringResults = CreateRoute(textInput.Text);
     labelResults.Text = Results ;


So let's say you put in 10 different locations (10 Latitude/Longitude pairs separated by semicolons), you'd get a string of formatted steps showing you the directions on how to get to each leg. It's amazingly easy to use.

I would posit though, that repeatedly looking up addresses' Latitude/Longitude  is kind of wasteful in most cases (unless they change constantly which is seldom the case). Since we now have Latitude and Longitude attributes for an account, why not just store them there?   This could be accomplished a few ways. One way would be to create a Plug-in that each time an Account was Created or Updated, simply grabbed the address (if available), retrieved the Latitude/Longitude , set those two properties and saved the record. Using the Replace function I included above, your result would be something like "47.608, -122.337"   You could use a call like this to get the Latitude/Longitude values which you could in turn set the current entity's address1_latitude and address1_longitude properties to:

StringFormattedAddress = String.Format("{0}, {1}, {2} {3}\r\n", act.address1_line1, act.address1_city,
act.address1_stateorprovince, act.address1_postalcode);

String[] LatLonGroup = GeocodeAddress(FormattedAddress).Replace("Latitude: ", String.Empty).Replace("Longitude: ", ",").Replace("\n", String.Empty).Split(',');
act.address1_latitude = new CrmFloat();
act.address1_latitude.Value = Convert.ToDouble(LatLonGroup[0]);
act.address1_longitude = new CrmFloat();
act.address1_longitude.Value = Convert.ToDouble(LatLonGroup[1]);


If you had the Plugin fire on both Create and Update, you'd catch the updates and each time the record changed, you'd go and get the Latitude/Longitude combination for those attributes and could be relatively sure things were always current.  If you had a failure (which is certainly a possibility when calling external web services) you wouldn't want to stop the record from being created or updated though, so this approach may not be as strong in reality as it is in theory.
 

To address that, you could create a workflow that ran nightly. This workflow could go through each of your Accounts and set the corresponding properties.  The code to accomplish this would look like the following (assuming you used the GetAccounts method I illustrated earlier

SetToken();
ListCurrentAccts = GetAccounts();
foreach (account act inCurrentAccts)
{
   StringFormattedAddress = String.Format("{0}, {1}, {2} {3}\r\n", act.address1_line1, act.address1_city,
   act.address1_stateorprovince, act.address1_postalcode);
   String[] LatLonGroup = GeocodeAddress(FormattedAddress).Replace("Latitude: ",String.Empty).Replace("Longitude: ", ",").Replace("\n", String.Empty).Split(',');
   act.address1_latitude = new CrmFloat();
   act.address1_latitude.Value = Convert.ToDouble(LatLonGroup[0]);
   act.address1_longitude = new CrmFloat();
   act.address1_longitude.Value = Convert.ToDouble(LatLonGroup[1]);
   ServiceInstance.Update(act);
}


This approach isn't perfect either.  That's because it would update every single record and chances are, at best only a few addresses would likely have changed each day. Depending on your licensing model, it would consume tokens and in most cases that would be unnecessary. Those concerns are for another discussion, this one just focuses on how to do it.

Since we're talking about convenience, this overall approach is still lacking. Although we now  have the  Latitude/Longitude stored in the record, the user would still have to ostensibly hit a button we put on the account form to have the directions given to them. We'd get the directions and show them in another window, or just take them to the Virtual Earth page and show that information there.  To me, that's still kind of lame.  So what do I propose?  

I'd recommend creating an additional attribute for the account entity called DirectionsFromOffice. Once the attribute is created, you could simply add the following line of code to either of the above blocks (the one for an individual update or the one for updating each record):  

 DirectionsFromOffice = CreateRoute(FormattedAddress);
  // or
  act.DirectionsFromOffice = CreateRoute(String.Format("{0}, {1}", act.address1_latitude, act.address1_longitude));


The total distance is available in the results and can easily be extracted so you could create another field called DistanceFromOffice and using the same approach as above, set it while you're setting everything else. This has the added benefit of giving someone a good approximation of how much distance they'd be travelling in a given day as they went about visiting customers.  The main shortcoming with the approach that stores directions in the record is that it's likely to get obsolete (addresses might not change very often but due to traffic, construction etc the 'best route' might. ClearFlow is just one mechanism to check traffic that Virtual Earth offers). It also doesn't do much for you if you had 5 clients you were visiting that day since each set of directions would show you how to get from your office to the client site, not from one client site to another.  To that end, I think there's probably no way around having a button and another form to show the results on to make this useful for most cases.  But remember, to get directions for multiple legs, all you need to do is grab the separate the Latitude/Longitude  for each customer with a semicolon and call the CreateRoute method. If the Latitude/Longitude  are already in place, it would be trivial to get all of the values of the customers on your trip in one place, add a semicolon between them and displaying the results.  You could grab the search results on a search form for instance or use multi-select on a grid.  Each business will have different needs and out of the box, this should be a good place to start.

In the next part of this series, I'm going to walk through using a phsyical map and pushpins  You'll be shocked how easy it is to do that once you have the above code in place.  

Manually Uninstall Microsoft Dynamics CRM 4.0 Server

I recently had a harrowing experience--I installed Microsoft CRM, and half way through the install process, the install failed due to a permissions issue.  Typically if this happens you run the install again and choose uninstall or go to add/remove programs (in Windows 2003) and remove Microsoft Dynamics CRM, fix the problem, then try again.

This time, when I tried to uninstall CRM, it returned several errors and would not let me remove it.  I manually deleted the CRM program files and every Microsoft Dynamics CRM string in the registry that I could find, but it would not let me reinstall.

I pulled out my hair for a while, but finally I came across KB article 946980: How to manually remove Microsoft crm 4.0.

Microsoft has put together instructions on how to completely remove CRM 4.0 server.   My problem was I had not stopped the indexing service, and I had not removed the assemblies from the GAC.

After following the instructions, I was able to successfully reinstall with no issue.

Technorati Tags: Microsoft CRM,Dynamics CRM,installation,troubleshooting

Best quote ever on why and when to CRM!

From Gene Marks at Business Week on Dec. 2nd:

"Customer Relationship Management (CRM) Software. What's the tipping point for buying CRM software? This is not a gray issue; it's black and white. It's strictly mathematical. You buy CRM software because you can mathematically prove that it will help you get more sales. How? By making sure that all quotes are being pursued to their very end. By making sure customers who haven't heard from you in the past six months are hearing from you. By making sure prospects who may in the future think about buying something you sell are hearing from you frequently enough to make them come to you first. By making sure you don't look like a dope when customers call because you don't know who communicated with them last and what problems they may be having. Think of all of all these situations you've encountered in the past three years, calculate how much revenue you've lost, and then compare that against the cost of a CRM system—and voilà! You have your answer. Here's another tipping point: You suddenly discover that the airline industry provides better customer service than your company. That's enough to make anyone take action."

Read the whole article here: http://www.businessweek.com/technology/content/dec2008/tc2008121_663967.htm

Bulk Record Deletion in CRM 4.0

There are a few different ways to be able to bulk delete records in CRM 4.0. If you use the standard Import functionality, or the Data Migration Framework, you do have the ability to bulk delete records created by those methods, but what about deleting all your records, including those not imported by the previously mentioned methods?

Luckily, you can create custom bulk deletion jobs through the SDK and then monitor their progress through the CRM UI, which is pretty neat. What I’ve done is create a small command line utility that I use to wipe out all records from one or many entities. The entire process is pretty simple and straightforward and I’ll show you how my application is written.