Count Aggregate in FetchXML report

Question

Trying to create a FetchXML report for CRM 2016 using VS2012 following instructions here: Create a new report using SQL Server Data Tools. I want to simply find a count of cases created between two dates.

The instructions talk about copy/pasting downloaded FetchXML from the CRM Advanced find process.

FetchXML generated by the Advanced Find process to list all Cases (by ticketnumber):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="incidentid" />
    <attribute name="ticketnumber" />
    <order attribute="ticketnumber" descending="false" />
    <filter type="and">
      <filter type="and">
        <condition attribute="createdon" operator="on-or-after" value="2015-07-01" />
        <condition attribute="createdon" operator="on-or-before" value="2016-06-30" />
      </filter>
    </filter>
  </entity>
</fetch>

I can't find a way to aggregate with Advanced Find, but instructions here: Use FetchXML aggregation seem to indicate a couple of attribute changes to the XML is all that is necessary.

So, I changed my FetchXML in notepad++ to:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
      <entity name="incident">
        <attribute name="incidentid" />
        <attribute name="ticketnumber" aggregate="count" alias="casecount" />
        <order attribute="ticketnumber" descending="false" />
        <filter type="and">
          <filter type="and">
            <condition attribute="createdon" operator="on-or-after" value="2015-07-01" />
            <condition attribute="createdon" operator="on-or-before" value="2016-06-30" />
          </filter>
        </filter>
      </entity>
    </fetch>

This results in an error:

Either I'm doing something wrong, or that FetchXML Aggregation page is incorrect.

Can someone show me how to create a fetchXML report that counts cases opened between two dates?

Henrik H · Accepted Answer · 2016-09-07 08:48:28Z

1

You cannot select an attribute (incidentid) while at the same time doing aggregation.

Additionally, applying ordering does not make sense when you are aggregating.

I have removed those two lines, after which the FetchXML is able to run:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
  <entity name="incident">
    <attribute name="ticketnumber" aggregate="count" alias="casecount" />
    <filter type="and">
      <filter type="and">
        <condition attribute="createdon" operator="on-or-after" value="2015-07-01" />
        <condition attribute="createdon" operator="on-or-before" value="2016-06-30" />
      </filter>
    </filter>
  </entity>
</fetch>

As a side-note, you might want to use the FetchXml Tester in XrmToolBox for quickly being able to edit and execute FetchXML.

answered Sep 7, 2016 at 8:48

Henrik H

5,8121 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mcalex Over a year ago

Thank you. This did the job, once I recreated the report in VS. I'm not sure how the incidentid was selected, I just used the advanced find and it gave me those fields.

Anwesh Thati · Accepted Answer · 2022-09-29 16:38:48Z

0

Use this for order by clause

<order alias="casecount" descending="true" />

answered Sep 29, 2022 at 16:38

Anwesh Thati

1

Collectives™ on Stack Overflow

Count Aggregate in FetchXML report

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related