Report for CRM online RRS feed

  • Question

  • Hi All,

    I am trying to write a report to find the difference between times of the case logged and the first email having regardingid as the caseid.

    MS CRM Online

    FetchXML SSRS report required.

    I am not sure how this can be achieved as I tried a few fetchxml but don't seem to get anywhere.

    <fetch version="1.0" mapping="logical" distinct="false" aggregate="true">
      <entity name="incident">
        <attribute name="title" groupby="true" alias="Title"/>
        <attribute name="ticketnumber" groupby="true" alias="Ticket_number"/>
        <attribute name="createdon" groupby="true" alias="create"/>
        <attribute name="incidentid" groupby="true" alias="IncidentId" />
        <filter type="and">
          <condition attribute="createdon" operator="on-or-after" value="2013-05-01" />
        <link-entity name="email" from="regardingobjectid" to="incidentid" alias="ab">
    	<attribute name="actualend" alias='date_min' aggregate="min" />

    This is failing on <attribute name="createdon" groupby="true" alias="create"/> line complaining that the dategrouping is invalid. i understand that this can't be done as grouping on date is not possible, but removing the grouping on the created on date is not being liked as well.

    Any other suggestions and approaches that can be taken to achieve this please??

    Many Thanks.

    Tuesday, January 6, 2015 11:11 AM

All replies

  • Hello,

    I would suggest to add "First Email Date Time" field to case and populate it with plugins/workflows. After that you will have possibility to use in your report.

    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Tuesday, January 6, 2015 11:24 AM
  • Hi Andrii,

    This would not cater to my present data which does not have such plugin/workflow in place. Any other suggestion to cater to old data please?


    Tuesday, January 6, 2015 11:33 AM
  • I would suggest to write DTS (for example using Scribe or SSIS Integration Toolkit) to update existing data. After that you can develop a report and it should work fine.

    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Tuesday, January 6, 2015 11:52 AM
  • Hi Andrii,

    This can be a possible solution but the customer doesn't want to spend on doing the DTS or the plugin/workflow. Is it possible at all to write such a report?

    The idea is to get the difference of the time between the first email received and the case being created, so we can guage the response time.


    Wednesday, January 7, 2015 3:56 PM
  • What I would suggest - try to write the same query using SQL and try to convert it to Fetch - http://www.sql2fetchxml.com/

    But from a first look that is not possible with Fetch query. In case you will do it in a fetch way I would love to see how you've done that.

    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Wednesday, January 7, 2015 4:05 PM