I have created a simple report with a calculation field within it but i am having a problem, I dont want to see unreserved quantities that are showing 0 (ZERO) on the report can anyone please tell me how do I create a filter in jet that will take 0 (zero) unreserved fields off the report?
Thanks
Date
Votes
15 comments
-
Jet Reports Historic Posts Hi,
There are a couple ways to do this. You can conditionally hide rows by putting Hide+? in cell B1 and then put an IF statement in B9 which returns "Hide" when I9 is 0. Then when you run the report, Jet will hide those rows.
The other way to do it is to put a calculated filter in your replicator in D9. It would look something like this:=NL("Rows","Sales Line",,"=NF(,""Outstanding Quantity"")-NF(,""Reserved Quantity"")","<>0")
Does one of these methods work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi
This works perfectly THANK YOU VERY MUCH for all your time and help, as you probably can tell im new to jet reporting.
Regards
Andy -
Jet Reports Historic Posts Thanks. I was also facing that sort of issue. You have provided solution already!
-
Jet Reports Historic Posts Hughes,
Should the NF portion of this code still work in JE2013 Update 1? When I attempt to use it in my formula I am getting an invalid field error. Here is the formula I'm using:=NL("Rows","AR_CUST",,"LOY_CARD_NO","<>""","=NF(,""TOT_LOY_PTS_ADJ"")+NF(,""TOT_LOY_PTS_EARND"")-NF(,""TOT_LOY_PTS_RDM"")-NF(,""LOY_PTS_BAL"")","<>0")
I should mention that I'm working with a universal connector, though I'm not sure that makes a difference…
Thanks in advance! -
Jet Reports Historic Posts Hi Brian,
Yes, the NF() syntax works in Jet 2013 (and 2015). The issue you are running into is that you are using a Universal data source. This syntax only works for NAV data sources. Since you are using a Universal data source, you will need to use SQL= to complete your calculations. For more information on using SQL=, please see the Jet Reports Knowledgebase. Here are some links that might help:
SQL= Reference
http://kb.jetreports.com/article/AA-00567
Using SQL=
http://kb.jetreports.com/article/AA-00542 -
Jet Reports Historic Posts Excellent! I'm not used to working with the universal connector and it seems it has its own learning curve so I appreciate you pointing me in the right direction.
Thanks Sarah! -
Jet Reports Historic Posts Hi,
Does the NF Syntax work in 2012? If not, is there any similar functionality?
Thanks,
Teresa -
Jet Reports Historic Posts It works in past versions all the way back to no longer supported versions from years ago (like V7).
-
Jet Reports Historic Posts In which case, can anyone help me with what's wrong with the formula below? The wordy exmplanation is that on our purchase lines, we always have an expected receipt date. Sometimes, we have a confirmed date, and when we do it supercedes the expected receipt date. In the formula below, I'm trying to get the purchase lines expected between a date range.
=NL("Rows","Purchase Line",,"=IF(NF(,""Confirmed Date"")="""",NF(,""Expected Receipt Date""),NF(,""Confirmed Date""))",NP("DateFilter",Options!$F$5,Options!$F$6),"Location Code","STORES","Outstanding Quantity",">0","Job/Contract No.",Options!$F$7,"Phase No.",Options!$F$8)
I don't get any errors, just no data returned.
Thanks! -
Jet Reports Historic Posts Isn't it enough to let the calculation result in the NAME of the field instead of the CONTENTS of the field?
Will this work? I didn't try it myself…=NL("Rows","Purchase Line",,"=IF(NF(,""Confirmed Date"")="""",""Expected Receipt Date"",""Confirmed Date"")",NP("DateFilter",Options!$F$5,Options!$F$6),"Location Code","STORES","Outstanding Quantity",">0","Job/Contract No.",Options!$F$7,"Phase No.",Options!$F$8)HTH
rmw -
Jet Reports Historic Posts Isn't it enough to let the calculation result in the NAME of the field instead of the CONTENTS of the field?
Will this work? I didn't try it myself…=NL("Rows","Purchase Line",,"=IF(NF(,""Confirmed Date"")="""",""Expected Receipt Date"",""Confirmed Date"")",NP("DateFilter",Options!$F$5,Options!$F$6),"Location Code","STORES","Outstanding Quantity",">0","Job/Contract No.",Options!$F$7,"Phase No.",Options!$F$8)HTH
rmw
I doesn't seem to work either - I'm not sure why as it just doesn't return anything, making it hard to debug. -
Jet Reports Historic Posts =NL("Rows","Purchase Line",,"=IF(NF(,""Confirmed Date"")="""",""Expected Receipt Date"",""Confirmed Date"")",NP("DateFilter",Options!$F$5,Options!$F$6), …
Theresa, I've isolated the problem piece of code.
CORRECT ANSWER (untested):
"=IF(NF(,""Confirmed Date"")="""",nf(,""Expected Receipt Date""), nf(,""Confirmed Date""))"
WHY?
verbalize what you want:
For each record IF THE FIELD confirmed date is blank then return THE FIELD expected receipt date ELSE return THE FIELD Confirmed Date.
how does your current code read?
For each record if THE FIELD confirmed date is blank then return THE TEXT Expected Receipt Date ELSE return THE TEXT Confirmed Date.
At execution you're comparing THE TEXT Expected Receipt Date against your Date Filter (ex. "1/1/2009..12/31/2009")
WHY?
As previous posters have outlined the function NF(,""Field Name"") returns THE FIELD for the record.
In your True and False clause, ""Field Name"" just returns THE TEXT Field Name. As opposed to the record. -
Jet Reports Historic Posts "=IF(NF(,""Confirmed Date"")="""",nf(,""Expected Receipt Date""), nf(,""Confirmed Date""))"
This is what i started trying but it doesn't work for me. There must be something wrong with either my setup or my data, as it sounds like that's the way to do it in theory.
Thanks for your assistance. -
Jet Reports Historic Posts What error are you getting?
-
Jet Reports Historic Posts I ended up contacting support. I was missing a "Date&" before my date filter.
=NL("Rows","Purchase Line",,"=IF(NF(,""Confirmed Date"")="""",NF(,""Expected Receipt Date""),NF(,""Confirmed Date""))","Date&"&NP("DateFilter",Options!$F$5,Options!$F$6),"Location Code","STORES","Outstanding Quantity",">0","Job/Contract No.",Options!$F$7,"Phase No.",Options!$F$8)
You would also need to put NUMBER& there if your calcualted field produces a number. Working now!
Please sign in to leave a comment.