Jet Reports – Using Arrays

In my experience Arrays in Jet Reports are a useful but underutilised tool, probably because of the terminology used and concepts which are not familiar to most Jet Report designers. So in this article I aim to clarify what Arrays are, the terminology surrounding them and how they can be used in Jet Reports.

What is an Array?

Basically an Array is a 'list' of things, for example a shopping list could be considered an Array;

Bread, Milk, Butter, Cornflakes, ... 

In terms of the Microsoft Dynamics NAV Cronus database it could be a list of, say, Customer No.'s;

10000, 20000, 01121212, 31505050, ... 

Combining / Comparing Array values

In Jet Reports two Arrays can be combined or compared in 3 ways to give different results. For example, if you have the following two arrays;

  • Array 1 with values of: 100, 200, 300, 400, 500
  • Array 2 with values of: 400, 500, 900, 1000, 2000

…the 3 different ways to combine them are;

Difference

This is a list of the values that are in Array 1 (primary) but not in Array 2.

Using the array values above, this will give the following result;

100, 200, 300

Note: – The order which the Array's appear in the Difference parameters will determine which is set as the primary array.

Union

This is a list of all values from both Array's, i.e. all values from Array 1 and all values from Array 2;

100, 200, 300, 400, 500, 900, 1000, 2000

Note: if a value is in both array's, for example in the arrays above the value '400' is in both arrays, then is will only be listed in the results once.

Intersect

This will list the values that appear in both Arrays, i.e. the values that are common to both Arrays, this will give the following result;

400, 500

Creating Arrays

In Jet Reports an Array is created using the NP function with the 'What' parameter set to 'Filter. So for example if you want to create a list of Customer's that have had a posted sales invoice in January 2016 then you would use the formula;

=NL("Filter","Sales Invoice Header","Bill-to Customer No.","Posting/Tax Point Date","01/01/2016..31/01/16")

The above formula returns the list of customer no.'s to the cell the formula is in. We cannot see the values directly in the cell, so we will need to use the NL function, together with NF functions (if needed).

Using the above NL formula placed in cell C3 of your workbook we can use the following NL formula to create a list of Customer records;

=NL("Rows","Customer",,"No.",$C$3)

Working example

To help explain how to use Jet reports and make use of the NL Filter function we have attached an example spreadsheet.

Leave a Reply