I had Jet Reports 7.1 and upgraded to 7.1.1 and I am still having difficulty scheduling a report as I would like.
I have daily reports that I need to schedule to run. At each run it my understanding of the scheduler that it could automatically update my date field so that the next time the report ran (the following day) it ran the report for the NEXT day, and so on.
I see on-line and in the help file that I can set this up on the advanced tab of the scheduler, but I can't get the scheduler to link to my fields.
What am I doing wrong? Is it a naming or coding on my report (that I'm not doing) that the scheduler is keying in to?
I'm SO close but feel I'm missing one little element.
I could use a bit of help.
Thanks!
Date
Votes
17 comments
-
Jet Reports Historic Posts What I have done to deal with this for daily reports is to just make the field inside the report update itself.
I have one that shows what shipped yesterday, and what orders were entered yesterday. My date field is just: =today()-1
If you need to pull data out that is for the next day, maybe what is shipping tomorrow, or what invoices are due it could be =today()+1
PURE GUESSWORK FOLLOWS :!: :?: (with bad formatting)
Using the advanced features of scheduler, I think you need to have your Options page configured something like what I have below…
A ____________ B ____________ C __________________ D
Auto+hide+HideSheet _________Title__________Value_______________ Lookup
_________ Filters
Option_____ Date Range_______ 1/1/2001..1/31/2001 _________ 10/12/2006
Option____ Salesperson Filter_______ * _________
Option____ Customer No. Filter_______ *_______ BASSPRO
Option____ Commission Amount _______0.05_________ .03
Under the "Lookup" column, the formula is: =NL("Lookup","Cust. Ledger Entry","Posting Date")
Maybe that will help… good luck. -
Jet Reports Historic Posts Thank you. Adding =Today()-1 gave me a remedy. I'm scheduling and automating reports now!
It is a little frustrating however that the Scheduler filter options are unusable as of yet…. :-( -
Jet Reports Historic Posts Hi, I had this issue too where i could not change the scheduler filter options.
You need to add named ranges to your spreadsheet to cater for all the options set.
i.e. if =Options!$C$5 refers to an Item, name this range Item, etc.
You will then see these in the filter options.
Regards
Neal -
Jet Reports Historic Posts Have you thought of using autopilot?
1) I use scheduler to auto run a report each morning at 1AM
2) It refers to an autopilot:
a) it calls the Jet report to run
b) it uses =today()-1 to state the date
c) it changes the name of the file to YYYY-DD Report.xls
d) it places the completed report on our sharepoint site
3) I arrive at work 8AM - the job is all done -
Jet Reports Historic Posts Thought I'd share how I work with dates.
When I have a date based report (nearly all of them) I do my dates like this:
B2 = nl("Eval","=today()") - Which I call 'Today'
B3 = date(year(B2),month(B2),day(B2)-1) - Which I call 'Work Date'
I keep that consistent across all my date based reports, as some reports I need the 'end of month' date:
i.e. last day of previous month =date(year(B2),month(B2),0)
i.e. last day of this month =date(year(B2),month(B2)+1,0)
My 'work date' calculations generally depend on (a) the nature of the report, and (b) when its scheduled - it it runs at 10pm doesn't require the -1 but 1am would, for example.
cheers -
Jet Reports Historic Posts It seems you have some experience with dates and the scheduler.
I have a report which uses the date-formula =Today()-1. The report runs perfectly, but at the time where the report is opened the date in the report changes to the correct date due to the dateformula (the report does not run again and update).
This is not at problem if you open the report the day it is run. But if the the report has run on June 6, it contains figures from June 5. Then if I open the report on June 7, the date in the report changes to June 6, but the figures in teh report is still from June 5th.
Can anybody help? -
Jet Reports Historic Posts Hi Martin,
You could put your date formula into an NP(Eval) formula and add +Values or +Lock to your report. Then the NP(Eval) formula will not get evaluated when you open the report and your date should remain the same as when the report was last run. The formula would look something like this:
=NP("Eval","=Today()-1")
Regards,
Hughes -
Jet Reports Historic Posts Hi
After I sent my question the other night I also got the idea about the NP(eval). Instead of putting the "Today()-1" within the NP (Eval) I put a cell-reference to the previous options-sheet inside.
The result of the cell with NP("Eval","cell-refence") was #VALUE# and therefore the total report showed #VALUE# all over due to this date-reference.
I will try to put the "Today()-1" formula directly inti the NP("Eval")-formula.
Thanks! -
Jet Reports Historic Posts Hi again
Using NP('Eval','=Today()-1') works perfectly, when I run the report manually it "holds" the correct date of yesterday when I open the report.
But when the scheduler runs the report it fails in all cells that contains NP('Eval',something) from my options-sheet.
Any ideas? -
Jet Reports Historic Posts Hi,
You probably have the syntax of NP(Eval) slightly wrong. The correct way to do it is this:
=NP("Eval","=Options$D$3")
Note the quotes around the formula to get the value off the options sheet. The WRONG way to do it that many people try is this:
=NP("Eval",Options$D$3)
You are supplying NP(Eval) with the actual value from the Options sheet here rather than with a formula to evaluate so it doesn't work right. I'm assuming if you do it the right way it will probably work correctly.
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Unfortunately I have made the NP("Eval") correct. It works perfectly when I run the report manually.
If I run the exact same report through the scheduler it fails in the date-cell. Everything run on a terminal-server and that can give problems, but we have other scheduled reports running. So why this won't run I don't understand??
Best, -
Jet Reports Historic Posts Do you have the cell reference in quotes? With NP(Eval), you always have to quote the formula so that it is a formula for Jet to evaluate, not a cell reference that Excel evaluates. So the correct formula looks like this:
=NP("Eval","=Options$D$3")
What will NOT work is something like this:
=NP("Eval",Options$D$3)
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
I am sure, that I write the NP(Eval)-formula correctly.
But isn't correct, that if the formula were wrong it wouldn't work, when I run the report manually?
This is very frustrating that I can't get it to work :cry:
Regards,
Martin -
Jet Reports Historic Posts What is actually in the value on your options sheet? Is it just a date that is typed in? Is it a formula like =NOW() or =TODAY()? Is it being set by the scheduler itself?
Regards,
Hughes -
Jet Reports Historic Posts Hi
It is the formula =TODAY()-1, because the report shows yesterdays turnover/delivered orders.
As I wrote earlier there is no problem with the report when I run it manually, but with the scheduler this formula gives #VALUE#.
Martin -
Jet Reports Historic Posts Hi again,
I have just tried to make this formula direktly in the Report-sheet:
=NP("EVAL";"=TODAY()-1"). Again it works when I run the report, but not when the scheduler runs the report.
If you have another idea how to solve this I would be happy to use it. I just need to have EVAL around the date, so the date doesn't change if I open the report x-days after the report is run.
Martin -
Jet Reports Historic Posts Hi Martin,
Hmmm, I'm not sure what the exact problem is but basically Jet are just taking your formula (=TODAY()-1) and running it through Excel's calculation engine and Excel gives us an answer back. I have seen some circumstances where for some reason Excel would not give us back a result for a formula that should be valid and particularly when the scheduler is running Excel and Excel is not visible perhaps this is happening. Have you tried just doing "=TODAY()" in your NP(Eval) and then using a separate formula (not with NP(Eval)) to subtract 1 day from the result?
Regards,
Hughes
Please sign in to leave a comment.