Microsoft Dynamics CRM (Archived)

fetch xml groupby

(0) Share

Report

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" />

ThankYou,

Waseem.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

ScottDurow 21 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

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 & TURKEY" uitype="team" value="{2CB74967-C9D2-E411-80E7-C4346BADB5C4}" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>

Was this reply helpful? Yes No
Mohammed Waseem 382 on at

Like (0)

Report

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 .

Thank You,

Waseem

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

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" />

Was this reply helpful? Yes No
Anwesh Reddy 10 on at

Like (0)

Report

RE: fetch xml groupby

Use this for order by clause

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

Was this reply helpful? Yes No