Fetch XML based reports in Dynamics CRM

Fetch XML based SSRS report in Microsoft Dynamics CRM Online/On-premises is an important and interesting topic to learn about. Here, in this post, I am sharing some of my rudimentary understanding, examples for some complex development scenarios , and limitations of Microsoft Dynamics CRM Fetch XML based report.

Rudimentary understanding 

The very basic thing that needs to be kept in mind is that unlike Dynamics CRM on-premises, you cannot write SQL queries against filtered views for Dynamics CRM online, because an access to the SQL database is not permissible here. Hence, the only option to develop SSRS report in CRM online is to write Fetch XML for retrieving the data. These fetch XMLs can be generated using Advanced Find. The Advanced Find helps us to write query to fetch data from existing CRM instance, save the results as a user owned saved view or as an organization owned view, and download the Fetch XML for this custom view. The Advanced Find utilizes the out of the box security structure to fetch the data. This is a good starting point for developers to get Fetch XML for such type of SSRS reports.

The Fetch XML language is an exclusive query language that is used in Microsoft Dynamics CRM and it supports similar query abilities as a SQL query expression. From CRM online, when we run the Fetch Xml based SSRS report, the query is sent to the web server to retrieve data from CRM database. This permits only SSL connections to the web server and protect data over the wire in the case of IFD and CRM Online deployments.

The below Fetch XML query is fetching records for Account entity. This query will return only active Account records with columns as Account Name, Primary Contact, and Home Phone. It will sort the result set based on Account Name and Home Phone fields.

A simple example of Fetch XML query.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >

<entity name="account" >

<attribute name="name" alias="Account_Name" />

<attribute name="primarycontactid" alias="Primary_Contact" />

<attribute name="telephone1" alias="Home_Phone" />

<attribute name="accountid" alias="AccountId" />

<order attribute="name" descending="false" />

<order attribute="telephone1" descending="false" />

<filter type="and" >

<condition attribute="statecode" operator="eq" value="0" />

</filter>

</entity>

</fetch>

How to create Fetch XML based report.

I’m not going to cover this topic in detail here in this post as it is for the developers who know the fundamentals about creating Fetch XML based report. Although, a reader who doesn’t know how to create Fetch Xml based report in SSRS for Dynamics CRM, the below links would be a good starting point for them to begin their journey.

• https://technet.microsoft.com/en-us/library/dn531151.aspx

• http://social.technet.microsoft.com/wiki/contents/articles/10234.microsoft-dynamics-crm-2011-develop-fetch-xml-based-ssrs-reports-in-visual-studio-2008.aspx

• https://blogs.msdn.microsoft.com/crm/2010/10/19/getting-started-with-custom-reports-in-the-cloud/

Some complex development scenarios

Adding Parameters to a Fetch XML Based report:

Adding/Passing Parameters mean passing values to the queries. Passing parameters to fetch xml reports is same as we pass parameters to SQL query. The advantage of passing parameter(s) is that their values will be set at run time, which can then be used by the fetch statements defined in the report. At run time the received value of the parameter is replaced in the fetch xml before it is executed to get the results.

The parameter name has to be started with “@” and it’s value is populated at run time. Let’s take an example and see how this works.

Consider the following fetch xml that shows sum of estimated revenue for opportunities in “ABC” stage for each customer:

 <fetch mapping='logical' count='10' aggregate='true'>
    <entity name='opportunity'>
        <attribute name='estimatedvalue' aggregate='sum' alias='sum_estimatedvalue'/>
        <attribute name='customerid' groupby='true' alias='customerid'/>
        <order alias='sum_estimatedvalue' descending="true" />
        <filter type="and">
            <condition attribute="stepname" operator="eq" value="ABC" />
        </filter>
    </entity>
</fetch>

Now, let’s make the same fetch xml query as Parameterized fetch xml:

1. Define a report parameter, in our case it is a ‘stage’. This parameter will be shown to the user at runtime and user can insert a value to filter the report data.

<ReportParameter Name="stage">
    <DataType>String</DataType>
    <DefaultValue>
        <Values>
            <Value>ABC</Value>
        </Values>
    </DefaultValue>
    <Prompt>Opportunity Stage</Prompt>
</ReportParameter>

2. When we use a parameter @stepname in our fetch xml, the SSRS internally defines it as mentioned below.

<QueryParameter Name="@stepname">
    <Value>=Parameters!stage.Value</Value>
</QueryParameter>

3. Modify the condition node i.e. <condition attribute="stepname" operator="eq" value="ABC" /> in aforementioned fetch xml to use the query parameter, as specified follows:

<condition attribute="stepname" operator="eq" value="@stepname" />

That’s it, the parameterized report is ready to work! When I execute the report, I will be prompted to enter an Opportunity Stage according to which the data will be filtered.

 

Pre-filtering in Fetch based reports

One of the most useful features of Reports in CRM is to allow reports content to be filtered at run time using Advanced Find feature. The Pre-filtering allows users to create context specific report.

E.g. A user who is running the report, should be able to run the report only for the opportunities owned by him. Using report pre-filtering, one can make the report to prompt the user with an Advanced Find control before executing the report. Then whatever filtering criteria is entered by the user, it is taken as the base filtering criteria for the report and any filtering defined by the report are applied on top of the filtered data set.

All you need to do is to specify an enableprefiltering attribute in the entity node of your fetch query. Please know, the prefilterparametername attribute is not a mandatory attribute.

If you are manually modifying a Fetch-based report definition without using the Report Wizard in the Microsoft Dynamics CRM Web application or SQL Server Data Tools to enable pre-filtering for primary and linked entities, make sure that you:

1. Set the value of the enableprefiltering parameter to 1 for the required entity or linked entity, And specify a unique parameter name for the prefilterparametername property. as specfied below

<fetch distinct="false" mapping="logical">

<entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">

2. Create a corresponding query parameter with the same name as specified for the prefilterparametername property. Make sure that the parameter name starts with "CRM_" and specify it as a hidden parameter.

<QueryParameters>

    <QueryParameter Name="CRM_FilteredAccount">

       <Value>=Parameters!CRM_FilteredAccount.Value</Value>

    </QueryParameter>

</QueryParameters>

3. Create a corresponding report parameter with the same name.

<ReportParameters>

   <ReportParameter Name="CRM_FilteredAccount">

      <DataType>String</DataType>

      <Prompt> CRM Filtered Account </Prompt>

   </ReportParameter>

</ReportParameters>

Multiple Datasets and Multivalued Parameter in Fetch based reports

Usually, when we need to use the result of one dataset to another, we would prefer to have multiple datasets in Fetch Xml based reports. Here, the multi-valued result set could be passed to a second dataset as a parameter to filter the record set for the second dataset.

Sometimes, we have a requirement like we have to select some multiple values in report filter criteria and need to display the records based on the selected values, like we have a filter of Cities (here user can select one or more cities for the report parameter) and we need to display the records based on the selected cities. At this time, we use the multivalued parameter.

The concept is that, in the multi-value parameter we set the data source that holds the cities and we use this parameter directly into our report’s main query.

Example: We’ve created a report that displays accounts which match the specified cities.

Dataset 1

<DataSet Name="DSCities">

   <Query>

      <DataSourceName>FetchDataSource</DataSourceName>

         <CommandText>

<fetch >

<entity name="new_city" >

<attribute name="new_cityid" />

<attribute name="new_name" />

</entity>

</fetch>

</CommandText>

</Query>

</DataSet>

Dataset 2

<DataSet Name="DSAccounts">

<Query>

< DataSourceName>FetchDataSource</DataSourceName>

<CommandText>

<fetch>

<entity name="account">

<attribute name="name"/>

<filter>

<condition attribute="new_cityid" operator="in" value="@Cities"/>

</filter>

</entity>

</fetch>

</CommandText>

<QueryParameters>

    <QueryParameter Name="@Cities">

        <Value>=Parameters!Cities.Value</Value>

    </QueryParameter>

</QueryParameters>

</Query>

</DataSet>

Multi-valued parameter

<ReportParameter Name="Cities">

   <MultiValue>true</MultiValue>

   <Hidden>true</Hidden>

   <DefaultValue>

      <DataSetReference>

         <DataSetName>DSCities</DataSetName>

         <ValueField>new_cityid</ValueField>

      </DataSetReference>

   </DefaultValue>

</ReportParameter>

Limitations

1. Fetch XML does not support RIGHT OUTER JOIN and FULL OUTER JOIN .
2. Fetch XML does not support EXISTS condition with sub-query/expression.
3. You cannot compare two fields directly. For instance, you won't be able to find an equivalent query for the following SQL script:

SELECT * FROM account

WHERE telephone1 <> telephone2

4. When you execute a Fetch XML query, the maximum number of records you get back from CRM server is 5,000.
5. No keyword like UNION, the following thing cannot be achieved in Fetch XML.
   Select Name from account Union ALL Select FullName from contact
6. There is no support of a CASE / WHEN structure.
7. You canno t have more than 10 linked entities in a Fetch XML query.
8. Fetch XML based reports cannot use non-CRM online data sources .
9. When you have more than one dataset the pre-filtering will be applied only on the first dataset.

Tip: In fetch xml when you would not like to consider the time part while comparing the date value then prefer to use “ on ” instead of “eq”.

I hope this article would be useful for developing Fetch XML based reports. Please feel free to update your views about this post and any additional points, which could be considered in relevance for this post.