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 G2
(note that we'll hide that column when the report runs)
Step 4 - Now, change the function in cell G2 by replacing the reference to cell E4 with the function NF(,"No.").
Currently, our NL(Sum) function references the customer number in cell E4. Since we are going to use this function inside of the function in cell E4, we cannot refer to that cell to get the customer number. We'll need to get it directly from the database. This is where the NF() function comes in.
When the report is run, the NF() function will retrieve the No. field from the current Customer record [retrieved by our NL(Rows) function]. The function will look like this:
(note that it currently returns #VALUE! ... that's OK, we're not done, yet.)
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 and doubles all the quotation marks that were already in the function]
If you don't see the Quote button on the Jet Ribbon: click Application Settings, select Jet Ribbon, and check the box next to Quote.
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 (inside the quote marks and 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 G2
If you prefer, instead of using a reference to another cell, you could instead place the entire NL(Sum) function inside your NL(Rows) function. That would look like this:
=NL("Rows","Customer","No.","-=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""))","*")
This is a little more difficult to read, but you would not have to keep the NL(Sum) function in a separate cell. Both techniques work.
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.
Because the NL(SUM) function is contained within quotation marks, Excel considers the value returned to be text (not a number).
If you want to limit the result to only those where the sum is larger or smaller than a specific amount, you can force Excel to treat it as a number by using the NUMBER& technique:
=NL("Rows","Customer","No.","-=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""))","NUMBER&>=100000")
will return only those customers whose sum is greater than 100,000.
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 6 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.
You can then, just as was done in step 7 above, add a filter to the NL(Rows) function in cell E4 by either embedding the modified NL(Count) or using a cell reference to the function in G2.