You’re offline. This is a read only version of the page.
I am trying to write a report that lists our CRM users and the number of times (from the Audit entity) that they have connected between 2 dates.
The link from the systemuser entity to the audit entity, works when I make it select the records from the audit entity, but due to the number of records returned, I just want a count of the audit records.
I can't get the syntax correct for the groupby/count clauses, can anyone see where I've gone wrong.
<fetch distinct="false" no-lock="false" mapping="logical" aggregate="true" >
<entity name="systemuser">
<attribute name="fullname" groupby='true' />
<filter type='and'>
<condition attribute='siteid' operator='not-null' />
<condition attribute='siteid' operator='ne' value='' />
<condition attribute='isdisabled' operator='eq' value='0' />
</filter>
<link-entity name='audit' from='objectid' to='systemuserid' alias='Audit' aggregate="true" link-type='outer' >
<attribute name="createdon" aggregate="count" alias="RecC" />
<filter type='and'>
<condition attribute='action' operator='eq' value='64'/>
<condition attribute='createdon' operator='between'>
<value>@StartDate</value>
<value>@EndDate</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>
*This post is locked for comments
Thanks guys for your suggestions, I never did solve this so I will give these a try.
Hi Steve,
Filter condition for date should be like below.
Share
Report
All responses (
Answers (
<filter type="and">
<condition attribute="createdon" operator="on-or-after" value="2017-08-01" />
<condition attribute="createdon" operator="on-or-before" value="2018-03-28" />
</filter>
You can refer below msdn reference to write aggregation and group by.
https://msdn.microsoft.com/en-us/library/gg309565.aspx
Hope this helps.
Hi Steve,
You can try the tools available in XRM toolbox to build the sysntact/ fetchxml query and also see the outcome.
www.xrmtoolbox.com/.../Cinteros.Xrm.FetchXmlBuilder
Hope this helps.
Hi Steve,
Tested below fetchxml works, see highlighted the part I changed.
Also, I'm not sure what kind of date range you are looking at, please beware the record limitation of fetchxml. Read point 9 of this article:
http://www.kingswaysoft.com/blog/2013/06/18/Limitations-with-CRM-FetchXML
<fetch distinct='false' no-lock='false' mapping='logical' aggregate='true' > <entity name='systemuser'> <attribute name='fullname' alias='fullname' groupby='true' /> <filter type='and'> <condition attribute='siteid' operator='not-null' /> <condition attribute='siteid' operator='ne' value='' /> <condition attribute='isdisabled' operator='eq' value='0' /> </filter> <link-entity name='audit' from='objectid' to='systemuserid' alias='Audit' aggregate='true' link-type='outer' > <attribute name='createdon' aggregate='count' alias='RecC' /> <filter type='and'> <condition attribute='action' operator='eq' value='64'/> <condition attribute='createdon' operator='on-or-after' value='2018-01-01'/> <condition attribute='createdon' operator='on-or-before' value='2018-03-01'/> </filter> </link-entity> </entity> </fetch>
Regards,
Abby
Thanks, but I had already tried both the answers in the stackoverflow post, neither work for me.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156