Jet Reports 101 - Evaluating Formulas NP(Eval)

A ‘volatile function’ in Excel are functions in which the value cannot be assumed to be the same from one moment to the next. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that has to recalculate. If too much reliance is put on volatile functions, it can make recalculation times slow in Excel alone - when you couple that with Jet reporting it can compound the issue further.

The following Excel functions are volatile:

NOW
TODAY
RANDBETWEEN
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)
SUMIF (depending on its arguments)

This is where NP(Eval) comes to the rescue as it is designed for use when working with volatile Excel functions.

Oftentimes, you’ll find that you have reports reliant on date ranges which will no doubt need to be dynamic as to keep the report rolling over monthly, for example like a monthly sales forecast. The easiest way to do this would be to specify a ‘date from’ and ‘date to’ for the report to use as a filter.

In excel we can get the date for the first day of the current month like this:

=DATE(YEAR(NOW()),MONTH(NOW()),1)

and the last date of the current month like this:

=DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1

We can use these two formulas within Jet’s NP(Datefilter) function:

=NP("Datefilter",DATE(YEAR(NOW()),MONTH(NOW()),1),DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)

You’ll notice in the above formula that the volatile function NOW() is used many times which will cause Excel to recalculate as Jet replicates (when using NL(”Rows”), NL(”Columns”), or NL(”Sheets”)). This will have a dramatic negative effect on the report runtime. We can wrap our above jet formula inside of NP(”Eval”) which will control the =NOW function to only run once when you update the report.

=NP("Eval","=NP(""Datefilter"",DATE(YEAR(NOW()),MONTH(NOW()),1),DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)")

Notice the double quote marks around Datefilter. If you are evaluating a formula that already has quotes inside it, then Excel’s syntax requires you to use two sets of quotes instead of just one.

Sheets replicator with NP("Eval")

The NP("Eval") function will work to reference from an Options page from a worksheet that is replicated with NL ("Sheets"). Just be careful as it will probably act oddly when you are referencing a cell on the same worksheet as your NL(”Sheets”) Using the NP("Eval") function on a sheet with an NL("Sheets") to reference cells that are on that same worksheet is not recommended and is best to keep this on an Options page instead. You can reference ‘off sheet’ by the following (for example, to reference a date range):