How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

(0) Share

Report

Posted on by Wayne Walton

13,728

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

I have the same question (0)

All responses (12)

Answers (0)

Megan V. Walker 1,810 Most Valuable Professional on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

Hi Wayne,

What does your FetchXML look like? You should be able to do this. You say required attendees is a custom lookup type, so you aren't using the Participation Type field with the Required attendee option?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

Hi Megan, We are taking the fetch xml from the advanced find and using that in the report. Every time I add the attendees I get an error in the report that says "An error occurred while executing the query. The attribute type "PartyList" is not supported. Remove attribute "requiredattendees" from the query and try again." This is inside of visual studio in the query editor. The fetch xml works fine when in advanced find but not in the SSRS report.

Was this reply helpful? Yes No
Suggested answer

Megan V. Walker 1,810 Most Valuable Professional on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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>

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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>

Was this reply helpful? Yes No
Suggested answer

Megan V. Walker 1,810 Most Valuable Professional on at

Like (1)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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>

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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.

Was this reply helpful? Yes No
Suggested answer

Megan V. Walker 1,810 Most Valuable Professional on at

Like (1)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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>

Was this reply helpful? Yes No
Megan V. Walker 1,810 Most Valuable Professional on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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)?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

Hi Megan, Unfortunately no it didn't. I ended up finding a solution from a blog online that worked.

Thanks for the help

Was this reply helpful? Yes No
Megan V. Walker 1,810 Most Valuable Professional on at

Like (0)

Report

RE: How to pull the Required Attendees on an Appointment for an SSRS report in CRM Online?

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.

Was this reply helpful? Yes No