SQL Server Reporting Services in Dynamics with FetchXML.
In Dynamics SSRS report create using FetchXML (online and on-premise) , SqlQuery(on-premise) and Store Procedure(on-premise) . For creating a report in MS CRM we need some supported tools are required.The following components are required before you start building Fetch XML Reports:
- Install Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012, which you can download from this Link
- Install Microsoft Dynamics CRM 2015 Report Authoring Extension (with SQL Server Data Tools support), which you can download from this Link
Install the both component for SSRS creation. After installation we got the visual studio 2012 with FetchXML data source.Most of people not know about how to start the reporting business intelligence and set up it. I will show it as step by step than mainly show the how to create FetchXML query with XRMToolBox (FetchXMLBuilder) .
Set up the FetchXML Data Source for SSRS
Step 1. Click on New Project then choose New Report Server Project.
Step 2. Need to add New Data Source to report. In Connection String field, fill the your organization URL (Setting -> Customization -> Developer Resources).
Need to add Data Source name as per you.
When report run it required organization credentials.And to avoid again provide or for security need to add this.
Step 3. Now as per requirement design the FetchXML. We can design the query in two way.
a) Advance Find, In dynamics advance find option available in top of ribbon.
Select required entity and apply the condition as per the requirement.Then download the XML.
Click on “Download Fetch XML” then we got the XML file.
Using advance find we are not able to add more condition so need to add manually in tags in downloaded xml.
b) Using FechXML Builder (Link), using this tool we can create query easily.In this tool all option are available for query creation link link-entity, join , aggregation. No need to add manually tag for extra condition in FetchXML.
Step 3. After creating FetchXML, need to add in report and design it.
Give report name and choose the data source . then add created FetchXML query. Now data source and query is set and data will comes as per requirement design the report and build it.After build *.rdl file is gendered in created project (debug folder).
Now need to add in organization, go to report section in CRM and click on new.
Choose the report type (existing file) and fill all the information related to report.
Save and close, now custom report available in report section.
Note:- fetchXML Builder is tool you can download and design query.
Following table describes different types of operators we can use in a ConditionExpression and FetchXML in Dynamics CRM
Enjoy FetchXML Reports!!!