Sort Reporting Intelligence Reports

Overview

By default, results returned by a Reporting Intelligence 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 Reporting Intelligence Function Wizard (Jfx), this is where they are:

In a cell, they appear like this:

Basic Sorting

1. 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*")

2. 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

1. 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.

   Sort By Sum For Data Sources Other Than NAV

Related Articles

• Help Topics
• NL Function Reference
• Sort by Sum in Data Sources other than NAV