REPORT TIMEOUT SOLUTIONS

Once we have identified the error, sometimes we need a little help planning our attack on improving report runtimes. We have provided some common solutions to long running reports here.

Note that these code improvements will only have effect on large data sets. Make sure you know the source of your timeouts before taking the time to redesign code.

CODE REVIEW

We all make mistakes. Perhaps the developer who wrote the RDP class made a mistake or the data processing requirements of the report have changed.

Code Review: Query

If a query takes a long time to run, see if you can make it run more efficiently.

Common areas of improvement are:

• Inner joins vs. exist joins
• Dynamic field property set to Yes

If the business requirement is to see if a record exists, we can accomplish this through an inner join. However, when we do this we may be pulling unnecessary amounts of data into AX.

For example, the business requirement in the query below is to give all purchase order lines where the vendor’s vendor group has a payment term of 30 days. The join to the VendGroup table is only to filter the results, noVendGroup fields are required in the report.

This query is making two mistakes; the query does not need to perform an inner join and it doesn’t need to pull all the fields for the VendGroup table.

Here we have set the JoinMode property of the VendGroup node to ‘ExistsJoin’ and removed all of the unnecessary fields from the VendGroup node.

Now when the query runs, it will run faster due to the reduced amount of data requested from the AX database.

Code Review: RDP Class

If an RDP class is written in an inefficient way, it can greatly impact report running times. Here we will provide an example of an RDP class that can be refactored to improve report performance.

For example, say we have a very simple business requirement to pull the number of invoices that a given vendor has had in a report.

In the code segment below, an extra SQL call is made for each vendor in the report.

We can optimize this report by having all the invoice counts done during the initial report query.

In the resulting RDP class, we only make one call to SQL to get the total number of invoices for each vendor. This reduces network traffic, causes the report to run more quickly, and reduces the number of calls to the SQL server during report execution

SPECIAL CASE: AX KERNEL CODE

This scenario is a *last resort* for troubleshooting. Once you have exhausted query/RDP re-design as well as AX/SSRS settings you will most likely need to submit a support request with Microsoft. You can contact your Partner to do this and they will contact Microsoft on your behalf to resolve the issue.

To give an example of this type of scenario, myself and my team had a customer that was printing check runs of 1,000+ checks. The customer was running AX 2012 R2 CU 7. This is not terribly uncommon and AX should be able to handle these types of reporting scenarios. To add to the complexity, the ChequeDP RDP class and SSRS report (along with accompanying C# code) was heavily customized by a previous Partner and we had no documentation. To resolve the issue we tried the following:

• We re-designed the ChequeDP class to extend the SrsReportDataProviderPreProcess
• We increased timeouts in the Reporting Services AXC file as well as an isolated AX Client AXC file
• We increased timeouts in the Production instance of SSRS
• We increased timeouts in the Ax32Serv.exe.config file for the AOS handling reporting requests

Even after increasing all available timeouts to over an hour, the report was timing out against the SSRS instance after approximately an hour. Nothing seemed to work and we reached out to Microsoft. After troubleshooting it turned out to be an AX Kernel issue. As a Partner we do not have visibility into the AX Kernel code. Microsoft provided a (at the time unpublished) hotfix. We are providing the KB number and title here for reference purposes. If you are seeing timeouts after implementing the suggestions in this blog post and are running AX 2012 R2 CU7 (we cannot attest to other versions of AX 2012) you may want to reach out to Microsoft to resolve this issue and/or log into your Life Cycle Services account to download the latest hotfix for this issue.