Microsoft Dynamics NAV reports seems to be working very well in terms of performance, however, there are few cases when the reports hit the limit. In this blog post we explore Microsoft Dynamics NAV report optimization. Whenever a report dataset exceeds a certain size limit you see the message below (OutOfMemoryException).

The dataset in RDLC reporting has a certain limit in size, depending on the version of NAV you are using.

• NAV 2009 supports up to 1 GB
• NAV 2013, 2015, 2016 support up to 4 GB

Dynamics NAV 2016 has been released with a 64-bit Windows client version, so assuming it supports a 64-bit Dynamics NAV report viewer, it might not have this issue any longer, but also as long as your computer has enough RAM.
Why this problem happens in the first place is actually related to NAV only using one dataset to pass all data into the Dynamcis NAV report and joins all columns from all dataitems into this gigantic flat data structure, which contains way too much repetitive data.
So if you have an example of a simple report, which prints a list of customers and their respective sales documents, showing company logo, you could end up with a dataset similar to the below.

• Customer (No., Name)
  • Sales Document (Document No., Posting Date)

This creates a lot of repetitive data. First, the customer name is probably only printed once (not next to every document) and the company logo is only needed at the top of a page as well as captions ought to be only used once. So when you lots of records and lots of levels of data items, the dataset becomes massive in size.

There are a few solutions to solve this issue: narrowing down the data on a report (by applying more filters), optimizing pictures, captions and compressing data.

Picture Optimization

There’s more than one way to optimize memory usage on pictures (logos, item pictures, etc.).

• Upload a smaller file. When the file BLOBL is smaller in the database, it will not be as big in the dataset and will make a significant impact, when it is being duplicated hundreds of times.
• Make the picture static (embedded in the report). You can drag and drop the logo onto the Dynamcis NAV report, uploading it onto the definition of the report. The con of this is that the customer will not be able to change it without having to modify the whole report. The pro is that the picture will not appear on the dataset at all.

• Load picture off a file or URL. When you set and image Source property to External, you can specify a file path or URL in its Value property. This can also be controlled dynamically – the file path or URL can be fetched from a field in the database and passed into report.

Caption Optimization

Captions are mostly static and repetitive, so ideally we only want them once in the dataset. This can be achieved by using labels, include caption checkbox (on the dataset designer), embedded text. In this case none of the captions will appear on the dataset. But there’s an important thing to note. This only works on reports with static language handling, i.e., the report does support multilingual captions, but only as long as you do not change the language of the report during run-time (using CurrReport.LANGUAGE global variable). If it is the case and you need to dynamically change the language (e.g., printing a bunch of invoices for clients in different countries in their respective language), then you have to use text constants and table field captions (using Table.FIELDCAPTION(“Field Name”) command).

Dataset Compression

Ideally the dataset from an earlier example should look like the below (where “<>” is blank data).

The customer name is printed only once, the logo appears only once and the captions are not repeated on every row. There is a way to achieve this by compressing the dataset using a simple design pattern. I have come up with this solution for a customer whose report has its language changed dynamically (so no labels), there hadn’t been any pictures on it and the data could not be narrowed down any further, so all the previous solutions could not be applied.
To use this method, create a new function Compress, which accepts a Variant and a Boolean parameter, and outputs a string. This function will work as a gate, controlling when a field or variable should be exported and when it should be bypassed.

Create a global Boolean variable CustomerExported to control, whether the Customer data has been exported yet, or not.

Change the source expressions of columns in the dataset designer as such, so they are enclosed by the Compress function. Thus, when CustomerExported will be true, the Compress function will produce a blank string and when it is false, it will export the actual value.

• Table Customer
  • No.
  • Compress(Name, CustomerExported)
  • Compress(Email, CustomerExported)
  • Compress(“Currency Code“, CustomerExported)
  • Compress(FieldCaption(Name), CustomerExported)
  • Compress(FieldCaption(Email), CustomerExported)
  • Table Sales Invoice
    • Document No.
    • Posting Date

During Dynamics NAV report run-time NAV triggers each source expression as many times as there are records, so the gate function should remove all the unnecessary data.
Additionally to the CustomerExported variable, we need a variable FirstSI (Boolean) to tell us whether we have passed the first document record, because the customer data needs to be exported together with the first document. This code needs to be appended to the dataitem triggers.

• Customer – OnAfterGetRecord() – The main dataitem loop

CustomerExported := FALSE;

• Sales Invoice – OnPreDataItem() – Subdataitem
  FirstSI := TRUE;
• Sales Invoice – OnAfterGetRecord() – Subdataitem loop

IF FirstSI THEN

FirstSI := FALSE

ELSE

CustomerExported := TRUE;

This design pattern can be applied to any number of data items and columns. Two things to note. Never compress the columns your data is being grouped on (i.e., primary key fields) and this will override the formatting of data. The Compress function can be enhanced by adding a format parameter to be passed into the FORMAT function to allow for custom format handling.

• Pros
  • Significantly smaller dataset
  • Better performance
  • Less memory usage
• Cons
  • More development work
  • Code is harder to read
  • Partial loss of control over formatting

What kind of solutions do you apply to solve the System.OutOfMemoryException issue?

By Igne Butene, Simplanova Project Manager

If you need help with Microsoft Dynamics NAV report optimization, Microsoft Dynamics NAV development or upgrade, our team of professionals are able to provide professional solutions in these areas. Contact Simplanova to discuss these and other matters and to find out how Simplanova can make your company’s service a competitive advantage.