The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Fetch-Xml based Reports: Bits & Pieces

Fetch-Xml based Reports: Bits & Pieces

  • Comments 11

With Fetch-xml based reports in Microsoft Dynamics CRM 2011, users can now create custom reports in Microsoft CRM Online. The CRM Report Authoring extension for BIDS provides a rich authoring experience for creating such custom reports. Abhijit Gore has a great introduction post on authoring and updating fetch based report using BIDS. In this post, I will detail other features of fetch-xml based reports like Adding Parameters, Pre-filtering that will help you create better and effective reports.

1. Adding Parameters to a Fetch Based report:

Just like parameters in SQL queries, one can have parameters in Fetch-xml queries too. The advantage of having parameters is that their values can be set at runtime by the end user.
For example, you want to show all opportunities with Estimated Revenue greater than some value specified by the user. If you were creating the Report using BIDS with the CRM Fetch extension, you would write the following fetch-xml in the Query-Builder:

clip_image002

Parameter names need to start with “@” and their value is populated at runtime. The above query returns opportunities with EstimatedValue greater than the value of the parameter @MinEstimatedValue. Adding the parameter in the fetch query also adds the following QueryParameter and ReportParameter nodes in the RDL.

<QueryParameters>
  <QueryParameter Name="@MinEstimatedRevenue">
    <Value>=Parameters!MinEstimatedRevenue.Value</Value>
  </QueryParameter>
ryParameters>
 
rtParameters>
     <ReportParameter Name="MinEstimatedRevenue">
    <DataType>String</DataType>
    <Prompt>Min Estimated Revenue</Prompt>
  </ReportParameter>
ortParameters>

At runtime, the text from the “Prompt” node in the ReportParameter is displayed to the end user. The parameter value entered by the user is then used to populate the QueryParameter.

clip_image004

2. Pre-filtering in Fetch based reports:

One of the most useful features of Reports in CRM was to allow reports’ data to be filtered using Advanced Find functionality. Pre-filtering allows users to create context sensitive report - this post from Dana explains how pre-filters can be used for SQL based reports. But how do you add pre-filters to a fetch query? Well, it is fairly easy. All you need to do is specify an “enableprefiltering” attribute in the entity node of your fetch query. (The “prefilterparametername” attribute is optional):

 

<fetch>
      <entity name="opportunity" enableprefiltering="true" prefilterparametername="OpportunityFilter">
        <attribute name="name" />
        <attribute name="estimatedvalue" />
        <link-entity name="account" from="accountid" to="customerid" enableprefiltering="true" prefilterparametername="AccountFilter" >
        <attribute name="name" alias="accountname" />
        </link-entity>
      </entity>
</fetch>

As with parameters before, adding a pre-filter parameter in the fetch-query also requires adding a QueryParameter and ReportParameter node in the RDL.

<QueryParameters>
  <QueryParameter Name="OpportunityFilter">
    <Value>=Parameters!OpportunityFilter.Value</Value>
  </QueryParameter>
  <QueryParameter Name="AccountFilter">
    <Value>=Parameters!AccountFilter.Value</Value>
  </QueryParameter>
</QueryParameters>
 
<ReportParameters>
  <ReportParameter Name="OpportunityFilter">
    <DataType>String</DataType>
    <Prompt>Opportunity Filter</Prompt>
  </ReportParameter>
  <ReportParameter Name="AccountFilter">
    <DataType>String</DataType>
    <Prompt>Account Filter</Prompt>
  </ReportParameter>
rtParameters>

Once the report is uploaded into CRM and Run an Advanced Find dialog opens up. The pre-filter parameters are populated with fetch-xmls generated from this Advanced Find dialog.

(In BIDS, the pre-filter parameter will be visible as any another string value parameter. The expected input is a fetch-xml with filter conditions. Leaving the parameter value blank when prompted defaults it to a redundant filter - a fetch-xml that returns all attributes for all entity records).

image

3. Union Queries in Fetch based reports

The UNION operator in SQL allows you to combine two or more select statements and return the result as part of a single DataSet. The current fetch-xml schema does not have an operator that allows a similar UNION functionality. Instead, the workaround is to retrieve the results as part of different data sets.
Suppose you want names of all accounts and contacts in you organization. The SQL Query in your RDL would be:

<DataSet Name="DataSet1">
  <Query>
          <DataSourceName>SqlDataSource</DataSourceName>
          <CommandText> select fullname as name from FilteredContact UNION select name from FilteredAccount </CommandText>
        </Query>
</DataSet>
In a fetch based report, you would instead have two data sets one corresponding to each of the above select statements but using the same data source.
<DataSet Name="DataSet2">
 
    <Query>
      <DataSourceName>FetchDataSource</DataSourceName>
      <CommandText>&lt;fetch&gt; &lt;entity name="contact"&gt; &lt;attribute name="fullname" alias="name" /&gt; &lt;/entity&gt; &lt;/fetch&gt;</CommandText>
   </Query>
     </DataSet>
  <DataSet Name="DataSet3">
 
    <Query>
      <DataSourceName>FetchDataSource</DataSourceName>
      <CommandText>&lt;fetch&gt; &lt;entity name="account"&gt;  &lt;attribute name="name"/&gt; &lt;/entity&gt; &lt;/fetch&gt;</CommandText>
    </Query>
ataSet>

4. Multiple Datasets and Multivalued Parameter in Fetch based reports

Reports with multiple datasets would typically require utilizing results of one dataset in another dataset’s query. Let’s take the following example:
We want to generate a report that displays the top-15 accounts sorted by revenue and a 16th row that displays the total revenue for the rest of the accounts. We would again require two datasets – Dataset1 retrieves the top 15 records ordered by revenue while Dataset2 retrieves the TotalRevenue aggregating over all accounts except the ones from DataSet1.

<DataSet Name="DataSet1">
        <Query>
          <DataSourceName>FetchDataSource</DataSourceName>
          <CommandText>
&lt;fetch count="15"&gt;
              &lt;entity name="account" &gt;
                &lt;attribute name="accountid" /&gt;
                &lt;attribute name="name" /&gt;
                &lt;attribute name="revenue" /&gt;
                &lt;order attribute="revenue" descending="true" /&gt;
              &lt;/entity&gt;
        &lt;/fetch&gt;
    </CommandText>
  </Query>
</DataSet>
 
      <DataSet Name="DataSet2">
        <Query>
          <DataSourceName>FetchDataSource</DataSourceName>
          <CommandText>
&lt;fetch aggregate="true"&gt;
              &lt;entity name="account"&gt;
                &lt;attribute name="revenue" aggregate="sum" alias="TotalRevenue" /&gt;
                &lt;filter&gt;
                &lt;condition attribute="accountid" operator="not-in" value="@TopAccountIds"/&gt;
                &lt;/filter&gt;
              &lt;/entity&gt;
            &lt;/fetch&gt;
    </CommandText>
    <QueryParameters>
      <QueryParameter Name="@TopAccountIds">
        <Value>=Parameters!TopAccountIds.Value</Value>
      </QueryParameter>
    </QueryParameters>
  </Query>
</DataSet>
 
      <ReportParameter Name="TopAccountIds">
      <MultiValue>true</MultiValue>
      <Hidden>true</Hidden>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>DataSet1</DataSetName>
          <ValueField>accountid</ValueField>
        </DataSetReference>
      </DefaultValue>
      </ReportParameter>

The Top 15 AccountIds retrieved from Dataset1 are taken in a multi-valued parameter called “TopAccountIds”. In the fetch-xml query in Dataset2, these parameter values are used as a filter to exclude accounts selected in Dataset1.

(Please note that the above multivalued parameter usage with fetch-xml will only work with the Microsoft Dynamics CRM 2011 RTM or later bits)

I hope that the above discussion helps you in authoring reports with parameters and pre-filters. In case you are experiencing issues with Microsoft Dynamics CRM 2011 Beta, please leverage the CRM Dev Forums.

Please let us know, via comments below, what topics you would like to see about CRM-Reporting in future blogs.

Cheers,

-Ankit Malpani



  • I just went through all of this over the past week! I'm glad you posted this for everyone else who ends up struggling with reports.

    How about a post on how to do dynamic reports in CRM? For example, you have 100 reports (1 for each entity(which has multiple relationships)), and you only want 1 report for all 100 (same layout and everything). Which the user can run from within a report (Run -> Report)

    I've looked into it, and it doesn't seem possible (out of the box anyways). Let me know!

  • "you must set the value of the enableprefiltering parameter to “1”, and specify a parameter name in the prefilterparametername property."

    - CRM 2011 SDK (msdn.microsoft.com/.../gg328288.aspx).

  • Great post.  Encouraging to see what you can with fetch xml reports.  My concerns have reduced some.

  • Could use please post on how to customize the OOTB charts to account for better querying logic?

  • I don't understand the union workaround.  Now you've got a contact dataset and an account dataset, but the report controls can only access one dataset at a time.  How do you combine these into a single dataset that can be used by a report control?

  • I don't understand the union workaround either.

  • If you are looking for better xml reporting software, please take a look at Windward's Xml Reports. www.windwardreports.com/xml-reports.htm With Windward you design reports in Microsoft Word, Excel, or PowerPoint so report design is a lot faster and easier - and non-programmers can design reports.

  • Hi there. I understand the concept of the union workaround, though when I try to set this up I receive a message "Invalid FetchXML". I am using 2008 R2 BIDS. I am able to do simple fetch statements against the online company, though this does not seem to work. Is this still supported, or did something change?

  • I am trying to create a report using CRM 2011 BIDS Fetch Extension. In my BIDS Project, I am able to set my datasource type to "Microsoft Dynamics CRM Fetch" and set the XML for the Dataset Query. This pulls data successfully. I have placed a table on the report that displays company name, etc as I navigate through the pages. That is all good.

    Now, I want to add another table to the report and bind that to a different dataset, but what I am not sure is how this dataset, or second table, knows what group is being displayed as each group is navigated? Do I specify something in the query (i.e. XML)?

    So in other words, when I navigate through the groups, I want the other tables/datasets on the page to refresh dynamically per whatever group I am viewing.

    Sorry for the ignorance....a little new to working with this.

  • Thanks for your information and it offers a lot to our development.  However, we would also like to know more about the UNION workaround.  While we can add 2 datasets into the same data source, we are still not sure how the result could be displayed in the report.  Can you kinldy advise more on this?

  • Can I filter items by time against one column eg Modified Date Time? I would like to get items modifited in the last 5 mintues, for example?

Page 1 of 1 (11 items)
Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post