This blog post was written to help you fix SSRS Timeouts within Microsoft Dynamics AX 2012. Reporting in Microsoft Dynamics AX 2012 has been updated from previous versions of Dynamics AX. Where we used to use MorphX reports in AX 2009 and earlier versions, the entire AX 2012 reporting solution has been migrated to SQL Server Reporting Services. Reporting SSRS timeouts can cause lots of frustration for users and impact critical business processes such as month-end, year-end, etc.
In this blog post we will discuss the causes of timeouts in AX 2012 SQL Server Reporting Services (SSRS) reports. The intended audience of this blog post are Dynamics AX developers and other technical professionals. If you are seeing errors like this, read on to discover some common ways to fix reporting timeout issues in AX 2012!
We have separated this blog post into two main sections:
SSRS report timeouts in AX 2012 are often caused by inefficient code/query design in AX. To identify where the bottleneck is, we need to debug and explore the SSRS logs.
Before we get started, you need to make sure you have the right components to debug.
To debug Report Data Provider (RDP) classes, you must be on the Application Object Server (AOS) machine and that same machine must have the SSRS instance installed. Also the AOS must be setup for debugging.
SSRS reports in AX can get data from one (or more) places:
Debugging a Query can be tough, as it simply pulls data from the AX database from an AX query object in the Application Object Tree. See the section below on query optimization for techniques on improving query performance.
To debug a report based on an RDP, set a breakpoint in that report’s
Observe the behavior of the RDP class using the same parameters as your report users to see where your bottlenecks are.
You can view the logs on the SSRS server to get more details on a timed out report. The logs can be found on the SSRS Server at
\Program Files\Microsoft SQL Server\MSRS10.<Instance Name>\Reporting Services\LogFiles.
Reviewing SSRS logs will provide more information than AX infolog dialogs in many cases.
If the log doesn’t provide enough information, you can increase the
DefaultTraceSwitchsetting in the
ReportingServicesService.exe.config file. The file can be found at
\Program Files\Microsoft SQL Server\MSRS10.<Instance Name>\Reporting Services\ReportServer\bin.
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.
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.
If a query takes a long time to run, see if you can make it run more efficiently.
Common areas of improvement are:
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
VendGrouptable is only to filter the results, no
VendGroup 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
Here we have set the
JoinMode property of the
VendGroup node to ‘
ExistsJoin’ and removed all of the unnecessary fields from the
Now when the query runs, it will run faster due to the reduced amount of data requested from the Dynamics AX database.
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.
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:
ChequeDPclass to extend the
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 Dynamics 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 Dynamics 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.
KB 2936794 – Printing reports to printer is failing without timeout error for larger reports
To prevent this type of discrepancy, always make sure the ‘Do not allow manual entry’ checkbox is marked on the ledger account to avoid direct posting to the account. This setting requires that all transactions post through the subledger accounts.
Occasionally the discrepancies are not as clear in the Customer reconciliation report and the problem is not due to direct postings to the GL account. When there might be a discrepancy within the subledger (customer) balances, compare the Customer aging report balance to Customer balance list with credit limit report balance to identify any discrepancies within a customer account.
The Customer balance list with credit limit report shows the customer summary balances that posted to the ledger. Sometimes the Customer aging report can be off from the ledger due to settlement dates, prepayments or other issues. If there is a discrepancy between these 2 reports, then compare the individual customer balances on the reports to identify the problem customer account(s).
In our example, the Customer balance list with credit limit report ties out to the Customer Aging report.
Once the reconciliation issues are addressed make sure the proper steps (fix setups, training, etc) are taken to avoid the same problems in the future.
Now that you know how to Reconcile Accounts Receivable, the Accounts payable reconciliation can be accomplished in a similar way using similar reports in the Accounts payable module.
For more information about Accounts Receivable Account and how you can Reconcile Accounts Receivable in Microsoft Dynamics AX2009, AX2012 R2, R3, D365 contact our Dynamics experts for training proposal, demos etc. on email@example.com.
If your organization are considering a move to Microsoft Dynamics 365 for Finance & Operation you can watch this (How to upgrade from Microsoft Dynamics AX to Dynamics 365 Finance & Operation?) upgrade tutorial where Michael Oakes take you through the different upgrading paths. You can also schedule a demo designed for your needs – Tell us about your needs, requirements, budget and number of users and we will get back to you – firstname.lastname@example.org
Introduction Navigating the intricate landscape of modern financial platforms can be daunting for many business professionals. As digital transformation redefines the industry,
Explore the transformative benefits of Microsoft PowerApps. Introduction In today’s rapidly evolving business landscape, the integration of automation is imperative to help
Explore the benefits, features, and implementation strategies of the Microsoft Dynamics 365 Business Central Shopify integration. This powerful integration seamlessly connects backend
Book a free meeting and let us have a look at your opportunities with Microsoft Solutions
Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.