You’re offline. This is a read only version of the page.
13,726
I have an issue with an SSRS Report on CRM Online. Required Attendees is a custom lookup type that handles multiple people/accounts/etc. Well, it appears FetchXML can't handle the ActivityParty lookup, and the report I want barfs when we add appointment Attendees to it.
I've found many SQL-based solutions, but obviously none of those will work for CRM Online. So does anyone know how to handle reporting against the ActivityParty for CRM Online? Specifically, this is on D365 version 8.2, so it's not Version 9.
*This post is locked for comments
That link would have been very helpful! Facing similar when creating reports here also.
I didn't save the link unfortunately.
OK thanks Cindy. Would you able to post the link here if you found a solution? I would certainly be interested, and I am sure anyone else would that might run across this thread.
Hi Megan, Unfortunately no it didn't. I ended up finding a solution from a blog online that worked.
Thanks for the help
Hi Cindy, did my last query work for you? If so, can you mark answer as verified, or ask Wayne to (as the original poster)?
OK, let's try again! :-) I was able to get data back for the required attendees. Only way I can see to do it is where it will return one row for each required attendee from an appointment. This will hopefully give you what you need to then structure your report as you see fit. You can put a group on the activity ID and then add the required attendee fields in to the Details row, and put your other fields in the group header. Disclaimer, I got it working without the filters (which I have put in bold in the query). Try it first without that, then if you get records back, add in your filters.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="appointment">
<attribute name="subject"/>
<attribute name="statecode"/>
<attribute name="scheduledstart"/>
<attribute name="scheduledend"/>
<attribute name="createdby"/>
<attribute name="regardingobjectid"/>
<attribute name="instancetypecode"/>
<attribute name="activityid"/>
<attribute name="description"/>
<attribute name="createdonbehalfby"/>
<attribute name="actualstart"/>
<attribute name="actualend"/>
<order attribute="subject" descending="false"/>
<filter type="and">
<condition attribute="scheduledstart" operator="on-or-after" />
<condition attribute="scheduledstart" operator="on-or-before" />
<condition attribute="statecode" operator="eq" value="1" />
<condition attribute="ownerid" operator="eq" />
</filter>
<link-entity name="activityparty" to="activityid" from="activityid" link-type="outer" alias="activityparty1">
<attribute name="partyid" alias="activityparty1_partyid" />
<attribute name="activitypartyid" />
<attribute name="partyidname" />
<filter type="and">
<condition attribute="participationtypemask" operator="eq" value="5" />
</filter>
</link-entity>
</entity>
</fetch>
ok I got the query to work but I still do not have the information I need. We are looking to add the field requiredattendees from the appointments.
Hi Cindy,
Try this. The first entity needs to be activitypointer like you see below. I tried to put in all of the same fields you have, but looks like you might have some custom fields. So, start with the query below in a report, and see if that works for you. Then you can add in the additional attributes as needed.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="activitypointer">
<attribute name="subject" />
<attribute name="statecode" />
<attribute name="activityid" />
<attribute name="instancetypecode" />
<attribute name="community" />
<attribute name="scheduledstart" />
<attribute name="scheduleddurationminutes" />
<attribute name="regardingobjectid" />
<attribute name="description" />
<attribute name="createdon" />
<attribute name="createdby" />
<attribute name="actualstart" />
<attribute name="actualend" />
<attribute name="activitytypecode" />
<order attribute="subject" descending="false" />
<link-entity name="activityparty" from="activityid" to="activityid" link-type="inner" alias="aa">
<link-entity name="appointment" from="activityid" to="activityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="scheduledstart" operator="on-or-after" value="@StartDate" />
<condition attribute="scheduledstart" operator="on-or-before" value="@EndDate" />
<condition attribute="statecode" operator="eq" value="1" />
<condition attribute="ownerid" operator="eq-userid" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>
Here is the query we are using...I am still getting the error message. I am not a fetch xml expert. Can you help me modify the query?
ion="1.0"?>
<fetch distinct="false" version="1.0" output-format="xml-platform" mapping="logical">
<entity name="appointment">
<attribute name="subject"/>
<attribute name="statecode"/>
<attribute name="scheduledstart"/>
<attribute name="scheduledend"/>
<attribute name="createdby"/>
<attribute name="regardingobjectid"/>
<attribute name="instancetypecode"/>
<attribute name="sqm_appointmentobjective"/>
<attribute name="sqm_appointmentcategory"/>
<attribute name="activityid"/>
<attribute name="description"/>
<attribute name="createdonbehalfby"/>
<attribute name="actualstart"/>
<attribute name="actualend"/>
<order attribute="subject" descending="false"/>
<link-entity name="activityparty" from="activityid" to="activityid" link-type="inner" alias="ag">
<filter type="and">
<condition attribute="participationtypemask" operator="eq" value="5" />
</filter>
<link-entity name="appointment" from="activityid" to="activityid" link-type="inner" alias="ah" />
<attribute name ="requiredattendees" />
</link-entity>
<filter type="and">
<condition attribute="scheduledend" operator="on-or-after" value="@StartDate"/>
<condition attribute="scheduledend" operator="on-or-before" value="@EndDate"/>
<condition attribute="statecode" operator="eq" value="1"/>
<condition attribute="ownerid" operator="eq-userid"/>
</filter>
</entity>
Hi Cindy,
Try this as a starting point. Start from Activities rather than Appointments and link in to the Appointments that way. I don't get the error you are referring to (although I have seen it before). You can then add in the attributes you need:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="activitypointer">
<attribute name="activitytypecode" />
<attribute name="subject" />
<attribute name="statecode" />
<attribute name="prioritycode" />
<attribute name="modifiedon" />
<attribute name="activityid" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="modifiedon" descending="false" />
<link-entity name="activityparty" from="activityid" to="activityid" link-type="inner" alias="ag">
<filter type="and">
<condition attribute="participationtypemask" operator="eq" value="5" />
</filter>
<link-entity name="appointment" from="activityid" to="activityid" link-type="inner" alias="ah" />
</link-entity>
</entity>
</fetch>
#1
André Arnaud de Cal...
291,969
Super User 2025 Season 1
#2
Martin Dráb
230,842
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
