We earlier developed reports using MS-SQL Server Reporting Services (SSRS). We define query for the desired output either in SQL Query or Stored Procedure. When we talk about Microsoft CRM 2011 then there are 2 approaches we can follow:
1.SQL Query or Stored Procedure
Now many developers aware of first method but not aware of the latter one. In the latest version of MS CRM - Microsoft CRM 2013 has only Fetch XML Reporting.
We will talk about Fetch XML Reporting in this article.
Fetch XML Reporting is new for the developer who uses SQL Server Reporting Services. Now we take one sample report to display what's new in it and what is way to create Fetch XML Report for MC-CRM 2013.
Step 1 – First open new BIDS Project for reporting.
Step 2 – Add a new report in the project.
Step 3 – Add Datasource. Here, if you notice we have a list of Embedded Connection Type, you have to select Microsoft Dynamics CRM Fetch.
If you didn't found the Fetch XML option, please download from the Microsoft Dynamics CRM 2011 Report Authoring Extension (with SQL Server Data Tools support).
Step 4 – Provide connection string where MS-CRM 2013 has been hosted.
Step 5 – Add credentials to access the CRM.
Now datasource has been connected.
NOTE: There is no “Test Connection” button in this Fetch XML Provider. The error will raise when you want to get data from the Data Source.
If credentials are not correct you will get this error.
Step 6 - Create Fetch XML Query
Next, we have to create Fetch XML Query to get the data. Fetch XML is in the form of XML so if you know the exact names of the Field and Entity then you can write yourself. There is predefined format for writing Fetch XML. We will discuss this in our upcoming blogs.
Another way of getting Fetch XML from the CRM system which is much easier than the previous one. First connect with the online MS-CRM 2013 through browser.
After Logged-in, Click on “Advanced Find” appearing below the menu items.
Let say, we want a simple report from Contact Entity displaying Name, Email, State and Mobile fields. Select “Contacts” Entity in the “Look for” Drop-down list. Then click on “Edit Columns” button.
In Edit Columns window, select the needed columns for Report.
There are few columns which will be coming by default, you can remove them and add required columns.
Step 7- Get Fetch XML Query through “Download Fetch XML” button.
This will ask for saving the XML File. Click on “Save” or “Save As”. This will save the file onto your system at the specified location.
If you open this file in web browser, it will look like below image:
This is a basic query and does not contain any condition, join, etc.
Now we have a valid Fetch XML to create a Report.
Step 8 – Add Dataset to report.
Go to Report project, right click on “Datasets” in “Report Data” window, it will display “Add Dataset...”, click on it.
It will display below dialog window:
In this window, Select dataset type as “Use a dataset embedded in my report”.
Then, Select Datasource. After that Select Query Type “Text”, paste that Fetch XML inside that textbox.
NOTE: When you copy Fetch XML from the web browser then it will copy all the things as “-” displaying in the beginning. Remove it, otherwise it will cause error.
Now, when you check for the fields under the added dataset then it will show something like the below image:
Step 9 – Add Fields to Report
Now, we have all the things to built a report, just we have to add the fields to report to show.
Dataset will return multiple contacts so will use Table Control from the Report Items to display records.
From the dataset Drag-n-drop fields inside the Data Section of Table Control.
Step 10 – Running Report
After some formatting of Table Header and Report Heading, When you click on Preview, the below report will display.
Report has been Created, now you can Upload it to the Online CRM system.