Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

Dynamic Activity Reporting using FetchXML

Dynamic Activity Reporting using FetchXML

Rate This
  • Comments 5

In a previous post I wrote about some of the differences and limitations around custom reporting in Microsoft Dynamics CRM, specifically of using FetchXML vs. the SQL Filtered Views. Because CRM Online is limited to the use of FetchXML, I have seen many of our online customers run into issues with activity reporting. There are out of the box reports available but do not allow flexibility when it comes to customizing them to fit the specific customer’s needs. Because of this limitation when one of my customers had the requirement to be able to run a report out of CRM that returned all activities that were “Set Regarding” an Account or their related Opportunities and/or Contacts it required a deeper dive into the capabilities of activity reporting using FetchXML. As I was developing this report I wasn’t able to find a lot of comparisons so I thought I would share what I did.

Through a few different iterations we were able to develop a custom activity report, using FetchXML that allows them to run an activity summary for selected Account or that returns activities that are set regarding either an account or that related accounts contacts or opportunities (@Accountid) and also filters based on these other parameters:

Parameter

Field

@Accountid

Regardingobjectid

@EventDateFrom

Scheduledend

@EventDateTo

Scheduledend

@ActivityType

Activitytypecode

@Status

Statecode

 

By using the link-type='outer' I was able to return the data I needed including custom columns from the phonecall and appointment entity. Note: performance should be considered when utilizing the outer link-type, for this particular customer they were okay with the fact that performance of the report may be impacted because their business need for this information is so high, they also put heavy restriction on who can run the report and the time of day that the report is run.

The query I used is:

<fetch version="1.0" output-format="xml-platform" mapping="logical"
       distinct="false">
  <entity name="activitypointer">
    <attribute name="subject" />
    <attribute name="ownerid" />
    <attribute name="regardingobjectid" />
    <attribute name="activitytypecode" />
    <attribute name="statecode" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="activityid" />
    <attribute name="description" />
    <attribute name="createdon" />
    <attribute name="createdby" />
    <attribute name="actualstart" />
    <attribute name="actualend" />
    <order attribute="modifiedon" descending="false" />
    <filter type="and">
      <condition attribute="regardingobjectid" operator="not-null" />
      <condition attribute="isregularactivity" operator="eq" value="1" />
      <condition attribute="statecode" operator="in" value="@Status" />
      <condition attribute="activitytypecode" operator="in" 
                 value="@ActivityType" />
      <filter type="and">
        <condition attribute="scheduledend" operator="on-or-after"
                   value="@EventDateFrom"/>
        <condition attribute="scheduledend" operator="on-or-before"
                   value="@EventDateTo"/>
      </filter>
    </filter>
    <link-entity name="opportunity" from="opportunityid" to="regardingobjectid"
                 alias="opp" link-type="outer">
      <attribute name="opportunityid" />
      <attribute name="customerid" />
      <link-entity name="account" from="accountid" to="customerid"
                   alias="opportunityaccount" link-type="outer" >
        <attribute name="accountid" />
        <attribute name="territoryid" />
        <filter type="and">
          <condition attribute="accountid" operator="in" value="@account" />
        </filter>
      </link-entity>
      <filter type="and">
        <condition attribute="accountid" operator="in" value="@account" />
      </filter>
    </link-entity>
    <link-entity name="contact" from="contactid" to="regardingobjectid"
                 alias="cont" link-type="outer">
      <attribute name="contactid" />
      <attribute name="parentcustomerid" />
      <link-entity name="account" from="accountid" to="parentcustomerid"
                   alias="contactaccount" link-type="outer" >
        <attribute name="accountid" />
        <attribute name="territoryid" />
        <filter type="and">
          <condition attribute="accountid" operator="in" value="@account" />
        </filter>
      </link-entity>
      <filter type="and">
        <condition attribute="accountid" operator="in" value="@account" />
      </filter>
    </link-entity>
    <link-entity name="account" from="accountid" to="regardingobjectid"
                 alias="acct" link-type="outer">
      <attribute name="accountid" />
      <attribute name="territoryid" />
      <attribute name="name" />
      <filter type="and">
        <condition attribute="accountid" operator="in" value="@account" />
      </filter>
    </link-entity>
    <link-entity name="appointment" from="activityid" to="activityid"
                 alias="appt" link-type="outer">
      <attribute name="activityid" />
      <attribute name="new_appointmenttype" />
      <attribute name="ownerid" />
      <filter type="and">
        <condition attribute="regardingobjectid" operator="not-null" />
        <filter type="and">
          <condition attribute="scheduledend" operator="on-or-after"
                     value="@EventDateFrom"/>
          <condition attribute="scheduledend" operator="on-or-before"
                     value="@EventDateTo"/>
        </filter>
      </filter>
    </link-entity>
    <link-entity name="phonecall" from="activityid" to="activityid"
                 alias="phone" link-type="outer">
      <attribute name="activityid" />
      <attribute name="new_phonetype" />
      <attribute name="ownerid" />
      <filter type="and">
        <condition attribute="regardingobjectid" operator="not-null" />
        <filter type="and">
          <condition attribute="scheduledend" operator="on-or-after"
                     value="@EventDateFrom"/>
          <condition attribute="scheduledend" operator="on-or-before"
                     value="@EventDateTo"/>
        </filter>
      </filter>
    </link-entity>
  </entity>
</fetch>
I was then able to use a table in SQL Server data tools (A.K.A. BIDS) report builder to group and format the data in the way the customer had requested.

 

 

Hope this was helpful! For additional information on FetchXML performance, specifically using the isquickfindfields function see my fellow PFE Sean McNellis’s blog post here: Take advantage of better query performance.

If you are interested, our PFE team is ready to help you with this, we can assist with query examples and various other engagements to help you improve your CRM reports and performance. In addition, we have many other services we offer such as reporting workshops, developer training, admin workshops, and code reviews.  If you would like to have another Microsoft PFE or I visit your company and assist with the ideas presented on our blog, contact your Microsoft Premier Technical Account Manager (TAM) for booking information.  For more information about becoming a Microsoft Premier Customer email PremSale@microsoft.com.

Thanks!

Sarah Champ

Microsoft Premier Field Engineer

  • Thanks for this helpful post, Sarah. With Microsoft's increasing push towards the cloud, I have a question regarding the future of reporting in CRM Online: will FetchXML be extended to have greater feature parity with SQL reporting or could the database backend be re-architected to allow some sort of sandboxed but flexible and user-generated SQL reporting to be offered in future in a way that would not pose security and performance risks the avoidance of which are the apparent rationale for the reporting limitations in CRM Online? The face that only FetchXML is available and SQL reporting is not allowed are a big deal and not at all obvious, in some cases until it's effectively too late (we got rid of our on-premises CRM and moved to cloud without being made aware of this severe shortcoming). We've been very frustrated that, e.g. we can't easily create views or reports that list all the accounts that don't have associated activities within the last given period of time (the built-in Neglected Accounts report is close, but not exactly what we'd like, and it cannot be customised or duplicated). Do you know how MS have plans to address this unsatisfactory situation for future releases of CRM Online?

  • Correction of typo: ^face^fact

  • @theJoeCarroll, I provided a little bit of info on my Orion/2013 post (http://bit.ly/15ghsB7), but as luck would have it Sarah has had a TON of experience creating very detailed in depth reports using fetch by blending multiple data sets together and using report processing to filter along with other techniques some of which she's highlighted here. Regarding the future direction, make sure to go vote on the Fetch feedback at connect.microsoft.com (link provided in the comments of the other post) this feedback goes directly to the product group and while they've certainly heard this feedback it can never hurt to vote on the topics out there and provide even more context - every little bit helps.  As CRM 2013 progresses and we find out more information we'll be sure to update our blog with new features & functionality.  Thanks again for reading and taking the time to comment!

    Sean

  • Thanks for this information. Maybe we can hire you to help us? We recently moved to CRM Online 2013 and have noticed we cannot figure out a way to get a good activity report. For example, we are trying to run a report on activities and the users that are doing those activities. We thought we could use "modified by" but we noticed that if UserA takes ownership of an Account that UserB owned, UserA is now the owner all all the activities(which is fine) but they are now the person that modified all activities under that account. Maybe it has something to do with cascading settings? Still looking into it. Its simple, I just want a report of who is actually doing the activities, like emailing, and phones calls, etc...  

  • @Pablo thanks for the feedback! I would look into the “Cascade Active” option on the activity relationship settings, it may help if it only reassigns active activities.

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post