0

Scheduling report with previous month's data

Jet Reports Historic Posts

March 05, 2012 15:41

None

Follow

OK I officially give up!

I've spent about three hours looking for an answer to this question…

I want to schedule a series of reports based on last month's figures. How do I specify that the date range I want is always the previous month regardless of the month I'm in? I've tried setting scheduler to run after close of business on the last day of the month using the posting date filter 01..t but Jet returns data going back to our go-live date with NAV!

I'm a newbie to this so whilst I'm pretty comfortable with excel and with table builder in jet, some of the more advanced features in jet are still a bit of mystery.

can anyone help?

Cheers

Matthew

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts March 07, 2012 21:33 Official comment

  Hi Matthew,
  
  I'm not sure what you mean by "fixes the value but wipes the formula". Are you trying to change it in Report mode without going back to Design mode in Jet Reports? You need to make all changes like this in Design mode. If this isn't the problem, maybe you can attach a report with an example of what you mean?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 05, 2012 17:04

  Hi Matthew,
  
  So you are just trying to construct a Jet Reports date filter that will give you the date range for the previous month, is that right? If that's the case, it might look something like this:
  
  B3: =NP("Eval","=TODAY()") C3: =DATE(YEAR(B3),MONTH(B3)-1,1) D3: =DATE(YEAR(B3),MONTH(B3),1)-1 E3: =NP("DateFilter",C3,D3)
  The result of the NP(DateFilter) function right now would be 2/1/2012..2/29/2012 and will always be the date range for last month. Is this what you're looking for?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 07, 2012 16:51

  Thanks Hughes
  
  I've tried this already but my version went like so….
  
  L1: =today() L2: =DATE(YEAR(L2),MONTH(L2)-1,1) L3: =DATE(YEAR(L2),MONTH(L2),0) L4: =NP("DateFilter",L2,L3)
  This works beautifully until I hit "Report" or "Refresh" in Jet whereupon it takes cell L4 and fixes the value but wipes the formula so that on the first of April the report will still be for Feb.
  
  How can I ensure that the =NP("DateFilter",L2,L3) remains?
  
  Matthew

• 0
  
  Jet Reports Historic Posts March 08, 2012 15:27

  Ah!I think I see what you mean!
  
  If I save it in design mode the cell still shows the formula in the function bar. But if it's saved in Report mode then the function bar just shows the value and it appears as though the formula has gone.
  
  I'm still getting the hang of how this all works!
  
  Thanks!