I'd like to create a report that filters based on a set of dates calculated in a cell. I have a formula to automatically calculate the first and last day of the current month, but don't understand how to load the cell properly into the filter.
Based on a KB article and a video, it looks like I need to have an Option for the filter, and then modify the Lookup parameter with my cell reference. The documentation looks like a wildcard was loaded into the Filter value, but when I do that it simply pulls all the dates from my table in, instead of the date range specified by the cell reference in the lookup.
Date
Votes
4 comments
-
Jet Reports Historic Posts Hello Jaycen,
A couple of quick notes on Option and Lookup
1. The "Option" tag is only if you want to allow your users to change the data in that field. - if you just want the report to run based on the dates you are calculating from another field, there is no need to use the Option tag.
2. Lookup (in my opinion) is useless with dates, people tend to know which date they want to run a report for - so even if I am allowing them to change the date filters, I don't include a Lookup for that filter. (which is your cell E7)
Looking at the sample you have posted, it appears that you want the report to run based on the date filter in G3 - so consider these options below:
1. You can put the formula in G3 into cell D7. Leaving the Option tag on A7 will allow your users to override the formula you have chosen.
2. If you don't want to let your users override the date - then just put your formula from G3 into cell D7, and remove the Option tag from A7.
If you're like me, and you want to give your users a more readable date filter - using the NL("DATEFILTER" command would write your date command in a more readable format: 12/1/2015..12/31/2015 instead of 42339..42369. -
Jet Reports Historic Posts Hi, Heather.
I moved the formula into D7 and that did indeed work. I tried swapping Lookup for Datefilter, but that didn't change the formatting. I think it's due to how I'm calculating the date range: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)&".."&DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
Or maybe changing the command was only part of what I needed to do?
I don't understand what you mean by Lookup being useless with dates. Being really new to Jet, I've built all my reports using Table Builder. When you add a filter for Posting Date, it defaults to the NL Lookup function.
Honestly, I'm not even sure how to apply the NP Datefilter function in terms of setting up a filter. Playing around a bit, I broke my formula above into two separate formulae in two separate cells, and then replaced the NL Lookup with an NP Datefilter referencing those cells. I then moved the NP function into D7 and that did actually work.
It formatted the dates in a friendly manner and left the option open in the case my salespeople want to examine a different range of dates. So, maybe that's what you intended!
Thanks! -
Jet Reports Historic Posts Congratulations! It sounds as if you have the report functioning the way you want to now ;)
My apologies for the confusion with the Lookup function. Let me explain it a bit further.
Yes, you are right that when using the Table Builder - if you check the "User Defined" box, then you are telling the table builder that you want your users to be able to change that value in the report. And when you give them the option to change it, the Table Builder always wants to give them the ability to search for (or 'lookup') the value in your database. It also puts "Option" into column A beside the filter value. Your filter value is in the column that the Table Builder has labeled "Filters"
As an example - if I am writing a report for our customer service team to run on a single customer - then one of the options I want to let them change is obviously the customer account number. But we have over two thousand customers - and I can't expect them to remember all of the numbers. So instead, I give them a lookup formula (placed just as it is in your report built by the table builder) - to search and find the proper customer.
But they shouldn't need to search for a specific date - so if I'm letting them change that, I just delete the Lookup formula to the right of my filter value.
The whole thing will make more sense as you get further into using Jet formulas - and it is a fun journey! :D
Just don't be afraid to ask if you run into trouble - we're here to help each other. -
Jet Reports Historic Posts Very cool. I really appreciate the help. Thanks!
Please sign in to leave a comment.