Hi everyone,
I would like to make a report where I filter on a certain time, but I'm a bit stuck. I hope I get a bit of help here.
I have a time field "Loading from" and "Actual Loading From". As result I want to make a report where I can filter on the "Loading From"-field based on the "Actual Loading From"-field.
For example I want a list with information where the "Loading from" equals the "Actual Loading From" with a margin of 15 minutes. (The 15 minutes is an option that can be changed by the end users.)
So where a file has a "Loading from" with 10 o'clock, then I want to see this file when it has a "Actual Loading From" range from 9:45 till 10:15.
I now have the following formula: =NL("Rows";"Shipment";;"Actual Loading From";"=NL(""Filter"";""Shipment"";""loading from"")")
However, this formula gives me the error "Invalid Filter". And I don't have added the range yet…
An additional problem is that when I get the data from "Actual Loading From" to Excel, it automatically adds the date: 24/06/2013 10:15:00 (see also print screen).
Thanks in advance!
Bert
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi Bert,
I have no idea if this will work but I'll give it a shot. Calculated filters only work in a NAV data source, so hopefully we're talking about NAV here and not universal or cube or whatever. Also, it's going to be really difficult to make this user settable. The reason is that in order to do math with times in Excel (to add/subtract 15 minutes), you have to know that times are stored as fractions of 24 hours by Excel. So to subtract 15 minutes, you have to first know that 15 minutes is 0.25 of an hour (15 / 60) and then you must divide 0.25 / 24 hours to get the actual number you need to subtract. Of course that will give you a number, so you need to use an Excel TEXT function to convert it back to a time for filtering purposes after doing the addition and subtraction. So your formula might look like this:=NL("Rows";"Shipment";;"Actual Loading From";"=TEXT(NF(;""loading from"")-(0.25/24);""[h]:mm"")&""..""&TEXT(NF(;""loading from"")+(0.25/24);""[h]:mm"")")
Now you mentioned something about Excel displaying the date when retrieving those fields, so they may be date time type fields that store a date and a time, in which case we might have to modify the TEXT format strings to have date and time in them (something like "DD/MM/YYYY [h]:mm" might work). Like I said to begin with, I'm really not sure this is going to work at all, but maybe with a little luck you can get it to work.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
It almost works like a charm!
My formula now looks like this:
=NL("Rows";"Shipment";;TEXT("Really Loading hour at";"hh:mm");"=TEXT(NF(;""loading hour"")-(Sheet2!$E$2);""hh:mm"")&""..""&TEXT(NF(;""loading hour"")+(Sheet2!$E$3);""hh:mm"")")
On my sheet 2 I have my report options.
Still one very strange (but big) problem!
When I change my report options my reporting result does not immediately use these changes. I have to use the Refresh button, because the Report button does not seem to work. And when I change the minutes I want to add/subtract I have to click the Refresh button twice (or more) before I get the correct result.
On my options tab (Sheet2!$E$2 for example) I have the following formula =IFERROR(NP("Eval";(C2/60)/24);"*"). Where C2 refers to the value cell that will be filled in by the end user. Even without the NP("Eval" and the IFERROR formula, the result stays the same…
Never had this issue before. Any idea how to resolve this problem?
Regards,
Bert -
Jet Reports Historic Posts Hughes,
To make it a bit more difficult, I get an extra error from C/Front on a NAV 2009 database.
It seems to work (by clicking a few times on Refresh) on a NAV 2013 database.
I've just tested it on a NAV 2009 database and I get an error about the time (see print screen). Translation = "… 9:27:29 is not a valid time".
Does it mean I have to change my formula when I want to report from a NAV 2009 database?!
Regards,
Bert -
Jet Reports Historic Posts Bert,
I really don't know about changing the formula for NAV 2009. I think you will have to test what date formats are acceptable for NAV 2009. You can open the NAV client and try to set a filter on your datetime field and see what format it accepts for the time part of the filter. Maybe it wants AM/PM or something else.
In terms of having to press report multiple times, this seems odd. I would definitely expect to press refresh anyway, since this gets fresh values rather than using any cached values, but I wouldn't think you would need to run the report multiple times. If this seems like a problem in the software, then you need to create a support ticket with Jet support.
Regards,
Hughes -
Jet Reports Historic Posts I have somewhate of the same question only involving times and dates. What I am looking for is to pull in time transactions from Date 1 4:00 AM to Date 2 3:59 AM. I am not sure how to create the date filter for this.
Currently I have =NP("DateFilter",$G$6,DATE(YEAR($G$6),MONTH($G$6),DAY($G$6+1)))&" 3:59 AM" which will give me the transactions from all of Date 1 to 3:59 AM of Date 2 (cell G6 is just Date 1).
Any help would be appreciated.
Thank you! -
Jet Reports Historic Posts Please see this post for solving the problem I posted above.
http://community.jetreports.com/viewtopic.php?f=7&t=2820
Please sign in to leave a comment.