SSRS Fetch XML based CRM Online Report performance in Dynamics 365

Hi,

Recently we were analyzing the performance of a fetch xml based online report.

The report was running against opportunity, and had 4 data set in it. Custom entity A and custom entity B were n – 1 related to opportunity.

Data Set Opportunity	13000 records
Data Set for Custom Entity A used for Lookup	100 records
Data Set for Custom Entity B used for Lookup	8000
Data Set for Tasks Activity used for Lookup	130000 records

More on Lookup Function in SSRS

Running without filter	107 seconds
Filter Last 6 months	94 seconds

We then updated the report’s design, to include only one Dataset by using link entities in the main dataset’s fetch xml. We got the below result

Running without filter	280 seconds
Filter Last 6 months	10 seconds

Another variation we tried was having two Dataset, one for opportunity and another one for tasks to be used for lookup.

Running without filter	50 seconds
Filter Last 6 months	50 seconds

The last variation we tried was using only one Dataset for Opportunity in the main report and the drill down report for Tasks

Running without filter	34 seconds
Filter Last 6 months	7 seconds

It clearly shows that the most efficient way of implementing is through the usage of drill down report, only glitch here is user needs to click on the main report to get the details.

Using single dataset is efficient for subset of records, however it can take long time if it runs on huge number of records in which case lookup performs better.

Basically,