I assume you are familiar with the below post that contains detailed instructions for increasing relevant timeouts to enable long running reports to execute successfully.
The first recommendation in case of reports which use the Report Data Provider to retrieve the data is to convert the report to pre-processing.
Why may this help to avoid hitting the timeouts? It may because it shifts the data preparation step to before sending the request to the report server (RS).
Without pre-processing the report is executed in these steps:
With pre-processing the steps are executed in a different order:
The report server execution time is the time between the two steps:
This explains why in case of reports with pre-processing it takes less time for the report server to render the report and why some timeouts will not be hit. (In many reports, it is actually the preparation of the data by AX that is most time consuming.)
This also explains why converting a report to pre-processing does not necessarily mean that the report will execute faster in overall. Particularly it will not, if the underlying tables are SQL tables. In these cases, the steps are only swapped – nothing else. The overall execution time of the report as perceived by the user is the sum of all three steps and this sum is the same in either approaches. On the other hand, reports that use InMemory temporary tables may execute faster after the conversion because pre-processing is implemented with SQL tables. InMemory temporary tables with large amounts of data may need to be paged to the file system and that may influence the data retrieval time. Moving these data to SQL tables may improve the report’s overall execution time.
Keeping in mind that only reports that use the Report Data Provider class can be converted to pre-processing (and this in fact does not necessarily lead to reports executing faster in overall), it is often required to increase the timeouts to enable a long running report to complete successfully. In order to reduce the trial and error phase to minimum, it makes sense to configure very generous timeouts for the start just to go sure that a report can be executed successfully. There is basically nothing that speaks against setting this timeout initially to 24 hours, for example. If the report was executed successfully we can take the exact time from the corresponding RS log file or RS Execution Log (they are collected by default) and reduce the timeouts to a value that ensures a successful execution of the affected report.
Configuring the timeout to a value higher than 12 hours, it is also recommended to increase the RecycleTime in the rsreportserver.config file accordingly. This value is 720 minutes per default (12 hours). We saw that when the application domain got recycled while a long running report was executed that it led to an error in most cases. Taking into account that all thresholds/timeouts will be reset at the Reporting Services service startup, it is recommended for testing to restart the service before starting a long running report so all counters get initiated at the same time.
Furthermore, in AX 2012 R2, it is required that the kernel build version is 6.2.1000.6578 or higher, in AX 2012 R3, 22.214.171.1241 or higher (KB 2936794). If it is not, the latest kernel hotfix available to date should be installed. Installing the latest kernel hotfix is recommended in general. (See FAQ: Microsoft Dynamics AX Kernel Hotfixes if you have any questions regarding installing a kernel hotfix.)
- If the report continues to fail, make sure that it does not fail due to a different reason (check RS log file/RS Execution Log).
- If the report fails due to a timeout again, make sure if it fails at the value that has been just configured (check RS log file/RS Execution Log). If so, increase the timeouts.
- If the report fails at a time before the chosen timeout, make sure that all settings were configured properly, none is missing or has an incorrect value by mistake.
- If the report fails at a time before the chosen timeout and this time is always the same, check in the log how long it took exactly. 10 minutes? 30 minutes? Knowing it may help in identifying the setting that was missed when increasing. 10 minutes – could be a call to the AOS service, for example. 30 minutes – could be the default timeout on the report in the report server. Double check if the timeouts were configured properly following the post thoroughly.
- If the report fails at a time before the chosen timeout, this time is always the same and none of the settings is missed, upload the attached report WaitFor2012.rdl to the report server (using Report Manager) and run it directly on the report server. This report is a pure SSRS report so there is no AX component involved. Enter the timeout currently configured and verify if the report fails again before this time and if this time is the same. If the report does fail, the reason could be an external (non-SSRS and non-AX) setting, in the network or ASP.NET, for example. If the report does not fail, this would indicate an issue in AX that may require investigation by AX support.
For detailed instructions on how to convert a report to pre-processing, refer to How To: Long Running Reports.
For more information on the new pre-processing class SrsReportDataProviderPreProcessTempDB, refer to Improving SSRS Report Performance using new R3 features – Part 6.