260
Hi All,
I have to create a Entity wise User Statistics report using FetchXML and SSRS. I am using Dynamics 365 CRM Online. The Report could be in below format :
|
User |
Activity(Entity) |
Total number of records Created |
|
User 1 |
Entity 1 |
10 |
|
Entity 2 |
7 |
|
|
3 |
||
|
5 |
||
|
11 |
||
|
14 |
||
|
No |
||
|
|
|
|
|
User 2 |
Entity 1 |
6 |
|
Entity 2 |
3 |
|
|
32 |
||
|
1 |
||
|
4 |
||
|
34 |
||
|
Yes |
*This post is locked for comments
Hi David, Thanks for the reply Can i use the first fetchxml with aggregate for all the entities to display the report? Can it be designed in the format that i have mentioned in my question?
You can do this solely in FetchXML using aggregates. Here's an example query to count the created leads and opportunities
Share
Report
All responses (
Answers (
<fetch aggregate='true'>
<entity name='systemuser' >
<attribute name='fullname' alias='createdby' groupby='true' />
<link-entity link-type='outer' name='opportunity' from='createdby' to='systemuserid' >
<attribute name='opportunityid' alias='opportunity_count' aggregate='countcolumn' />
</link-entity>
<link-entity link-type='outer' name='lead' from='createdby' to='systemuserid' >
<attribute name='leadid' alias='lead_count' aggregate='countcolumn' />
</link-entity>
</entity>
</fetch>
This creates a separate field for each entity count, so in the report you'd have one Row Group on the User, then a separate row for each entity. One drawback of this is that, if you add an extra entity to the query, you'll have to add an extra row to the report
Another option could be to make use of activity feeds. If you configure this to create a post each time a record is created, then you could query the post entity, and use the regardingobjectid and createdonbehalfby columns:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="post">
<attribute name="postid" />
<attribute name="createdon" />
<attribute name="createdonbehalfby" />
<attribute name="text" />
<attribute name="regardingobjectid" />
</entity>
</fetch>
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,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156