crminthefield: Dynamics CRM Custom FetchXML Reporting with Multiple Datasets using Pre-Filtering

[Blog bot]

Источник: http://blogs.msdn.com/b/crminthefiel...filtering.aspx
==============

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

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.

Report Design

They were able to accomplish the pre-filtering requirements by creating their main data set and setting pre-filtering = 1:

</strong> They the created 5 related data sets that were pulling specific connection relationships, the following example is the dataset named &ldquo;Employee&rdquo;:

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&rsquo;t change the timeout settings from the server side, we had to modify the report query to get the report working:


By adding the count = &lsquo;5000&rsquo; 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.

Thanks!

Sarah Champ



Follow the conversation:
@pfedynamics | http://www.pfedynamics.com















Источник: http://blogs.msdn.com/b/crminthefiel...filtering.aspx