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.

Jet Reports – Using a Field Cache with the NL function can give unexpected results.

The Field Cache in Jet reports is a list of fields that are to be retrieved from a table with the NL function. If the Field Cache is not specified then all fields are returned.

An example without a Field Cache;

=NL("Rows","Item  Ledger Entry")

An example with a Field Cache;

=NL("Rows","Item  Ledger Entry",{"Item No.", “Quantity","Posting  Date"})

In the second example above the Field Cache is the list of fields contained within the {} brackets;

{"Item No.","Quantity",”Posting  Date”}

A Field Cache is mandatory for a table that has no primary key, and is also recommended for a table with a primary key since it speeds up the report and reduces the amount of data.

So what does this all mean in practise and why can it lead to unexpected results?

Consider the following item ledger entries for items in the Dynamics NAV Cronus database;

Figure 1

Entry No. Item No. Quantity Posting Date

1

1000

10

01/04/2015

2

1100

12

01/04/2015

3

1000

8

02/04/2015

4

1120

3

02/04/2015

5

1100

9

02/04/2015

6

1155

25

02/04/2015

7

1000

8

02/04/2015

8

1000

15

03/04/2015

9

1100

6

03/04/2015

If you need a Jet report to list records from the Item Ledger Entry table and have columns for ‘No.’, ‘Quantity’ and ‘Posting Date’ , you could use the following NL function ( together with NF functions for ‘No.’, ‘Quantity’ and ‘Posting Date’) to report the data;

 =NL("Rows","Item  Ledger Entry")

You will get the following results;

Figure 2

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

8

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

This is ok, however if you have a large number of records to retrieve you may choose to use a Field Cache to reduce the amount of data retrieved from the database by only choosing the fields that are required for the report.

Instinct would be to change the NL function to be as follows;

=NL("Rows","Item  Ledger Entry",{"Item No.","Quantity","Posting  Date"})

Using the function above will give the following results;

Figure 3

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

Note that a record for Item 1000, Quantity 8 and Posting Date 02/04/2015 is missing.

This is because the Field Cache is used and the NL function will only report the unique combination of the fields in the cache, in our case this is "Item No.","Quantity","Posting Date".

From Figure 2 above the following records are ‘repeated’ and the NL function will only report one of them;

Figure 4

Item Quantity Posting Date

1000

10

01/04/2015

1100

12

01/04/2015

1000

8

02/04/2015

1120

3

02/04/2015

1100

9

02/04/2015

1155

25

02/04/2015

1000

8

02/04/2015

1000

15

03/04/2015

1100

6

03/04/2015

To avoid this you can add a field (or fields) to the Field Cache that form the primary key for the Dynamics NAV table, in our case this field is ‘Entry No.’ Adding this field to the Field Cache will report all records, this is because the Entry No. is unique for each record.

Our Jet NL function will then be;

=NL("Rows","Item Ledger  Entry",{"Item No.","Quantity","Posting  Date","Entry No."})

So how do I know which field(s) forms the primary key for a Dynamics NAV table?

This information can be found from the table information in Dynamics NAV.

In RTC Client use the ‘About This Page’ option to list the table fields information;

Image-001

 

Image-002

The field(s) at the top of the list with the suffix (PK) are those that form the Primary Key, in the example above this is ‘Entry No. (1) (PK)’

In Classic Client use the Sort option to list the table keys;

Image-003

The field(s) shown on the first line are those that form the primary key. In the example above this is ‘Entry No.’.