By default, results returned by a Jet function are sorted in ascending order based on the field values returned; however, you can override the normal sorting and sort in ascending order or descending order based on any field in the table you are using.
If you want to sort ascending, put a "+" followed by the field name in the FilterField parameters.
To sort descending, put a "-" followed by the field name in the FilterField parameters.
The filtering properties of the FilterField remain intact so you can enter a filter in the Filter parameter. If you want to sort on a field without a filter, put a "*" in the corresponding filter since blank filters are not allowed.
The following NL function sorts by CompanyName while filtering for CompanyName starting with B.
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.
Specific Database Examples for Jet Professional
With Northwind, the following NL function sorts by CompanyName while filtering for CompanyName starting with B.
The following NL function sorts Invoice ProductIDs by CustomerID then by the Quantity, in reverse order.
With Dynamics NAV, the following NL function sorts by Search Name while filtering for Search Names starting with B.
The following NL function sorts Customer Names by state (in ascending order) then by sales (in descending order) where sales cannot be 0.
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 an 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 as a result. 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.
The key that will sort correctly needs to start as follows.
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.
This section applies to Jet Reports Version 7.x and older
In some situations, allowing Jet Reports to perform the sort may result in a sort order that seems inconsistent, especially in the case of Code fields. Code fields (i.e. "No." in the "Customer" table) can contain both numbers and letters, and they sort differently depending on whether you are using a native NAV Server or an NAV SQL Server (please see the NAV Help C/Side Reference Guide for details). On the other hand, Jet Reports internal sorting mechanism sorts everything textually (i.e. "10" < "100" < "20"). Items in a textual sort are compared on a character-by-character basis. If you would like to force a textual sort but are using filters that match a key in NAV, you can do so by simply switching the order of two of the filters or adding an extraneous filter at the end of the formula