HI All,
I have inherited a jet report which I am trying to modify and for the most part it is doing everything that I want with one exception.
It is a pretty basic report that is pulling an inventory listing from the StockKeepingUnit table (per our location) but I want it to show only those items that have positive "Qty.on Sales Order" and "Qty. On Kit Sales Lines" against them.
The filter currently inplace in the document is:=NL("filter","Stockkeeping Unit","Item No.","Item No.",E12,"Location Code",$G$6,"Qty. on Sales Order",">0","Qty. on Kit Sales Lines",">0")
This is understandably returning those items that have Sales orders with positive quantity and Kit sales with positive quantity… I need the report to return rows that either have >0.
What obvious bit of code am I missing ?
Thanks for your help.
Aggerz.
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment Hi Aggerz,
Go ahead and try the modified file. It should work now, but I'm not sure, since I can't test it with my db here.
A few suggestions to improve your skill:
1. make it a habit to use the JFX (Jet Function Wizard), it's truly a wonderful tool under your disposal. Using JFX will save you from the mistake of misplacing the "field" and the "filter". Look at cell E12 & E13
2. when you use JFX, make sure to choose the field / table / filterfield from the dropdown menu, it will save you the trouble of mistyping the name.
3. don't just copy paste from this forum :lol: , you need to put the cell reference correctly in your report, otherwise it won't work.
Go ahead and compare your original report file and the one I modified first. You'll be able to learn something from it.
Cheers, ;)
Andy -
Jet Reports Historic Posts Hi Aggerz,
I believe you can't do it in a single line of code like that. What you need to do is to break-up the filter & then join them togehter using NP (Union).
Let's say, you have the following code in B3:=NL("filter","Stockkeeping Unit","Item No.","Item No.",E12,"Location Code",$G$6,"Qty. on Sales Order",">0")
Put another one in B4:=NL("filter","Stockkeeping Unit","Item No.","Item No.",E12,"Location Code",$G$6,"Qty. on Kit Sales Lines",">0")
And then, combine them together using NP (Union), Put it in B5:=NL("Rows",NP("Union",$B3,$B4))
It should work.
Andy -
Jet Reports Historic Posts Hi Andy,
I see what you're getting and it makes sense. I've integrated your suggestion as best as I can but It is now producing a new issue. "Empty Filter not allowed" I'm sure I've probably put something in the wrong spot but I've tried a few combinations and no-go.
I've attached the report If you care to have a look Thanks for your help its much appreciated.
Aggerz. -
Jet Reports Historic Posts Thanks Andy,
With your file and few other small adjustments I got it to work.
Thanks for your help and advice.
I have compared the 2 and appreciate/understood your notes. (I'm not quiet that silly to copy/paste straight from forum without updating cell references but must have I missed it in my haste and various combinations I was trying) I had not even heard of Jet reports 2 months ago so as you could imagine it has been a steep learning curve.
Thanks again.
Aggerz. -
Jet Reports Historic Posts Hey, welcome to the community ! :P
Glad to hear that you got your report working. If you stick around in this forum, you'll see your skill improve dramatically in no time.
And please don't mind my comment & suggestions. I've known it all too well how devastating a simple mistake like misplacing the field / filter, typo, & misplaced cell reference to a report. And end up spending hours and hours trying different combination & function, just to realize that the cause of all this problem is a simple mistake in our original formula :o :roll:
Cheers & again welcome,
Andy
Please sign in to leave a comment.