Dynamics CRM 2011 Report Development using FetchXML
October 24, 2011 3 Comments
The inability to have custom reports was one of the biggest drawbacks of Dynamics CRM online when it was first released. Fortunately Microsoft has addressed this issue in the release of Dynamics CRM 2011. However, there is one small catch. The catch is that you have to use FetchXML (which is a proprietary query language that is used in Microsoft Dynamics CRM) for your SRS report query.
In my opinion one of the most powerful features of reporting in Dynamics CRM is the ability to use Advanced Find to “pre-filter” the data sent to the report. As a report writer of a number of years, I was always frustrated by the moving target of parameters that a user might request. When you get more than two or three, the report becomes a little difficult for the end user to use. By taking advantage of the “pre-filtering” with Dynamics CRM and Advanced Find the options of parameters become almost infinite and as a developer I only need to worry about one thing–setting up the option of “pre-filtering” in my report. It may seem like a difficult task but it is actually quite simple. I’ll walk through the steps.
First you have to install the Microsoft Dynamics CRM 2011 Report Authoring Extension, set up a project in the Business Intelligence Development Studio, and start a report. When setting up your Data Source you will set the option as an Embedded Connection and in the Type drop down you will have an option for Microsoft Dynamics CRM Fetch. The connection string will simply be the URL of your CRM instance. If you are using multi-tenancy you can also add the org. I usually just copy the URL right out of the browser. The name of the Data Source and credentials aren’t critical because Dynamics CRM actually overwrites this Data Source with the one stored in the SQL Server Report Server.
Now that we have the Data Source set it is time to build our Data Set. There are a few different tools out there in the community that are available for download . I find it easiest just to use Advanced Find to build my basic query and the use the newly added feature in Dynamics CRM 2011 of downloading the FetchXML.
Once you download the XML and open it with something like Visual Studio, it will look something like this. To take advantage of the “pre-filtering” you need to set the value of the enableprefiltering parameter to “1” which I have highlighted in the screen shot below. You also have the ability to specify the name of the parameter here using the prefilterparametername property. I personally don’t see the point in giving my parameter a name as the application will do it for you if you leave the property out. I will demonstrate this in just a moment.
Once the Dataset Properties are saved you will see that a parameter is created in the Parameters section with the name of @CRM_entityname. If you now go back and look at the Parameters in the Dataset Properties you can see that the parameter was set up automatically.
Go into the Report Parameter Properties of the newly created parameter and change the parameter visibility to “Hidden” or “Internal”. This will prevent the end users from seeing the parameter at runtime.
Once the report development is finished, it will need to be uploaded to CRM. This process is the same for all reports using “pre-filtering” either with SQL data sources or FetchXML data sources. Make the “Related Record Types” the entity that you set “pre-filtering” on. Then you can choose where to display the report. Using the “pre-filtering” allows the report to work in the Forms and Lists as well as the Reports Area. You will also have the ability to set up a “Default Filter” for a report to make it easier for the users but the users will have the ability to edit that filter on the entity with the “pre-filtering” for when the report is run from the Reports Area.
Reports written with the FetchXML data source with “pre-filtering” are supported in both CRM Online and On Premise. However, CRM Online will only support the FetchXML model. After writing several reports with the FetchXML model I am starting to like it. I found it a bit challenging to figure it out at first but once I figured it out I am finding it fairly simple to use.
If you are unfamiliar with the reporting options, the “pre-filtering” works several ways. First, a user has the ability to go to the Reports Area and use Advanced Find to build the query for the report. Second, from a list the user can run the report on a selected record, the current view or all records. Lastly, the user can run the report from a form for just the current record. As you can see with these options “pre-filtering” is a very powerful and useful feature.
Good luck on your next reporting project!
|Learn more about our Denver office||Learn more about Slalom Consulting Customer|
Subscribe to follow new CRM posts