Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / fetch xml groupby
Microsoft Dynamics CRM (Archived)

fetch xml groupby

(0) ShareShare
ReportReport
Posted on by Mohammed Waseem 382

Is the fetchxml query correct ?

i want results to be grouped on createdon column of audit entity .

when i excecute fetch xml online i get error: An attribute can not be requested when an aggregate operation has been specified and its neither groupby nor aggregate. NodeXml : <attribute name="action" />

<fetch distinct="false" no-lock="false" mapping="logical" aggregate="true">
<entity name="audit" enableprefiltering="1">
<attribute name="createdon" alias="create" groupby="true" dategrouping="day" />
<attribute name="action" />
<order attribute="createdon" descending="false" />
<filter type="and">
<condition attribute="createdon" operator="on-or-after" value="2016-01-01" />
<condition attribute="createdon" operator="on-or-before" value="2016-10-26" />
</filter>
<filter type="and">
<condition attribute='action' operator='eq' value='64'/>
</filter>
<link-entity name='systemuser' from='systemuserid' to='objectid' link-type='outer' alias='SystemUser'>
<attribute name="fullname" />
<attribute name="windowsliveid" />
<attribute name="businessunitid" />
<filter type="and">
<condition attribute="systemuserid" operator="not-null" />
<condition attribute="isdisabled" operator="eq" value="0" />
<condition attribute="fullname" operator="ne" value="Andy Liu" />
<condition attribute="fullname" operator="ne" value="Dnyaneshwar Gajle" />

<condition attribute="fullname" operator="ne" value="Vishal Mokashi" />
</filter>
<link-entity name="businessunit" from="businessunitid" to="businessunitid" link-type="outer" alias="BusinessUnit">
<attribute name="name" />
<filter type="and">
<condition attribute="bis_regionalbuisnessunit" operator="eq" uiname="BIS MIDDLE EAST &amp; TURKEY" uitype="team" value="{2CB74967-C9D2-E411-80E7-C4346BADB5C4}" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>

ThankYou,

Waseem.

*This post is locked for comments

  • Anwesh Reddy Profile Picture
    Anwesh Reddy 10 on at
    RE: fetch xml groupby

    Use this for order by clause

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

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: fetch xml groupby

    Hi Mohammed, you can't have ordering in aggregate queries. You will need to remove the line below from the query...

    <order attribute="createdon" descending="false" />

  • Mohammed Waseem Profile Picture
    Mohammed Waseem 382 on at
    RE: fetch xml groupby

    Hi Evello\Scott ,

    when i execute the given query given by you  i get below error

    An attribute cannot be specified for an order clause for an aggregate Query. Use an alias.

    By the way i will clarify my requirement  . if the action no 64 i.e user access via web gives  results like mentioned in below table below . i want the first value of every date *(highlighted)the user has logged in  . these results am showing in ssrs report . and the above query am using will be as data set .

                       

    user-details.png

    Thank You,

    Waseem

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: fetch xml groupby

    Hi Mohammed, this is similar to SQl, where you must have aggregates on all columns being returned for group by's. To resolve the error, you must have groupby on all the attributes in the query.. see the Group By section here  https://msdn.microsoft.com/en-us/library/gg309565.aspx 

    Also, a trick for getting the createdon date is to have dategrouping for day, month and year, and then to do a max aggregate on the createdon field.

    You FetchXml should be something like this..

    <fetch distinct="false" no-lock="false" mapping="logical" aggregate="true">
    <entity name="audit" enableprefiltering="1">
    <attribute name="createdon" alias="createdonday" groupby="true" dategrouping="day" />
    <attribute name="createdon" alias="createdonmonth" groupby="true" dategrouping="month" />
    <attribute name="createdon" alias="createdonyear" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="createdon" aggregate="max" />
    <attribute name="action" groupby="true" alias="action" />
    <order attribute="createdon" descending="false" />
    <filter type="and">
    <condition attribute="createdon" operator="on-or-after" value="2016-01-01" />
    <condition attribute="createdon" operator="on-or-before" value="2016-10-26" />
    </filter>
    <filter type="and">
    <condition attribute='action' operator='eq' value='64'/>
    </filter>
    <link-entity name='systemuser' from='systemuserid' to='objectid' link-type='outer' alias='SystemUser'>
    <attribute name="fullname" groupby="true" alias="fullname" />
    <attribute name="windowsliveid" groupby="true" alias="liveid" />
    <attribute name="businessunitid" groupby="true" alias="buid" />
    <filter type="and">
    <condition attribute="systemuserid" operator="not-null" />
    <condition attribute="isdisabled" operator="eq" value="0" />
    <condition attribute="fullname" operator="ne" value="Andy Liu" />
    <condition attribute="fullname" operator="ne" value="Dnyaneshwar Gajle" />
    <condition attribute="fullname" operator="ne" value="Vishal Mokashi" />
    </filter>
    <link-entity name="businessunit" from="businessunitid" to="businessunitid" link-type="outer" alias="BusinessUnit">
    <attribute name="name" groupby="true" alias="businessunit" />
    <filter type="and">
    <condition attribute="bis_regionalbuisnessunit" operator="eq" uiname="BIS MIDDLE EAST &amp; TURKEY" uitype="team" value="{2CB74967-C9D2-E411-80E7-C4346BADB5C4}" />
    </filter>
    </link-entity>
    </link-entity>
    </entity>
    </fetch>

  • Suggested answer
    ScottDurow Profile Picture
    ScottDurow 19 on at
    RE: fetch xml groupby

    Hi

    This is because when you group by you can only include an attribute that you are not grouping-by as an aggregate ( E.g sum/min/max/count)

    If you are trying to find the number of actions grouped by creates on you will need to add  aggregate='count' to that attribute. See msdn.microsoft.com/.../gg309565.aspx

    Hope this helps

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans