Dynamics CRM 2011 Report Development using FetchXML

Slalom Consultant Jayson Goldinger

Jayson Goldinger is a Slalom Consultant based in Denver, CO, and a member of the Slalom National CRM practice. His work revolves around Microsoft Dynamics CRM including configuration, customization, report development, and integration. He is also recognized as a Scribe MVP.

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.

Dynamics CRM 2011 Report Development using FetchXML

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.

Dynamics CRM 2011 Report Development using 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.

Dynamics CRM 2011 Report Development using FetchXML

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.

Dynamics CRM 2011 Report Development using FetchXML

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.

Dynamics CRM 2011 Report Development using FetchXML

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.

Dynamics CRM 2011 Report Development using FetchXML

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!

Slalom Consulting’s Denver office Slalom Consulting's Customer focus
Learn more about our Denver office Learn more about Slalom Consulting Customer

subscribe by emailSubscribe to follow new CRM posts

About Jayson Goldinger
Jayson Goldinger is a Slalom Consultant based in Denver, CO and a member of the Slalom National CRM Team. His work revolves around Microsoft Dynamics CRM including configuration, customization, report development and integration. He is also recognized as a Scribe MVP.

3 Responses to Dynamics CRM 2011 Report Development using FetchXML

  1. Paul Oh says:

    Jayson great post. I think I can use the same “pre-filter” in my current project. Thanks for your insight as always.

  2. sree_hcl says:

    I am getting the following error while i connect to datasource in business intelligence with online crm
    i have given the connection string and credentials correctly. Please help me on this

    LogonIdentityExWithUI()

    —————————-

    An authentication error occurred while connecting to server “https://cmscameronmckenna.crm4.dynamics.com/XRMServices/2011/Discovery.svc”. Make sure that the connection string and credentials are correct, and try again.

  3. Anca Sarbu says:

    Hello!
    Thank you for your article, it is very useful. For me it works for standard entities perfectly.
    However when I try to use it on a entity that is customized as an activity entity(let s name it DerivedEntityX), the filter is not working:(.
    In the Related Record Types i have put both Activity and then DerivedEntityX, but I receive all the value. Any tips about this?:)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 129 other followers

%d bloggers like this: