CRM Online Reporting with FetchXML (Intro)
We use CRM Online internally in our business and one of the main needs we have as a business is the ability to create a weekly report that shows what we worked out for the last week. We use Outlook calendar appointments that are "set regarding" a project in CRM so we can associate the time spent with the customer project.
I had been using a Report Wizard report to get this information out of the system, but as anyone who's worked with Report Wizard knows, it's not terribly capable. It's easy to use, and if you just need to get data out of the system with a total, then it's great. However, if you really want to have reporting flexibility, you'll need to crack open SQL Reporting Services and use FetchXML to collect the data out of CRM Online.
Since this post gets really long, I'm going to break it into a few different pieces, so I'll put links to each of the subsidiary articles - I encourage you to use this post as a jumping off point for the particular piece of the puzzle you need to solve to get SQL Reporting Services to work for your CRM Online implementation.
Part I: How to Set up a Data Source to Access CRM Data in SQL Reporting Services
Part II: How to Use FetchXML Data in the Report
Part IIa: CRM FetchXML Operators
Part III: Handy SQL Reporting Services Functions for CRM Online Reports
Here is a list of the other blog entries I reviewed while putting together my report this weekend and preparing this blog article:
- How to create custom reports from CRM team blog: http://blogs.msdn.com/b/crm/archive/2010/10/26/how-to-creating-custom-report-with-microsoft-dynamics-crm-2011-bids-fetch-extension.aspx
- FetchXML reports for CRM 2011 Online:
http://garethtuckercrm.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/ - CRM 2011 FetchXML based custom reports:
http://wwwrahulsharmain.wordpress.com/category/sql-server-reporting-services/ - Useful SSRS Functions:
http://msdn.microsoft.com/en-us/library/ms157328(v=SQL.90).aspx - Using Parameters in Reports:
http://msdn.microsoft.com/en-us/library/gg309583.aspx - FetchXML Based Reports - Bits & Pieces:
http://blogs.msdn.com/b/crm/archive/2011/02/23/fetch-xml-based-reports-bits-amp-pieces.aspx
Good luck in your efforts to create CRM Online reports and let us know if we can help.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.