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.
Here is a step-by-step example of the technique using customer numbers and the sum of the Amount field in NAV's Cust. Ledger Entry table for each customer:
Example #1 - NL(Sum)
Step 1 - Create your function to retrieve the customer numbers (the No. field), and place it in cell E4
Step 2 - Create your function to add up the Amount for each customer and place this in cell F4
If the report were run at this point, we would get our customers and their associated amounts:
(but not sorted by amount, of course)
Step 3 - Let's copy the function in cell F4 to cell G4
(note that we'll hide that column when the report runs)
Step 4 - Now, change the function in cell G4 by replacing the reference to cell E4 with the function NF(,"No.").
When the report is run, this will retrieve the No. field from the current Customer record. The function will look like this:
(note that it currently returns #VALUE! ... that's OK)
Step 5 - With that function selected, click the Quote button on the Jet ribbon.
[note that this places the entire NL(Sum) function within quotation marks]
Step 6 - Add either a plus sign [+] or minus sign [-] (depending on whether you want your sort to be smallest-to-largest or largest-to-smallest) to the beginning of the function (directly before the second = sign)
(in this case, we've added a minus sign so that the largest sum will appear at the top of our report)
Step 7 - Add a filter to your NL(Rows) function to reference the function that you created in cell G4
When the report is run, you have a list of customers which is sorted (largest to smallest) by the sum of the customer ledger entry amounts.
This same technique can also be used the the NL(Count) function instead of the NL(Sum).
Example #2 - NL(Count) with cell references
Let's look at an NL(Count) example that also includes extra cell references.
Again, we start with a basic report that shows our customer numbers and the count of the number of transactions.
Note that, this time, there is an additional filter (Business Group Code) that references another cell.
The steps 3 through 7 are the same for the NL(Count) as they were for the NL(Sum) we looked at above.
Next, we need to make sure that the reference to $C$3 is recognized as a cell reference and not just text.
This requires the use of some extra quote marks and the & symbol around the cell reference.
That looks like this:
="-=NL(""Count"",""Cust. Ledger Entry"",,""Business Group Code"","""&$C$3&""",""Customer No."",NF(,""No.""))"
which allows for the word "Corporate" from cell C3 to be included in the filter of the NL(Count) function.