Hi all,
I am trying to filter a date range from the Options page. I have looked and found the "@@" but am unsure how to use it as none of the examples use date or options.
I have attached the report, as you can see D11 is my NL function and it is referencing the options tab date rage. I understand the error is because some of the Posting date/order date/document dates are blank which gives me the "no empty filter not allowed" I just don't know how to fix it
Date
Votes
7 comments
-
Jet Reports Historic Posts Hello nrhodes,
You can use "@@" in front of any cell reference that has the potential to be blank. I the case of your report, you could use:
=NL("Rows","Purchase Header",,"Posting Date","@@"&Options!F3)
BUT…. Since you are filtering on a date range, I wouldn't recommend this. You see, the point of using the "@@" is to allow blanks and special characters to pass through as a filter. If you put the @@ in front of a date range, it will be sent into the database to filter as the text of the date range - instead of the actual date range. I think your best option would be to use Excel's IF() statements to handle blanks. I would put the IF() statement up at the top of the report page to see if the cell is blank. Then you can handle it however you like - like sending it a blank.
Here is a link to the KB article from Jet: http://kb.jetreports.com/article/AA-00540
Does this help? -
Jet Reports Historic Posts Thank you for the reply The Kloser,
Sorry for the late reply. Yes the blanks make sense which explains my problem since Jet cannot filter it if the field is blank. I will look into the article that you linked since I am having the same problem with Item No as well (since if there is no item listed it cannot filter or reference it). -
Jet Reports Historic Posts I think the issue with the items might be related to the function in P11. I assume that you are trying to list out the purch Lines for the purch headers you are listing in D11. The problem is that you are not limiting/filtering the purch lines in any way. Also, you are pulling Purch Headers (which are unposted) and then pulling in Purch Inv Lines (which ARE posted). Are you sure you want to pull from the Purch Inv Line table? If you meant to pull the associated unposted purch lines, I would update your function in P11 to look like this:
=NL("Rows","Purchase Line",,"Document No.","@@"&E11)
It might also be good to limit the information a bit further by Document Type and maybe Type but that all depends on what you really want to see in the report. -
Jet Reports Historic Posts edit: Nevermind what I said I do not need to search by Item on this report, that was a previous one
-
Jet Reports Historic Posts I just tried entering the date filter as you originally typed with the =NL("Rows","Purchase Header",,"Posting Date","@@"&Options!F3) and I get the "Invalid Filter "@@7/1/2014..7/1/2014" error. I have done date filters all the time so I am not sure why it just won't show Invoices (E11) with only dates from H11 within the date range. If there are none for that reason, shouldn't it just be blank instead of keep giving me this empty filter error?
edit: I started the report from scratch and I did not use the flow filter date filter before for the posting date, I just put the filter option on the NF, I haven't go the blank error yet but am not done but hopefully this works
double edit: It seems the problem is whenever I try to add a filter to the NF function referencing the Options tab. I tried adding Status of PO to it and received the blank filter as well which doesn't make sense since I told it to only show Released POs. I think the problem is that its reading my Options tab cell as "empty" but I am doing it normally
**SOLVED IT! I'm dumb :P I was having to hit "enter" when selecitng my options filter (instead of double clicking) so it was referencing down a row (E9 instead of E8) gosh that was alot of stress over nothing -
Jet Reports Historic Posts Okay nevermind (again) I still need help.
So right now I have the "@@" in all of my date filters except posting date so I can filter by posting date alright. However this means two problems
1) If I try to filter by another date range, I get the "@@7/1/2014..7/31/2014" is not a valid filter. This makes sense.
2) However if I remove the "@@" and only filter by those date ranges I then go back to "Empty Filter Not allowed". This makes no sense to me since I am just doing a normal date range filter and if all my other date ranges have "*" in them it should include everything
Also when I tried to add "company" and chose the correct Options cell it kept giving me "7/1/2014 is not a company" like it was referencing a different cell but I quadrupled check it was the right one.
Can the report just not be made and filtered the way it was requested? I hate telling people "No" when they request things -
Jet Reports Historic Posts Hi,
after having a short look at your Report, i would Change 2 or 3 things…
- if you want to filter "*" for a date-range, try to change it into "..12/13/2999". Does this make any difference?
- in your Report you refer to the "option"-sheet (like "Expected Receipt Date";"@@"&Options!F5" ). It would be better to Change it into ""Expected Receipt Date";"@@"&Options!$F$5"
But this is still not good for report-Performance, so i would get all your options via "=NP("Eval")" to the top of your report-sheet and refere to these cells in your rows…
maybe this will help a bit…
Please sign in to leave a comment.