Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Sort by Sum (or count) with Dynamics NAV


Moderate

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

 
Sort by Sum using Dynamics NAV

This video provides an in-depth look at the sort-by-sum technique available for Dynamics NAV.

Example #1 - NL(Sum)

  1. Create your function to retrieve the customer numbers (the No. field) and place it in cell E4

    jrds.png
  2. Create your function to add-up the Amount for each customer, and place this in cell F4

    The Application Container is the term used to refer to the IIS website that will host the Service Instance(s). The Application Container is associated with a port during installation.

    If the report were run at this point, we wopuld get our customers and their associated totals:

    (but not sorted by the totals, of course)
  3. Let's copy the function in cell F4 to cell G2

    note that we will hide that column when the report runs
  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 the NL(Sum) function inside of the NL(Rows) function in cell E4, we cannot refer to cell E4 in order 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.
  5. 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

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


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)

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

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments