Reporting from Dynamics CRM Online: An Introduction to FetchXML Part 2
Posted by Mike Giuffre on November 14, 2016
In Part 1 of Reporting from Dynamics CRM Online: An Introduction to FetchXML, I explained how important your Advanced Find is for creating your FetchXML SQL Report. To quickly recap, FetchXML is a way to bring data from Dynamics CRM Online into a SQL Report. Hopefully you’re comfortable creating and modifying Advanced Finds now and can get to the data you need for your report.
Install SQL & Dynamics CRM Tools
There are 2 things you’ll need to make sure you have installed first.
- SQL Server Data Tools for Visual Studio - this is the development environment where you create your SQL Reports from.
- Download and install Microsoft Dynamics CRM 2015 Report Authoring Extensions. This will give you the all-important "Microsoft Dynamics CRM Fetch” data connection type that you will need to use in your report.
Once you have all the above installed, and you’ve downloaded the FetchXML from your Dynamics CRM Advanced Find, you are now ready to create your SQL Report.
Create Your Report
- In SQL Server, start by creating a new report.
- Next, go to create a new Data Source.
- Create the required “Embedded” connection, and select “Microsoft Dynamics CRM Fetch” as your type.
- Now you need a connection string. You can get this directly from your browser session connected to CRM Online. It’s simply the first part of the browser address when you are using CRM Online.
- Next, go to the Credentials tab on the Data Source. During development of the report, choose the option to “Use this user name and password” and enter your information.
Once you finish and publish the report to CRM you need to go back and switch this to “Prompt for credentials”. This will force the report to use the CRM login of the user, and their security permissions. This will ensure that the user can only see the data they are allowed to access.
- Click OK to close the Data Source window and save your data source.
- Next up you will create a Dataset to base your report on. Each Dataset corresponds to the FetchXML you extracted from your Dynamics CRM Advanced Find. Open your saved FetchXML query from your CRM Advanced Find and create a new Dataset in your SQL Report.
- Give your Dataset a suitable name, perhaps based on the source for your FetchXML. Select “Use a dataset embedded in my report”. Next choose the Data source you created above. You will be creating a “Text” Query type as below and paste in your FetchXML as the Query.
- You can click either “Refresh Fields” or OK and SSRS will attempt to validate your XML against CRM. This may take a moment but if all is well you should see your new Dataset in your SSRS report.
Now you can create your report using your data.
In my next post, we will take a deeper dive into the resulting columns Dynamics CRM returns. If you have questions email us at Marketing@Crestwood.com. We have expert report writers who can help you with all your reporting needs.