I am kind of new to Jet Reports so bear with me.
I am trying to create a list of inactive customers by comparing an active list to the full one, and then removing any customers with activity. However I have to be able to search both the Sales Invoice Line table and the Sales Invoice Header table so that I can properly apply filters to both. Header for Posting Date and the Lines to block zero amounts and quantity (as some orders are processed with zero's for various reasons, but it does not count as activity). However this is leaving me with values in the header entry and no values for the lines. So I want to filter out the header rows when the line filters prevent the line rows from populating. This will hopefully leave me with enough entries filtered out to not run up agaisnt the 65k row limit. I am assuming this is possible in some manner, or maybe some easier way than how I am going about to accomplish this. Any suggestions would be appreciated.
Here are the NLs, and I am using simple NF's to populate the fields, but didn't see the point in including them here.
Sales Invoice Header NL Statement
=NL("Rows=2","Sales Invoice Header",,"Posting Date",">=6/1/2008","Sell-to Customer No.","<>Z INACTIVE*")
Sales Invoice Line NL Statement
=NL("Rows","Sales Invoice Line",,"Document No.",$B4,"Amount","<>0","Quantity","<>0")
Date
Votes
3 comments
-
Jet Reports Historic Posts I have a couple things that might help you. First, you might want to try using the Item Ledger Entry table (ILE) instead of the Sales Invoice Header and Line. I am not sure if all of the fields you need to filter on are in there but it is a table that contains both item and customer info. Just remember that the Customer number is called the Source No. in the ILE and you only want to deal with the Entry Type of "Sale".
You will also probably need to use the NP(Difference) to do the subtraction for you. There are a couple knowledgebase articles you will want to read about using it (if you haven't already done so).
http://support.jetreports.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=59
http://support.jetreports.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=134
I hope this helps! -
Jet Reports Historic Posts So I switched over to the ILE and that was a good change, but the NP(Difference) function just won't work. I've tried to nest it into the NL both on my own, and using the union formula example (which is the only example available anywhere) and it refuses to find anything. I am clearly doing something wrong, but for the life of me I don't know what. Any chance you have a sample code of what the NP Diff is supposed to look like? According to how it is supposed to work it is exactly what I am after, but it just doesn't like to play nice with the kiddies.
-
Jet Reports Historic Posts Or I think I found the right syntax, just as I was despairing.
Thanks for the help, both of your suggestions worked great. :)
Please sign in to leave a comment.