Reports are a way of presenting useful data to the users in an organized format. CRM reporting needs are often solved by using out-of-box CRM reports, Advanced Find, Dashboards and Charts, or the Report Wizard. But when a business demands a complex report involving calculations, grouping, detailed layouts, etc., then custom reports are needed. There are two types of reports in Microsoft Dynamics CRM:
1. Reports using SQL queries (on-premises only)
These reports use SQL queries to securely retrieve data for reports from filtered views defined by the system. The default reports that are shipped with Microsoft Dynamics CRM are SQL-based reports.
2. Reports using FetchXML (Online and on-premises)
These reports are introduced in Microsoft Dynamics CRM and use FetchXML queries to retrieve data for reports. All reports that are created using the Report Wizard in the Microsoft Dynamics CRM are Fetch-based reports. However, to create complex reports (such as drill-through reports or sub reports) or to change the layout and formatting of reports, you need to create a custom Fetch-based report using the Business Intelligence Development Studio.
In this blog, I am going to describe how to create and deploy custom reports using FetchXML.
Step 1: Get FetchXML
FetchXML is a proprietary query language that is used in Microsoft Dynamics CRM Online. It is xml data that conforms to the schema definition of FetchXML. For example, here is a FetchXML statement that retrieves all accounts:
<fetch mapping=‘logical‘>
<entity name=‘account‘>
<attribute name=‘accountid‘/>
<attribute name=‘name‘/>
</entity>
</fetch>
An easy way to obtain this from existing reports and queries in CRM is to go to ‘Advanced Find’ tab and create a query for the report, selecting required columns. Under the debug section, there is a button for ‘Download Fetch XML’ which will save the FetchXML behind that query, locally on your computer.
Step 2: Create a new SSRS report, Data Source and Dataset
Microsoft Dynamics CRM 2015 Report Authoring Extension is required to author custom, Fetch-based reports for Microsoft Dynamics CRM by using Microsoft SQL Server Data Tools Business Intelligence (BI). Download and install it from the Microsoft website.
Open Visual Studio with a supported version of SQL Server Data Tools installed. Start a new project of type ‘Report Project.’ Add a new report, create new Data Source and Dataset. Data Sources and Datasets are functions within the various reporting tools which allow you to connect the report to the database. Data Sources control the connection to the database, and the Dataset controls the actual information retrieved. For FetchXML reports, use an embedded connection. And select type as ‘Microsoft Dynamics CRM Fetch.’
The connection string above is comprised of the CRM online url, and the unique organization name, separated by a semicolon. The unique organization name can be obtained by browsing to the CRM home page and looking for the ‘Developer Resources’ under ‘Settings.’
Next we create a new Dataset and in the query window paste the FetchXML that we saved in step 1.
Step 3: Deploying Report in CRM
Deploying reports in CRM is quite an easy task. Go to Module menu, ‘Tools’ and then ‘Reports.’
Click on ‘New’ button in ‘Reports,’ a popup will open for ‘New Report.’
Select ‘Existing File’ from ‘Report Type’ drop-down, select report and click on ‘Save’; your report will be added to reports in CRM.
FetchXML has a few limitations; for more complex reports ‘SQL based reports’ should be used, if possible. For that, CRM needs to be deployed on-premises rather than online. If that is not possible, then the SQL database tables can be replicated locally from online CRM, and SQL reports can be created off that.
 |
MINAL WAD | Business Solutions DeveloperEver since Minal joined KTL Solutions in March 2008, she has been diligently working as a Business Solutions Developer, providing customized software that integrates with Dynamics GP. She has a passion for using technology to solve real world problems, and it is this passion that enables her to deliver projects with exceptional quality in time and within budget. Her role as a business analyst, is a new found skill, and she has been able to successfully demonstrate it in some of her recent projects. She is proficient in C#, ASP.Net, SQL Server, Sharepoint, WPF, Entity Framework, MVVM and Database Design. Minal has over 8 years of experience as a developer. Prior to KTL, she worked with Microsoft in Fargo, ND, where she was a part of Dynamics Axapta team. She holds a Master’s degree in Computer Science from Oklahoma State University. She involves herself in training and certifications that keeps her abreast with new technologies. |
