Check out the latest features available in Dynamics 365 for Customer Engagement, including LinkedIn Connect, Voice of the Customer and Universal Resource Scheduling.
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
I was recently assisting a CRM Online customer with troubleshooting a custom report they built. The report was running on the Opportunity as the main entity and also had 5 related datasets that were bringing in some specific connections related to that Opportunity. They were facing three big hurdles:
1. They wanted to use pre-filtering on the report so that users could run the report from list views and advanced find
2. They wanted to display Opportunity information but also related connection information in one table
3. Being that they are using CRM Online the report must use FetchXML as the data processing extension and due to some server side settings, they also had to consider the timeout settings that are in place.
They were able to accomplish the pre-filtering requirements by creating their main data set and setting pre-filtering = 1:
<fetch distinct="true" no-lock="true" mapping="logical">
<entity name="opportunity" enableprefiltering="1">
<attribute name="statuscode" />
<attribute name="statecode" />
<attribute name="new_stage" />
<attribute name="estimatedvalue" />
<attribute name="customerid" />
<attribute name="name" />
<attribute name="actualclosedate" />
<attribute name="ownerid" />
<attribute name="estimatedclosedate" />
<attribute name="createdon" />
<attribute name="opportunityid" />
<order attribute="estimatedvalue" descending="true" />
They the created 5 related data sets that were pulling specific connection relationships, the following example is the dataset named “Employee”:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<attribute name="record1id" alias="OpportunityID"/>
<attribute name="record2id" alias="Employee"/>
<condition attribute="record1objecttypecode" operator="eq" value="3" />
<condition attribute="record1id" operator="in" value="@OpportunityID" />
<condition attribute="record2id" operator="not-null" />
<link-entity name="connectionrole" from="connectionroleid" to="record2roleid">
<condition attribute="name" operator="eq" value="Team"/>
Then in the report table, they used the following expression to pull the values from those other datasets into their respective fields, joining based on the @OpportunityID parameter being set by default from the main dataset query:
=join(LookupSet(Fields!opportunityid.Value, Fields!OpportunityIDValue.Value, Fields!TeamLead.Value, "Employee"), Constants.vbcrlf)
So we were able to get the report working as expected in a development environment where there was significantly less data than their UAT and Production environments. When they deployed the report into their UAT environment, we noticed that the report was timing out, regardless of the view we were running it from or how many records were being returned in our pre-filter criteria.
After reviewing the report and considering causes, what we found out is that by default, when using pre-filtering, FetchXML will return the max number of records allowed for a dataset (15001) unless otherwise specified and then filter by the pre-filter and reporting criteria after. Because we couldn’t change the timeout settings from the server side, we had to modify the report query to get the report working:
<fetch version="1.0" count='5000' output-format="xml-platform" mapping="logical" distinct="false">
<entity name="opportunity" enableprefiltering="1">
By adding the count = ‘5000’ into the header of the FetchXML, the report was able to render successfully in the environments with larger datasets, preventing the timeout. For more information around Count and other functions within fetch you can reference this MSDN article Use FetchXML to Construct a Query.
The final outcome ended up something like this:
If you are interested, our PFE team is ready to help you. We have many services we offer such as reporting workshops, developer training, admin workshops, and code reviews. If you would like to have another Microsoft PFE or I visit your company and assist with the ideas presented on our blog, contact your Microsoft Premier Technical Account Manager (TAM) for booking information. For more information about becoming a Microsoft Premier Customer email PremSale@microsoft.com.
Follow the conversation: @pfedynamics | http://www.pfedynamics.com
Business Applications communities