Overview
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.
How-To Video
This video provides an in-depth look at the sort-by-sum technique available for Dynamics NAV.
Example #1 - NL(Sum)
-
Create your function to retrieve the customer numbers (the No. field) and place it in cell E4
-
Create your function to add-up the Amount for each customer, and place this in cell F4
If the report were to be run at this point, we would get our customers and their associated totals:
(but not sorted by the totals, of course) -
Let's copy the function in cell F4 to cell G2
note that we will hide that column when the report runs -
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 the NL(Sum) function inside of the NL(Rows) function in cell E4, we cannot refer to cell E4 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 the NL(Rows) function].
Our updated NL(Sum) function will look like this:
note that it currently returns #VALUE! That's OK, we're not done, yet. -
With that function selected, click the Quote button on the Jet ribbon.
(if you don't see the Quote button on the Jet ribbon: go to the Jet App Settings, select Jet Ribbon, and check the box next to Quote)This placed the entire NL(Sum) function within quotation marks and doubled all the quotation marks that were already in the function
-
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 -
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 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.
That's it!
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:
For example:
The function:
=NL("Rows","Customer","No.",$G$2,"NUMBER&>=100000")
will return only those customers whose sum is greater than 100,000.
Example #2 - NL(Count) with cell references
This same technique can also be used with the NL(Count) function instead of the NL(Sum)
-
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. This will require an extension to our technique. -
Next Steps
The next steps are the same as steps 3 through 6 listed above for the NL(Sum) example.
Next, we need to make sure that the reference to cell $C$3 is recognized as a cell reference, and not just text.
This requires the use of some extra quotation marks and the & symbol around that cell reference.
That would look 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 cell G2.
Comments