0

Help with a formula as a filter

Jet Reports Historic Posts

June 15, 2012 14:05

None

Follow

Hi,

I am new to JET so please go easy!

My aim is to auto fill a filter value in the Options tab of my report, so that the report can be scheduled to run automatically and always filter by dates prior to the date on which the report is run. I also want it to ignore blank values.

This is what I have come up with:

Cell E5 contains: =NP("Eval",TODAY())
Cell E6 contains: <
Cell E7 contains: &><''

Value cell contains: =CONCATENATE(E6,TEXT(E5,"dd/mm/yyyy"),E7)

I know that the filter expression <15/06/2012&><'' works when entered manually. I also know that my formula creates the correct filter expression. However Jet doesn't like it when I try and run the report having let the formula create the expression for me, it says the filter is invalid.

So, my question is, where am I going wrong?!

My next question is, I have scheduled this and other reports to run automatically, and to email the results to various people. Will the report run if I do not have Excel open (I assume that I must at least have my computer turned on)?

Thanks in advance,

Dan

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts June 18, 2012 06:38

  Hi Dan,
  
  Welcome to the Jet community. Let me see if I can help you with your questions.
  
  Instead of trying to ignore blank values, I would try to define a date range starting at a specific date. This could e.g. be the date that you started working with your accounting system or the start of a fiscal year. That will leave the blank dates out. Besides that Jet has a very convenient function to create a date filter for you.
  
  So, how about:
  E4 1-1-2005
  E5 =NP("Eval"",TODAY())
  E6 =NP("Datefilter",E4,E5)
  
  Does it make sense? Does this work for you?
  
  The Jet Scheduler uses Excel in the background, so you don't have to leave Excel open. And yes, the computer has to be on. More about the Jet Scheduler is to be found hee: http://www.jetreports.com/customer-portal/.

• 0
  
  Jet Reports Historic Posts June 18, 2012 06:48

  Shouldn't NP("Eval",TODAY()) be quoted like NP("Eval","=TODAY()") for it to be effective? Or did this change?

• 0
  
  Jet Reports Historic Posts June 18, 2012 09:33

  Hi Sebastiaan,
  
  Thanks for the update! Good reading! You are absolutely right :) My focus was on the Datefilter function only - my bad.

• 0
  
  Jet Reports Historic Posts June 19, 2012 13:22

  Thanks very much guys, your guidance has resulted in a report that works beautifully.