Today’s guest blogger, CRM MVP Frank Lee, describes reporting options available for Microsoft Dynamics CRM Online.
Microsoft Dynamics CRM Online is a fantastic CRM platform for most small medium businesses (SMB) – it is so easy and inexpensive to get started.
However, one area of challenge for Microsoft CRM Online is custom reporting. Some background on this. Unlike Microsoft CRM On-Premise, Microsoft CRM Online does not support uploading of SQL Report Services (SRS) custom reports (.RDL) or accessing the SQL Server Database filtered views. CRM Online does not allow users to directly access the CRM Application Server or Database Server – this is a good thing to maintain security and the 99.9% uptime for all the companies that are running on Microsoft CRM Online. In fact, I’m not aware of any other major On-Demand Business Application vendors allowing their users to directly access the application or database server.
After working on numerous Microsoft CRM Online deployments, here is what I would suggest as a good practice for reporting:
Start with the "Out of the Box" options such as Default Reports, Advanced Find / Export to Excel, Mail Merge, and Report Wizard to meet your CRM Online reporting needs. If none of them are "good enough" then go with custom report development (SDK).
Here are some good references/links to the reporting options mentioned above:
- To view the list of default reports that come with Microsoft CRM Online –
from the CRM Client >> Workplace >> Reports
- Blog: Advanced Find / Export to Excel
- Video: Using Advanced Find – Time: 10:02 minutes
This video will show you how to use the Advanced Find feature to find and take action on targeted sets of data. It will also show how to create Saved Views (My Views).
- Blog: MSCRM 4.0 Mail Merge Basics
- Blog: Microsoft Dynamics CRM Mail Merge Templates
- Blog: Printing Quote for customer
- Video: Mail Merge – Time: 08:03 minutes
This video will show how to create mail merge templates and create emails and Word labels with mail merge.
- Blog: Report on Opportunities Lost to Competitors
- Video: Reporting Overview – Time: 15:29 minutes
This video will show the reporting capabilities in Microsoft Dynamics CRM Online.
Export and Integrate CRM Data to a Local Data Source (Access, SQL Server, SQL Server Express, etc.):
There are two approaches for this. Please note that the two approaches are not supported by Microsoft CRM Support – use at your own risk/support.
- Subscribe to the Microsoft CRM Online Professional Plus edition that supports the Offline Data Synchronization feature. Setup a Microsoft CRM Online Outlook Client and ensure that all the needed CRM Online data are continuously updated to the local database. Then use the Microsoft SQL Server Reporting Service (SRS) report development tool or other reporting, Business Intelligence (BI) tools to build the custom report against the local SQL Server Express CRM database used by the Microsoft CRM Online Professional Plus edition. Note: 4 GB database limit for SQL Server Express
- Utilize an integration tool to extract CRM data to a local data source and then use a corresponding reporting tool. For example, you can purchase and setup the Scribe Software Insight to integrate between Microsoft CRM Online and the local SQL Server database. The local SQL Server database will mimic the needed CRM Online tables so you can use the Microsoft SQL Server Reporting Service (SRS) report development tool or other reporting, Business Intelligence (BI) tools tools to build the custom report against the local data
Custom Development :
With custom development using the Microsoft Dynamics CRM SDK, the reporting options are endless.
Here is a link to a FREE Software utility download – Workopia CRM Online Statistics. It was built by one of our developers using Microsoft CRM SDK that produces a report of the current record count of the following Microsoft CRM Online entities: Account, Contact, Lead, Opportunity and much more. It reports the break down for the different record states, Lead count from different Lead Sources and the count of Opportunity records originated from a Lead record. Enjoy.
Here’s a case study regarding custom report development for Microsoft CRM Online:
One of our Microsoft CRM Online clients in the high tech engineering services industry had a need for several reports, some of those reports are used as "summary printouts" to be given to the customer upon service completion. These reports contain very specific formatting, layout, calculations, graphics, and data content from various Microsoft CRM related areas, more than two levels of data relationships.
We had tried to use the "Out of the Box" options to see if it is "good enough", but none of them were acceptable for this case. The Client loves Microsoft CRM Online but without their critical reports, they will have to either switch to Microsoft CRM On-Premise or to another CRM system.
We decided to build their Microsoft CRM Online reports via custom report development – this worked great! The Client can now click on a report button from either their CRM Online Web Client or Outlook Client to access these custom reports. The Client got the EXACT reports they are looking for and was able to stay with Microsoft CRM Online without switching to another CRM system. The effort to build these custom reports was about the same as if we have to build them via SRS custom report development for an On-Premise environment.
In summary, if you have data in Microsoft CRM Online that you would like to report on, and none of the "Out of the Box" reporting options are good enough, then try custom report development.