Hi All
i am new to Jet Reports and came across the "Empty filter not allowed" error.
I have tried a few of the solutions on the forum but cannot seem to be able to find a solution.
If anyone can take a look at the attached sheet and let me know where the formulas are wrong, i would appreciate.
There are two tabs, same formula on both. On one, everything works but not on the other.
Thanks all.
Dynamics NAV 2009 R2
Jet Essentials 2012
Date
Votes
8 comments
-
Jet Reports Historic Posts Hi,
Well your formula in I13 is definitely wrong. You have this:=nl("rows=2","G/L Entry",$G$5,"Division Code",$G$6,"Location Code",$G$7,"Project Code",$G$8,"Employee Code",$G$9,"G/L Account No.",E13,"Posting Date",nl("datefilter",$G$3,$G$4))
The 3rd function argument (the Field argument) should probably be empty in this case but you have it set to G5 which is your G/L Account filter, which is wrong. It should probably be the same as the one on the sheet that does work, which is this:=nl("rows=2","G/L Entry",,"Division Code",$G$6,"Location Code",$G$7,"Project Code",$G$8,"Employee Code",$G$9,"G/L Account No.",E13,"Posting Date",nl("datefilter",$G$3,$G$4))
Your function in P13 probably needs an @@ in front of the cell reference like this:=nl("Filter",IF(N13="Credit Memo","Purch. Cr. Memo Hdr.","Purch. Inv. Header"),"No.","posting date",nl("datefilter",$G$3,$G$4),"no.","@@"&O13)
The same thing should be done for the function in Q13:=nl("rows",IF(N13="Credit memo","Purch. Cr. Memo Line","Purch. Inv. Line"),"Type","G/L Account","No.","@@"&E13,"7 Location Code",$G$7,"2 Employee Code",$G$9,"Document No.","@@"&P13)
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Thank you Hughes for your reply,
I did make those changes but i still get the error. And that is where i am stuck.
I have rechecked to make sure the sheet that worked, the formulas are exactly the same as the one that does not work.
In cell F13, you see in the one that works, the first account number in that series is being pulled in, in the second Sheet, that is not happening. Based on my filter criteria, there are no values to pull. How do i prevent it from returning an error when there is nothing to pull?
In the formula below, is there an easier/shorter way to achieve the end result?=IF(nl("rows=6","G/l Entry","G/L Account No.","G/L Account No.",$G$5,"Division Code",$G$6,"Location Code",$G$7,"Project Code",$G$8,"Employee Code",$G$9,"Posting Date",nl("datefilter",$G$3,$G$4),"Amount","<>0")="",0,nl("rows=6","G/l Entry","G/L Account No.","G/L Account No.",$G$5,"Division Code",$G$6,"Location Code",$G$7,"Project Code",$G$8,"Employee Code",$G$9,"Posting Date",nl("datefilter",$G$3,$G$4),"Amount","<>0")) -
Jet Reports Historic Posts Hi,
Your IF function won't work. If it DOES return values, it won't expand if it's inside an IF statement. Replicators will only expand if they are the first thing in the cell. Why would you want to return 0 instead of blank? I'm still not sure what is going wrong for you, but I can look at it. Can you attach the current version of your report again?
Regards,
Hughes -
Jet Reports Historic Posts I did find out the IF statement won't replicate, The zero was not necessary, being new to these reports, i was just trying something to see if it would work. :)
i have included the original workbook with ALL tabs as i currently have it.
If you look at the IMED fees tab as an example, since there is no data with the filter ranges selected, i am still getting "Empty Filter Not Allowed" in cell Q13
And thanks again for your assistance. -
Jet Reports Historic Posts So it's like I said in my first reply to you, the formula in Q13 needs the "@@"& in front of all the filters that are cell references that could be blank. You didn't have the "@@"& in front of your cell reference to E13 like I recommended, so when the value in E13 was blank, this formula in Q13 was giving an error. Please see my first reply on this thread for the correct formula. If you do this correctly, you also don't need all the IF(ISERROR) formulas around your NF functions that are referencing Q13. I will make these changes on the Imed Fees sheet and attach the report here.
Regards,
Hughes -
Jet Reports Historic Posts Thank you again for your help. I had made the changes in the sample worksheet but not on the latest one i sent to you, my bad.
As for the updated sheet, when i ran it, i got the error below "invalid filter".
There was a solution on this forum to remove the "@@" is that accurate?
Thank you again -
Jet Reports Historic Posts Oh I see; it's because you're using NL(Filter) there. It will be better (and faster) to change that to a Link=. Then we don't have to worry about blanks or using @@. Try the attached.
Regards,
Hughes -
Jet Reports Historic Posts :D With all the trial an error, i can now say i have learnt something…
Thank you for your help, Your solution worked and it was great!!!
Please sign in to leave a comment.