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,

g

Hope it helps..


Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

1 thought on “SSRS Fetch XML based CRM Online Report performance in Dynamics 365”

Share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s