Sort Jet Reports
Overview
By default, results returned by a Jet function are sorted in ascending order based on the field values returned.
However, it is an easy task to override the default and sort in ascending descending order based on any field in the table you are using.
Terminology
First, it's important to understand the difference between the terms FilterField and Filter
The FilterField is that field in the database which you want to use to limit the data returned from your data. The Filter is the limiting value(s) you want applied to that field.
When using the Jet Function Wizard (Jfx), this is where they are:
In a cell, they appear like this:
Basic Sorting
-
Ascending or Descending Sort Order
If you want to sort ascending, put a plus sign ( + ) followed by the field name in the FilterField parameters.
To sort descending, put a minus sign ( - ) followed by the field name in the FilterField parameters.
The following NL function sorts by CompanyName while filtering for CompanyName starting with B
=NL("Table","Customers",," + CompanyName","B*")The following NL function presents that same information, but in DESCENDING order...
=NL("Table","Customers",," - CompanyName","B*")Try it yourself: Dynamics NAV Example Dynamics GP Example
-
Primary and Secondary Sorting
The following NL function sorts the values of the Invoice ProductID field by the values of the CustomerID field in ascending order, then by the values of the Quantity field in descending order.
=NL("Table","Invoices","ProductID," + CustomerID","*"," + Quantity","*")The following NL function presents that same information, except Quantity is listed in DESCENDING order...
=NL("Table","Invoices","ProductID," + CustomerID","*"," - Quantity","*")
Sorting by the Sum of a Field
-
Using Dynamics NAV
Report designers often find themselves needing to create a report that is sorted, based on the sum or count of some field in a Dynamics NAV table.
This "sort by sum" technique is an advanced capability - and, once you understand the process, is one that the Jet Excel add-in can easily perform for you.
-
Using SQL-Based Data Sources
When working with a data source other than Dynamics NAV (for example, a SQL database), sorting by the Sum of a field is even simpler.
Using Dynamics NAV Keys to Optimize Sorting
-
The Jet Excel add-in can sort on any field but if you are sorting in ascending order, you can easily improve the performance of the report by adding a Dynamics NAV key to the table that you are getting data from.
To maximize performance, the key should have all of the fields you are sorting on in the same order as they appear in your NL function. If you do not know how to add a key in NAV, contact your NAV Solutions Center for assistance.
If you are returning a field with the NL command, this field also needs to be included in the key to optimize speed.
Jet add-in Sorting vs. NAV Sorting
By default, the Jet Excel add-in will allow Dynamics NAV to perform all sorting. This is generally preferred because NAV uses keys to sort and is very fast. If a key that matches the filters exactly is not found, however, the Jet Excel add-in will sort the resulting data list internally.
The following examples illustrate the fields needed in an NAV key to guarantee that Jet results are sorted the same as NAV.
This NL sorts by State , then by Customer No.
=NL("Rows","Customer","No.","+State","*","Sales ($)","<>0")
The key that will sort correctly needs to start as follows.
State , No. , ...
This can be followed by other fields. Since No. is the primary key, it is included automatically at the end of the key so a key of State would suffice.