Report Performance Optimization in Dynamics NAV 2013 R2

Introduction

In this demonstration we are going to look at how to speed up report generation in Dynamics NAV 2013 R2. Reporting is an important tool for company management and operation, and when slowdowns start to cause issues they can have serious business impacts. A real world example would be if it took more than an hour to generate a physical inventory list it could it could impact a company’s manufacturing department which would not be able to take certain actions until the list is updated.

Report optimization is usually a necessity for large production databases where the amount of data and calculations required causes a high load on hardware, resulting in reports taking hours to be generated. Let’s have a look at what can be done to speed up this process.

Main Optimization Areas

There are three areas which can be optimized to have reports be generated faster:

  • Request Page
  • DataSet
  • Layout

Request Page

This is the area where users can specify filters, so that the report is generated and processed using less overall data. This is especially useful in reports involving various ledger data. A date filter would be one option to limit the time it would take to generate the report.
Example: Report Detail Trial Balance. For every G/L Account it loops through the G/L Entry table:

When running this report on the ‘Request Page’ we can see an example of a date filter that can potentially speed up the report. A counter example is ‘New Page per G/L Account’, which if selected will require more time to create the pages and consequently the entire report.

DataSet

Next, let’s have a look at how we can optimize the report dataset. This can be achieved by removing unnecessary columns or restructuring them into appropriate DataItems.

Below is the design of the standard Dynamics NAV report – Bank Account List:

The FIELDCAPTION columns can be removed from the dataset and be replaced using the Labels feature. This will delete the caption columns from the dataset and will make them report parameters.

Let’s have a look at how it works. In the Report Dataset Designer, delete the FIELDCAPTION(“No.”) row and click View, Labels to open the Labels. Create the following label:

Close the Labels window and click View, Layout. What we can see now is that the Bank Account No. field was removed from the dataset and was moved to the report parameters:

This is a very good way to optimize report performance, because a smaller dataset is processed by the report.

Another example of report optimization is to use filtering prior to dataset generation, such as in the C/AL code or on the DataItem itself. The place to filter DataItem is in its properties, specifically the DataItemTableView property:

Once Dynamics NAV has applied the filter on the DataItem, it will prepare for the smaller amount of data to be processed by the report.

An example of filtering in C/AL code is a report which uses a buffer table, an example being Trial Balance/Previous Year. Here we can see that an Integer buffer table is used to loop through the data in every G/L Account while the looping calculations are done in C/AL code.

Layout

To performance optimize the report layout itself the following tips can be applied:

  1. Avoid using the Toggle Item functionality. Every time you expand/collapse the data region, it causes load on CPU and memory:
  2. Example: Customer Detailed Trial Balance report where the ledger data region can be expanded/collapsed:

    The Toggle Item for customer ledger data in this case is the Customer No. field:

  3. The report header expressions feature is another heavy performance hitter. The less of them used in a report, the quicker it will be generated. This is especially a concern for reports where there is a large document header with company information, such as invoices and shipments:

    GetData and SetData functions, which set the actual data in report body as hidden and then retrieve and display this data in the report header. These processes cause loads on report engine so a good option would be to use these Expression fields as sparingly as possible.

Conclusion

In this demonstration we have covered the most essential ways to speed up the processing of report generation and the building of report datasets. However, there are many other ways for report optimization, including optimizing reports containing charts and diagrams, or executing reports with TOP N functionality.

Please let us know in the comments if you were able to speed up your reports, or if you have other optimization tips to share.