Hi All. Thanks in advance for reading. This is my first post and I am not a Jet Expert by any means, but I feel like what I'm trying to do should be very simple. This data in being pulled from NAV2009R2.
I've created a report which looks sums the Amount ($) field by Source Code in the Customer Ledger Entries. I would like to sort the subtotaled amount in ascending order. I can sort the amount by using +/- in my NL statement, but can't seem to find a spot for the total. The report generates 3 groupings. 1 for Customer No. (which is the one i want sorted) 1 for Customer with a breakout by Source Code, and lastly the details that make up the amount for each source code.
Any input would be appreciated.
Date
Votes
4 comments
-
Jet Reports Historic Posts Okay, it sounds like you want a sort by sum. There are actually quite a few examples of how to do this if you search this community site. If you want more specific help, post the formulas you are using (1) to retrieve the Customer No. and (2) to get the sum you want for each customer. You may currently be listing out entries for each customer and subtotaling them, but in order to sort we need something like an NL(Sum) formula which gets the sum of those entries that you want to sort by for each customer.
Regards,
Hughes -
Jet Reports Historic Posts Thanks for your reply.
Here are the formulas I'm trying to use. I did change up the design of the report a little, but am still not having any luck with the sorting.
My first column gets all the customers with a record in the Cust. Ledger Entry table with a Source code at meets my criteria.
=NL("Rows","Cust. Ledger Entry","Customer No.","Source Code","RENTALS|SALES|SERVICE")
The second column is the Sum of the Amounts for the customer:
=NL("Sum","Cust. Ledger Entry","Amount ($)","Customer No.",G8,"Posting Date",C$2)
Where G8 is the value returned from the first formula, and C$2 is a filter.
Any input is appreciated. Thank you! -
-
Jet Reports Historic Posts Haha, nice Sebastiaan. That post does actually outline the path to creating the sort by sum formula. In this case you can probably do this the easy way and just sort by the "Balance" or "Balance on Date" flow field on the Customer table and filter by the "Date Filter" flow filter field.
Regards,
Hughes
Please sign in to leave a comment.