SBX - Search With Button

SBX - Forum Post Title

SSRS - wont support PARTY LIST? for Appointment "Required Attendees"

Microsoft Dynamics CRM Forum

donyc asked a question on 6 Apr 2014 2:22 PM
My Badges

Question Status

Suggested Answer

Hi all, I am trying to create a - Summary Report per ACCOUNT, and one table will hold my appointments details. I have a table with Dataset for Appointment info.. I noticed that it does not allow me to report on required attendees??? Is this true? If so is there a workaround around this? I basically want my report to show appointments details and of course who participated. Any advice is much appreciated! I used below fetchXML to get Appointment details

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="appointment">
<attribute name="subject"/>
<attribute name="statecode"/>
<attribute name="scheduledstart"/>
<attribute name="activityid"/>
<attribute name="instancetypecode"/>
<attribute name="requiredattendees"/>
<order attribute="subject" descending="false"/>
<link-entity name="account" from="accountid" to="regardingobjectid" alias="ae">
<filter type="and">
<condition attribute="accountid" operator="eq"  value="@name"/>
</filter>
</link-entity>
</entity>
</fetch>
Reply
donyc responded on 7 Apr 2014 7:58 AM
My Badges

this is the error I get.. anyone seen this issue before????

Reply
Guido Preite responded on 7 Apr 2014 8:23 AM
My Badges
Suggested Answer

It's a limitation of FetchXML, a solution is to copy the string values of the standard attribute to a custom field (by plugin or with a workflow using a custom activity) and use the custom field inside the report.

Reply
Suggested Answer

Hi,

As Guido says, it's a limitation of Fetchxml that it can't flatten down the Activity Parties into a single field to display in your report.

You have a number of options in addition to creating a calculated field using a plugin:

1. Create a master/detail report so that the activity parties are shown as a sub report for each activity line in the master report (this does have performance penalties) nishantrana.wordpress.com/.../creating-a-master-detail-report-in-ssrs-without-using-sub-report

2. Create a simple grouped report where each activity party is shown as a separate row grouped by the activity subject - you can do this using the report wizard if you add both the activity party entity and appointment to the report entities and then add the subject as a grouped by row and the Activity Party as a detail row. This will give you some fetchxml similar to:

<fetch distinct="false" no-lock="false" mapping="logical">

 <entity name="appointment" enableprefiltering="1" prefilterparametername="CRM_FilteredAppointment">

   <attribute name="subject" alias="subject" />

   <attribute name="activityid" />

   <link-entity name="activityparty" to="activityid" from="activityid" link-type="outer" alias="activityparty1" enableprefiltering="1" prefilterparametername="CRM_FilteredActivityParty">

     <attribute name="participationtypemask" alias="activityparty1_participationtypemask" />

     <attribute name="partyid" alias="activityparty1_partyid" />

     <attribute name="activitypartyid" />

     <attribute name="partyidname" />

   </link-entity>

 </entity>

</fetch>

3. If you are using OnPrem, you can create a separate SQL database to act as a SSRS helper database where you can add a funciton that flattens down the activity party list to a string for a given activity id and re-write your report to use SQL rather than fetchxml.

Hope this helps,

Scott

Reply
Guido Preite responded on 7 Apr 2014 8:23 AM
My Badges
Suggested Answer

It's a limitation of FetchXML, a solution is to copy the string values of the standard attribute to a custom field (by plugin or with a workflow using a custom activity) and use the custom field inside the report.

Reply
Suggested Answer

Hi,

As Guido says, it's a limitation of Fetchxml that it can't flatten down the Activity Parties into a single field to display in your report.

You have a number of options in addition to creating a calculated field using a plugin:

1. Create a master/detail report so that the activity parties are shown as a sub report for each activity line in the master report (this does have performance penalties) nishantrana.wordpress.com/.../creating-a-master-detail-report-in-ssrs-without-using-sub-report

2. Create a simple grouped report where each activity party is shown as a separate row grouped by the activity subject - you can do this using the report wizard if you add both the activity party entity and appointment to the report entities and then add the subject as a grouped by row and the Activity Party as a detail row. This will give you some fetchxml similar to:

<fetch distinct="false" no-lock="false" mapping="logical">

 <entity name="appointment" enableprefiltering="1" prefilterparametername="CRM_FilteredAppointment">

   <attribute name="subject" alias="subject" />

   <attribute name="activityid" />

   <link-entity name="activityparty" to="activityid" from="activityid" link-type="outer" alias="activityparty1" enableprefiltering="1" prefilterparametername="CRM_FilteredActivityParty">

     <attribute name="participationtypemask" alias="activityparty1_participationtypemask" />

     <attribute name="partyid" alias="activityparty1_partyid" />

     <attribute name="activitypartyid" />

     <attribute name="partyidname" />

   </link-entity>

 </entity>

</fetch>

3. If you are using OnPrem, you can create a separate SQL database to act as a SSRS helper database where you can add a funciton that flattens down the activity party list to a string for a given activity id and re-write your report to use SQL rather than fetchxml.

Hope this helps,

Scott

Reply

SBX - Two Col Forum

SBX - Migrated JS