web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How to pull the Requir...
Microsoft Dynamics CRM (Archived)

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

(0) ShareShare
ReportReport
Posted on by Wayne Walton 13,730

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)
  • wilson_smyth Profile Picture
    wilson_smyth on at

    That link would have been very helpful! Facing similar when creating reports here also.

  • Community Member Profile Picture
    Community Member on at

    I didn't save the link unfortunately.

  • Megan V. Walker Profile Picture
    Megan V. Walker 1,841 Most Valuable Professional on at

    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.

  • Community Member Profile Picture
    Community Member on at

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

    Thanks for the help

  • Megan V. Walker Profile Picture
    Megan V. Walker 1,841 Most Valuable Professional on at

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

  • Suggested answer
    Megan V. Walker Profile Picture
    Megan V. Walker 1,841 Most Valuable Professional on at

    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. 

    rows-in-database.PNG

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

  • Community Member Profile Picture
    Community Member on at

    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.

  • Suggested answer
    Megan V. Walker Profile Picture
    Megan V. Walker 1,841 Most Valuable Professional on at

    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>

  • Community Member Profile Picture
    Community Member on at

    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>

  • Suggested answer
    Megan V. Walker Profile Picture
    Megan V. Walker 1,841 Most Valuable Professional on at

    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>

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Shidin Haridas Profile Picture

Shidin Haridas 2

#2
Abdullah13 Profile Picture

Abdullah13 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans