I have created an AR Collections Report molded after the sample report available to everyone. I need to figure out how to filter on the Balance so that only customers with a balance greater than 150 are showing. I am fairly new to jet so I'm sure that this is probably something simple, I just don't know how to do it! Any help that anyone could offer would be greatly appreciated. Thanks!
P.S. I am using JetReports 2009 and Excel 2003 with Navision 5.0(SP1)
Date
Votes
12 comments
-
Jet Reports Historic Posts Official comment The entire report shows up blank with no data….. :?
-
Jet Reports Historic Posts I took a look at the shared found at http://community.jetreports.com/viewtopic.php?f=13&t=1018
if you change the formula in cell C19 to=NL("Rows=5","Customer",,"No.",$H$8,"Date Filter",$D$15,"Customer Posting Group",$H$11,"Global Dimension 1 Filter",$H$9,"Global Dimension 2 Filter",$H$10,"Net Change",">150")
You should get only customers with a balance greater than 150 -
Jet Reports Historic Posts Thanks! I tried it and it seems to have worked. There are still lines that are coming up with a 0$ Balance Due. Any idea how to get rid of them? -
Jet Reports Historic Posts How about
=NL("Rows=5","Customer",,"No.",$H$8,"Date Filter",$D$15,"Customer Posting Group",$H$11,"Global Dimension 1 Filter",$H$9,"Global Dimension 2 Filter",$H$10,"Net Change",">150&<>0")) -
Jet Reports Historic Posts I tried that and I'm still getting a few lines that are coming up with 0$ balances. :evil:
-
Jet Reports Historic Posts I see the problem.
Have another look at the dates that have a value of $0.
:) They are after 150 Days and you are missing your 150+ Column.
Attached is the updated report.
You already had the column…but it wasn't doing anything.
Ive also removed the <>0 part of your filters because its not required anymore. -
Jet Reports Historic Posts Thanks! I knew something didn't look right. But now I am getting people who have no Balance due and no aging. How do I get rid of them? This report is getting very frustrating! :x When I run the sample report on my database everything comes out perfectly but I can't seem to get my report to work like the sample!!
-
Jet Reports Historic Posts Could you please re-run the report and attach?
It would be easier to track through the Data
Thanks -
Jet Reports Historic Posts Sorry! Forgot to attach last time
-
Jet Reports Historic Posts Thanks for the Report.
The values are there, but they are hidden.
have a look in Column N
That column is the Current outstanding balance.
but the "Balance Due" Column doesn't count it.
You have 3 options at this point.
1. Remove the Hide in column N
2. Use a Flow filter from the Customer Table to not bring in the current documents.
3. Use conditional "Hide" to hide the rows with Values only in the "Current" Column (N)
1 would be easiest.
3 Would be next. -
Jet Reports Historic Posts Thanks for all your help!
Unfortunately, what my accounting department really wants is option 2. And of course it is the most difficult.
So, now what do I need to do and where do I need to go to learn how to do one of these flow filters? -
Jet Reports Historic Posts Flow Filters are used within the NF function.
i.e. NF($E14,"Remaining Amount (LCY)","Date Filter",$F$3)
so that it would calculate the documents Remaining Values as of the date in F3
Try the Attached.
I've made a couple of changes.
1. Ive removed "DateFilter" from your "Cust. Ledger Entry" rows
2. Ive put a NL("Filter") in C10. The Customer Ledger Rows now refer to this.
3. Ive edited the Flow Filter you had for the Salesperson Code (L15)
Please check thoroughly to make sure its accurate.
Please sign in to leave a comment.