I thank you all for the help you've provided me directly and indirectly by posting the reports that work for you. By reverse engineering them, I've been able to create reports that are very helpful to our company.
I have another request.
We'd like a report that lists customers with columns for 30-59day past due amts, 60-89 day past due amts, 90 day and over 90.
Do any of you have a report you can post to help me, or guide me into the filter/math formulas that may help me wrap my head around this report?
THANK YOU!
Date
Votes
4 comments
-
Jet Reports Historic Posts I figured out the hardest answer on my own. I created each date range in a hidden cell and referenced it in my formula. Works great!
My formula returns all Customers with a balance, then sums any past due amts into the 30, 60, 90, 120+ columns.
What I would like to do and am still having problems with is returning only customers with a past due balance (vs a current balance like I am currently doing)
I'm not skilled in filters or cross field references yet. From what I understand, I would use a
NL("Rows","Customer") reference, then have to cross into the "Cust. Ledger Entry" to find balance due vs date due.
Can anyone help me how to build that formula?
Thanks! -
Jet Reports Historic Posts Hi Airplaneguy,
An example of such a report may be found in a Jet Reports folder that comes with the installation: C:\Program Files\JetReports\Reports\Navision\AR Aged Accounts Receivables.xls. Or here if you wish to use currencies: http://community.jetreports.com/viewtopic.php?f=13&t=39
I believe the field you are looking for is called "Balance due (LCY)". It is the defined for the Customer table and may be found on the Customer sales form (open Customer card, select Customer menu button, select Sales). Navision Help defines it as "the amount (in LCY) that is due for payment in the period shown in the left column".
Let's assume you have a cell E4 with a date in the past. To filter only those customer with a balance due on that date it could be done like this: NL("Rows","Customer",,"Date filter",NP("Datefilter",,$E$4),"Balance due (LCY)","<>0").
I used the W1 version of Navision 4 SP3 to find this information.
Please let us know if this works for you. -
Jet Reports Historic Posts Could you post a sample report under the sample reports area - I would love to crib the code of this report. Thanks
-
Jet Reports Historic Posts Hi, i've added a comment on this topic:
http://community.jetreports.com/viewtopic.php?f=7&t=209
that is good when working with periods.
Please sign in to leave a comment.