Sonoma Partners Microsoft CRM and Salesforce Blog

Metablast - get your Microsoft CRM entity schema data NOW

As we’re working with our clients on their CRM implementations, we frequently need to generate schema documentation about the various entities and data fields in their system. Of course it’s just good project management to provide this type of documentation, but it also helps in other client scenarios such as prepping for a data migration or planning for a third-party system integration.

Back in the Microsoft CRM 4.0 days, you could use tools such as the Microsoft CRM 4.0 Form Reporter or the CRM 4 Document Generator to generate this type of entity schema information. Unfortunately neither of these tools work with Microsoft Dynamics CRM 2011.

We recently spent a little time to create our own Microsoft CRM schema documentation generator that we call Metablast (and yes it works with Microsoft Dynamics CRM 2011!). The concept is pretty simple really, you specify a few parameters including:

  • Microsoft CRM website URL
  • Organization name
  • Entities that you want to generate documentation for
  • Output file name

Metablast then runs through the CRM API to automatically generate a CSV file with all of the data fields for the entities that you specified. For option set data fields, it also outputs all of the picklist values for the field as well. Very handy!


The user interface isn’t sexy (it’s a command line tool as shown below), but we promise it will save you HOURS of time.


If you want to check out Metablast, you can download it (for free) from our website. Please keep in mind that we can only provide Metablast support to our customers, but we think you’ll like it just the same!

Topics: Microsoft Dynamics CRM 2011

Supercharge your Dynamic Excel Pivot Tables into SQL Server Reporting Services in 15 minutes

While Microsoft CRM offers plenty of different reporting and analysis options, each reporting tool has its own unique pros and cons. One of the most popular reporting tools in Microsoft CRM is the ability to create Dynamic Excel files using Pivot Tables and Pivot Charts. You can crank out some very powerful reports without writing a single line of code. While Pivot Tables are a great reporting option, they do have some limitations.  One of the biggest issues that I see is that many of our customers’ end users aren’t comfortable working with Pivot Tables. Instead of trying to train everyone on how to use Pivot Tables, another option is to convert the Pivot Table report output into a simpler report format so that the entire audience can use it.

We’re going to show how to convert a dynamic Excel Pivot Table into an advanced SQL Server Reporting Services (SSRS) report. Even better, you can do this within 15 minutes!

Since many of our customers focus on sales forecasting reports, let’s create an Opportunity Pipeline report for our example. First, I will open up Microsoft CRM and navigate to the Open Opportunities view. In my example, I have 18 open opportunities.


Let’s assume that these are the columns you want to include in your report. On the Export to Excel dialog, I’ll select Dynamic PivotTable.


In the PivotTable Field List in Excel, I will add the Potential Customer field to the Rows, Probability to the Columns, and set the Values equal to the Sum of Est. Revenue.


Just like that, we’re done with our Dynamic Excel report. Now we’re going to take this Dynamic Excel file and supercharge it into a SQL Server Reporting Services report. SSRS reports offer you advanced reporting features such as:

  • You can schedule SSRS reports for automatic email delivery (i.e. send the opportunity report to sales management every Monday at noon).
  • You can create sub-reports and drill-through reports.
  • You can include data from multiple entities in the report query .
  • You can setup report caching, snapshots, etc.

While the Report Wizard in Microsoft CRM allows you to create SSRS reports, unfortunately it doesn’t let you pivot the data into the table/matrix style the Excel file output shown above.

First, we need to grab the query from the data connection from the Excel file. Getting the query using this technique makes your life easier as you don’t need to know the CRM database to get the SQL query needed for the report. It also ensures that your SSRS report conforms to the Microsoft CRM security settings(because the query in Excel connects to the Filtered Views designed for reporting). To get the connection information, click on the data tab in Excel and select ‘Connections’.


Open up the properties of the connection named ‘Connection’. On the Definition tab, copy the text found in the Command Text section:

select top 10000 as 'name', opportunity0.estimatedvalue as 'estimatedvalue', opportunity0.estimatedclosedate as 'estimatedclosedate', opportunity0.customeridname as 'customeridname', opportunity0.opportunityratingcodename as 'opportunityratingcodename', opportunity0.closeprobability as 'closeprobability', opportunity0.opportunityid as 'opportunityid', opportunitycustomeridcontactcontactid.emailaddress1 as 'opportunitycustomeridcontactcontactid.emailaddress1' from FilteredOpportunity as opportunity0 left outer join FilteredContact as opportunitycustomeridcontactcontactid on (opportunity0.customerid = opportunitycustomeridcontactcontactid.contactid) where (opportunity0.statecode = 0) order by asc, opportunity0.opportunityid asc

Quick tangent: The ‘top 10000’ portion of the query ensures that the user does not export more than 10,000 rows (the default limit) into Microsoft Excel. That value can be changed in the database if necessary. Alternatively, if you want to get around that limit in an Excel report, you can modify the value in the connection properties area and click OK. We should probably keep that a secret though. Shhhhhhhhh.

OK, back to the report. Now that we have our query, we will open up Business Intelligence Development studio and use the report wizard to create the SSRS report.

Note: Business Intelligence Development studio is part of the Visual Studio install. If you do not have access to this tool, please see your Administrator

First, we will create a project. To do so, click the Create Project link.


Next, select Report Server Project Wizard, give the report a Name and click OK.


The Report Wizard appears. On the Select the Data Source page, click the Edit button and enter the Server and Database name for your Microsoft CRM installation.


Click OK and click the Next button to go to the Design the Query page. Paste the query captured from Excel in the Query String field and click Next.


On the Select the Report Type page, select Matrix and click Next. A matrix report represents a pivot table in SSRS.


In the Displayed Fields area, move closeprobability to the Columns area, customeridname to the Rows area, and estimatedvalue to the Details area.


Click Next. On the Choose the Matrix Style page select Corporate and click Next. Click Next once again to get to the Completing the Wizard page. Give your report a name, click the Preview Report checkbox and click Finish


There you have it…the basic SSRS report is created.  


Apply some simple formatting in SSRS on the design change, and you can upload the report (RDL file) to CRM and you are done!


Does your current CRM system stop at franchisees?

When we were at the IFA Annual Convention recently, we heard lots of talk and emphasis around franchisee profitability. It's pretty simple really, everyone knows that successful and profitable franchisees leads to successful franchisors.

We’ve been helping franchise organizations deploy CRM systems for years, and I would describe most of these deployments as franchise management systems. That is, the franchisors use Microsoft Dynamics CRM to help them manage and keep track of their franchisees. Typical franchise management systems include features/modules such as:

  • Franchisee recruitment and application management
  • Operations, legal and compliance
  • Property development and construction management
  • Franchisee customer service

Almost all of these CRM features are focused on helping the franchisor serve the franchisee, but the CRM system stops with the franchisee. However, we’re seeing a trend lately where more and more franchisors are asking about how they can provide their franchisees with a tool like Microsoft Dynamics CRM so that their franchisees can manage their customers. Some CRM systems, like Microsoft Dynamics CRM, can handle the needs of both franchisors and franchisees on one platform. By leveraging one CRM platform from start to finish, franchisors can really get the complete and integrated picture of how their franchise system is performing.

We recently spoke with PostNet's President and COO Brian Spindel about how CRM systems can help drive your franchisee’s profitability, and we wanted to share the article with you. You can read the article online here.

With Microsoft’s very aggressive pricing of just $34/per user per month* for Microsoft Dynamics CRM Online, there’s really no excuse for franchisors to not arm ALL of their franchisees with the latest and greatest CRM capabilities. By doing so, you’ll be putting your franchisees in the best possible position to achieve their profitability goals.


* Promotional pricing expires June 30, 2011.

Updated Statement of Direction for Microsoft Dynamics CRM


We just got our hands on the updated Microsoft Dynamics CRM Statement of Direction from Microsoft, and we wanted to share our thoughts on the document. The Statement of Direction (SoD) outlines the future direction of Microsoft Dynamics CRM over the short and longer term. If you remember, Microsoft last updated this document back in April 2010…so this May 2011 SoD release is a pretty big milestone. While the May 2011 SoD is just 7 pages long (short compared to the 20 page April 2010 update), it does contain some goodies.

Here’s our highlights from the document:

  • Microsoft will remain committed to the “power of choice” allowing customers to deploy EITHER cloud-based and on-premise versions of Microsoft Dynamics CRM.
  • SOCIAL CRM! Yes of course social will be part of the future CRM roadmap. Some of the social and collaboration subpoints called out in the SoD include: micro-blogging, business activity feeds, and social intelligence.
  • Microsoft Dynamics CRM will continue to “harness and surface” the collaboration capabilities from Microsoft SharePoint, Microsoft Lync, and Microsoft Office. This is great to see the continued investment in making the Microsoft products work better together. While Microsoft’s Skype acquisition was too late breaking for this document, it wouldn’t surprise me to see some Skype-to-CRM integration in the future.
  • Improved provisioning and evaluation of Microsoft Dynamics Marketplace solutions, nice!
  • Improved reporting: additional charting, visualization and dashboarding capabilities. Who can argue with better, easier and sexier reporting?
  • Cross-device support: while the SoD does not provide details about specific devices CRM will support (iPad and iPhone?!?), it does call out different form factors such as desktop, PC, laptop, tablet and phone.
  • Multi-browser support: Microsoft Dynamics CRM will work on different web browsers by utilizing HTML5 (again no specifics about Chrome, Safari,etc.).
  • Better support for enterprise organizations to run Microsoft Dynamics CRM Online (as part of their IT infrastructure plus better on-boarding, admin and integration capabilities).

Of course this all sounds like GREAT new stuff and we can’t wait to get it! So we’re guessing the next question on your mind is when can customers expect to see new versions of Microsoft Dynamics CRM? The SoD covers future release timing too:

  • Instead of the traditional 3 years between major releases, Microsoft Dynamics CRM will change to a semi-annual release cycle (spring and fall).
  • Microsoft will release an “automatic update” to Microsoft Dynamics CRM Online in Q4 of calendar 2011. There will be a “scheduled update” to Microsoft Dynamics CRM Online in Q2 of calendar 2012.
  • As you would expect, automatic updates happen all at once while scheduled updates allow Microsoft Dynamics CRM Online customers to schedule their update within one year. I think it’s pretty safe to assume that scheduled updates will be larger in scope and complexity compared to automatic updates.
  • Microsoft will release an “update” for the on-premise version of Microsoft Dynamics CRM in Q4 of calendar 2011. An “upgrade” of the on-premise version of Microsoft Dynamics CRM will be available in Q2 of calendar 2012.
  • On-premise upgrades may incorporate significant Microsoft platform innovations (e.g. the next releases of Windows Server and desktop, .NET Framework, SQL Server, Office), while updates offer smaller scale new capabilities.

From a customer and partner perspective, I am really jazzed up about the semi-annual release cycles. It will be outstanding to get new features and capabilities more quickly. However from a book author perspective, I am slightly terrified! :)

Of course, you can download and read the Microsoft Dynamics CRM Statement of Direction from our website. Enjoy.

Topics: Microsoft Dynamics CRM 2011

New Microsoft Dynamics CRM Whitepaper: Improving Client Performance

The Microsoft Engineering for Enterprise (MS CRM E2) team just released a new whitepaper titled "Optimizing and Maintaining the Performance of Microsoft Dynamics CRM Clients”. As you would expect, this document goes through 19 pages of detail on how to soup up your Web client and Outlook client to achieve the best possible performance. In addition to covering the obvious stuff like “make sure you have a fast network” and “don’t skimp on the system hardware” (I’m paraphrasing here :) ), the white paper also gets into some not-so-obvious nitty gritty details. Some of the tips outlined in this document include:

  • Configure client-side browser caching in Internet Explorer
  • Configure simultaneous download sessions in Internet Explorer
  • Optimize the Outlook synchronization process by limiting synchronization filters
  • Optimize the Outlook client Address Book performance by only matching against the contacts that are synchronized to Microsoft Dynamics CRM
  • Pin commonly used views in the Outlook client
  • Limit the use of JScript, IFrames and sub-grids

Personally, the suggestion to configure multiple simultaneous download sessions was a new suggestion for me that I will try. Apparently this is a new feature available in IE 8 and IE 9.

While you shouldn’t expect these tips to make MAJOR improvements in your performance, every little improvement helps! You can download a copy of this whitepaper on the Sonoma Partners website.

Topics: Microsoft Dynamics CRM 2011