Optimizing your Aged Accounts Receivables Report

Overview
Doing numerous upgrades from an older version of Navision to NAV 2015 and 2013, one common complaint is how slow the reports are running. This is especially true for larger reports like the Aged Accounts Receivables, Aged Accounts Payables , Inventory Valuation, etc.

The old reports that took a long time, such as the Inventory Valuation report, will still take a long time. It doesn’t matter what version you go to. However, there are some reports that used to be quick, but is slow after the upgrade.

One of these reports is the Aged Accounts Receivables report (Report ID: 10040 – Aged Accounts Receivable).

The Breakdown
CAUTION: I’m about to get “programmer”. If you want the faster report, just skip down to the bottom and download the object.

Removing the Data Type of Column from the report, we get the following DataItem that the report loops through:

AgedReceivables

On initial look, the report looks simple enough. There are 3 data items:

  • Customer – The report loops through the customer record to see which customer we need to calculate the aging for
  • Cust. Ledger Entry – For every customer record it finds, it will loop through the all of the customer ledger for that customer. For any customer ledger that has a remaining amount, it’ll put it into a temporary table
  • Integer – This dataitem loops through the same records that are inserted into the temporary table on the Cust. Ledger Entry dataitem and summerizes the information to display in different aging “buckets”.

The Problem
The reason why this report is slow is if you check the DataItemTableView property on the Cust. Ledger Entry dataitem, you’ll see that the report is looping through ALL of the customer ledger for that customer.

NoFilterSet

This report will run fine if you’re A/R aging is small. However, this report will get slower as time progresses with more transactions. Worst, it’ll consume all the memory on the server and force you to restart.

The problem becomes real apparent when you have EDI customers that are running hundreds of invoices per day.

The Solution
The idea of the original A/R aging report is correct. Basically, look at the remaining amount based on the date criteria; if there is a balance, then it goes into the calculation.

The problem is that it’s not running any type of filters to exclude old transactions that has no relevance in our calculation.

To address the performance problem, here are the main things we will need to do:

  1. Look at only transactions that are marked as Open
  2. If the report is to be backdated, look into the only the history that pertains to the date criteria

First thing we do is to set the proper DataItemTableView property with the filter of Open.

FilteronOpenOnly

Then we need to add a new Detailed Cust. Ledger Entry dataitem to look at the application history of our A/R transaction:

The Property:

DCLEProperty

The Code:

AddDetailedCustLedger

Basically, we’re limiting the reads of the database to only open transactions and the subsequent A/R applications from the Aged as of Date set on the report.

Here are the report objects and the text file for your reference:
OptimizedARAging

Conclusion
Not sure what the developer at Microsoft is thinking when programming this report. Aged Accounts Receivable/Payable is one of the most data intensive reports next to the inventory valuation. Reading through every record just does not make sense.

Yes, it’ll work in the short run, but give a few years and the report will slow to a crawl, which is already experienced by customers upgrading.

  1. Interesting! I have always applied the first part of your suggested fix (as I think, filter on Open was there in older versions). But the second part of your optimization is even more interesting! 🙂

  2. Yes. If you do not apply the 2nd part, you will not be able to backdate the report.

  3. I think the report was written with the C/SIDE database structure which places check marks in the database at the end of periods to help speed up reporting when the computers were running in 286 processors and very little ram. Also customers were encouraged to use the compression routines in Navision to squash down ledgers to month end balances which again allow for faster reporting. With the advent of SQL for Navision the reporting structure needs to be updated , as you have shown, to accommodate the lac of compression within the database.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>