How to Improve Performance of SSRS Reports in D365 FO

Performance has been one of the huge bottlenecks for SSRS Reports since AX 2012 RTM.

Even in D365 FO if the reports are not optimized properly there can be heavy lags in the overall processing.

There are several ways through which we perform optimization in SSRS Reports :-


1) SRSReportDataProviderPreProcessTempDB - When we are creating Report Data Provider
Logic based report we should always extend our DP class with SRSReportDataProviderPreProcessTempDB. This class extension has been proved very helpful for optimization especially when we are dealing with huge amount of data in the report.

Here is an example :-

class ABCDP extends SRSReportDataProviderPreProcessTempDB
{
}


2) RecordInsertList - RecordInsertList object helps to perform bulk insertion in one go without bothering the back end to go for multiple round trips for record insertion.

Here is an example :-

RecordInsertList              insertList;
ABCTMP                         abcTmp;


insertList = new RecordInsertList(tableNum(ABCTMP), false, false, false, false, false, abcTmp);

abcTmp.Field1= "A";
abcTmp.Field2="B";

insertList.add(abcTmp);

insertList.insertDatabase();


3) RecordViewCache - RecordViewCache is one of the caching mechanisms provided by x++ in the form of a class. It helps to cache the records so that upon running the data can be fetched right away from cache rather than generating from the Report Dataset.

This mechanism is useful only for those reports which have to be executed very frequently in a day.

Here is an example :-

RecordViewCache      viewCache;
ABCTMP                    abcTmp;

abcTmp.Field1= "A";
abcTmp.Field2="B";

viewCache = new RecordViewCache(SOSummaryWithInvTmp);

abcTmp.insert();


4)  Refrain Use of While Select and multiple select statements :- Avoid using while select and multiple select statements as it causes multiple roundtrips with Database thereby degrading the performance in several ways. Try using views or queries.

In case you have to use While select make sure that you are fetching only those fields are fetched which are required. Field List is while select is always helpful.

In case multiple select statements without field list make sure you are using firstonly in front of the table buffer.

Here is an example :-

select firstonly custtable;



If we follow the above 4 principles properly we will be able to see significant improvement in the performance of our SSRS Report.



Happy Coding !!!