RSMUS.comI was recently tasked with creating an SSRS report for an organization with Microsoft Dynamics CRM 2015 on-premise. The report needed to contain dynamic fields from the service activity, the regarding record (which for this organization is always a case), the client related to that case, and the resource for the service activity. I was able to use the FetchXML builder from Cinteros within the XML toolbox to easily build my FetchXML for all the required entities except for the resource. The problem I ran into is that the Resources field on the Service Activity is a party list, and FetchXML cannot use party list fields.
After digging around on the internet for a while I found this article by Microsoft (https://msdn.microsoft.com/en-us/library/gg328549.aspx). I was able to access information from the resources user record by linking first to the ActivityParty entity, then the SystemUser entity.
Another requirement for the report was to dynamically filter the report based on the scheduled start date for the service activity and the resource. The scheduled start filter is easily accomplished by using a parameter within MS Visual Studio. The requirement for the resource required a second data set within the report. The client wanted to be able to select multiple users from a list of all the users that were resources for this specific type of service between the start date and end date entered by the user running the report. My first idea was to edit the FetchXML from the first dataset to create a new data set, but this time only pull distinct user IDs from the link entity area related to resources, but FetchXML does not support the distinct function for linked entities.
I ended up writing a new FetchXML to fetch distinct users linked to activity party based on their Dynamics CRM log in linked to service activities with the service the client is interested in and adding a parameter to the report to filter based on the second dataset query. The code for both data sets is below.
Code for the Report Data:
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true” >
<entity name=”serviceappointment” >
<attribute name=”activityid” alias=”SAActivityID” />
<attribute name=”scheduledstart” alias=”SAScheduledStart” />
<attribute name=”createdby” alias=”SACreatedBy” />
<order attribute=”scheduledstart” descending=”false” />
<filter type=”and” >
<condition attribute=”serviceid” operator=”eq” uitype=”service” value=”{F88267F9-216D-E511-8145-1458D04DB4D0}” />
<condition attribute=”scheduledstart” operator=”on-or-after” value=”@StartDate” />
<condition attribute=”scheduledstart” operator=”on-or-before” value=”@EndDate” />
</filter>
<link-entity name=”systemuser” from=”systemuserid” to=”createdby” link-type=”outer” alias=”CreatedBy” >
<attribute name=”fullname” alias=”CreatedByFullName” />
<attribute name=”internalemailaddress” alias=”CreatedByEMail” />
<attribute name=”address1_telephone1″ alias=”CreatedByPhone” />
</link-entity>
<link-entity name=”incident” from=”incidentid” to=”regardingobjectid” link-type=”outer” alias=”Case” >
<link-entity name=”account” from=”accountid” to=”customerid” link-type=”outer” alias=”Client” >
<attribute name=”name” alias=”ClientName” />
<attribute name=”address1_composite” alias=”ClientAddress” />
</link-entity>
</link-entity>
<link-entity name=”activityparty” from=”activityid” to=”activityid” link-type=”outer” alias=”ActivityParty” >
<filter type=”and” >
<condition attribute=”participationtypemask” operator=”eq” value=”10″ />
</filter>
<link-entity name=”systemuser” from=”systemuserid” to=”partyid” link-type=”inner” alias=”R” >
<attribute name=”mcgcc_supervisorcode” />
<attribute name=”fullname” />
<attribute name=”internalemailaddress” />
<attribute name=”address1_telephone1″ />
<attribute name=”address1_line1″ />
<attribute name=”address1_city” />
<attribute name=”address1_stateorprovince” />
<attribute name=”address1_postalcode” />
<attribute name=”address1_country” />
<link-entity name=”systemuser” from=”systemuserid” to=”parentsystemuserid” >
<attribute name=”fullname” alias=”SupervisorName” />
</link-entity>
</link-entity>
</link-entity>
</entity>
</fetch>
Code for the filter query
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true” >
<entity name=”systemuser” >
<attribute name=”systemuserid” alias=”UserID” />
<attribute name=”fullname” alias=”FullName” />
<link-entity name=”activityparty” from=”addressused” to=”domainname” link-type=”inner” alias=”ActivityParty” >
<attribute name=”addressused” />
<filter>
<condition attribute=”participationtypemask” operator=”eq” value=”10″ />
</filter>
<link-entity name=”serviceappointment” from=”activityid” to=”activityid” link-type=”inner” alias=”ServiceActivity” >
<filter>
<condition attribute=”serviceid” operator=”eq” uitype=”service” value=”{F88267F9-216D-E511-8145-1458D04DB4D0}” />
<condition attribute=”scheduledstart” operator=”on-or-after” value=”@StartDate” />
<condition attribute=”scheduledstart” operator=”on-or-before” value=”@EndDate” />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>
For more information about this topic or for other questions about how to get the most out of your Microsoft Dynamics CRM solution, contact our CRM experts at RSM or call 855-437-7202
By Steve Trefz at RSM
