How to get the time spend on billable tasks and non-billable task on CRM 2016 for each Individual

Question

I added a field on the Task entity on CRM to keep track of whether the task is billable or not. Now I want to sum the time spend on the billable tasks and non-billable tasks for each individual. How can I achieve this in Microsoft Dynamics CRM 2016?

How do you want your data presented? In a report, on the individual's CRM User form, in a dashboard..? — Dave Clark

Dave Clark Dave Clark · Accepted Answer · 2017-04-18T12:05:04

This can be achieved with an aggregated report using FetchXML as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name = "task">
    <attribute name="actualdurationminutes" alias="durationCount" aggregate="count" />
    <filter type="and">
      <condition attribute="ownerid" value="userID" operator="eq" />
      <condition attribute="isbilled" value="isBilledTask" operator="eq">
    </filter>
  </entity>
</fetch>

Replace userID with the ID of your User.

Replace isBilledTask with 0 for no and 1 for yes.

Notes:

The attribute isbilled is an out-of-the-box field; you may not have needed to create your own custom field for billable/non-billable.
actualdurationminutes is in minutes and is not automatically calculated (it must be manually input by a CRM User).

How to get the time spend on billable tasks and non-billable task on CRM 2016 for each Individual

1 Answers