Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

NP Function Reference


=NP(What, Arg1, Arg2,..., Arg22)

 

Purpose:  Performs utility operations. 

For general information about the NP function, see Introducing the NP Function

 

Help Topics

NL Function
GL Function
NF Function
Filtering

 

 

What Description
"Eval" Evaluate the formula in the Arg1 parameter. The formula must be enclosed in quotes and will be evaluated when the report refreshes.
"DateFilter" Calculates a date filter using the start date and end date specified in the Arg1 and Arg2 parameters.
"Union" Returns (in the form of a Jet-specific list) the Union of two arrays specified in the Arg1 and Arg2 parameters. Note that in versions of Jet Essentials 2015 and earlier, if NP("Union") is by itself in a cell, it will only return the first value from the array. For those versions, you must put it inside an NL("Rows") in order to correctly return all the data.
"Integers" Returns a string that can be used to generate integers using a Replicator, where Arg1 is the start number and Arg2 is the end number. 
"Intersect" Returns (in the form of a Jet-specific list ) the intersection of two arrays specified in the Arg1 and Arg2 parameters. Note that in versions of Jet Essentials 2015 and earlier, if NP("Intersect") is by itself in a cell, it will only return the first value from the array. For those versions, you must put it inside an NL("Rows") in order to correctly return all the data.
"Difference" Returns (in the form of a Jet-specific list ) the difference of two arrays specified in the Arg1 and Arg2 parameters. Note that if NP("Difference") is by itself in a cell, it will only return the first value from the array. You must put it inside an NL("Rows") in order to correctly return all the data.
"Join" Joins the elements of the array specified in Arg1 together into a single string separated by the contents of Arg2.
"Split"

In versions of Jet Essentials 2015 Update 1 and higher, this function splits the string in Arg1 into a Jet-specific list.

In earlier versions of Jet Essentials, this function splits the string in Arg1 into an array of values (instead of a Jet list). The splitting is delimited by the contents of Arg2. Note that if NP("Split") is by itself in a cell, it will only return the first value from the array. You must put it inside an NL("Rows") in order to correctly return all the data. 

"Codeunit" Evaluates and returns the value returned by the Dynamics NAV code unit function.
"Companies" Returns a list of the companies associated with a data source. Arg1 is a company filter such as A* to return all companies that start with the letter A. Leaving Arg1 blank will return all companies. Arg2 is the data source. Leaving Arg2 blank will return companies from the current data source. Note that you should reference the result of this function in the table argument of an NL replicator function to actually list them out in Excel.
"Dates"

Returns a string that can be used to generate dates using a Replicator, where:
Arg1 is the start date
Arg2 is the end date.
Arg3 can be used to specify a period type of Day, Week, Month, Quarter, or Year.  Default is Day.
Arg4 can be set to "True" in order to return the end of each period.  Default is "False".

"Datasources" Returns an array containing the current user's Jet data sources.
"Formula" Evaluates the Excel formula contained within Arg1.
"Slicer" Returns an Excel Slicer in Arg1 that can be used as a filter in Jet functions when using a Cube data source.
"Format"

FOR BACKWARD COMPATIBILITY - DO NOT USE or USE EXCEL'S TEXT() FUNCTION

Formats an expression with a specific Excel formatting string.  Arg1 is the expression to format such as a date or cell reference, and Arg2 is the Excel formatting string such as "YYYY/MM/DD" for a date formatted with a 4-digit year then a 2-digit month and 2-digit day.

 

NP Function Examples

 

Use of Array Calculations (i.e., UNION, INTERSECT, DIFFERENCE)

 

EVAL

To increase performance, you can reduce cross-sheet references.
The following NP evaluates the formula in cell of D5 from a worksheet called Options.
=NP("Eval","=Options!$D$5")

This function is executed once on refreshing the report, rather than for every cell update.
=NP("Eval","=Today()")

 

Performance can also be increased by not using volatile functions.

DATEFILTER Results of using the NP(DateFilter) function, which can then be nested in other functions.

 

 

INTEGERS

This NP(Integers) function will create rows with the numbers 1 through 10.
=NL("Rows",NP("Integers",1,10))

 

JOIN

The following NP(Join) joins the strings from an array and creates the result " 100|200|300|400" for potential use in another function.
=NP("Join",{"100","200","300","400"},"|")

 

SPLIT

The following NP(Split) splits up the string "this|is|an|array" and creates the array {this, is, an, array}.
=NP("Split", "this|is|an|array", "|")

 

COMPANIES

The following NP(Companies) function lists all the companies for the current data source in rows.
=NL("Rows",NP("Companies"))

 

DATES

The use of NP(Dates) to create a set of column headers for a report. (Dates can also be placed in reverse order by putting the later date in first)

 

DATASOURCES

This NP(DataSources) function will return a list of the data sources in use on the machine it is run on.
=NL("Rows",NP("Datasources"))

 

FORMULA

Used in conjunction with the NL(Table) function to define a calculated column in the table definition.
For example: To determine available credit for a customer; if cell E6 contains the credit limit, and cell F6 contains the open credit, then
=NP("Formula","=E6-F6") would be put in the field list of the NL(Table) definition

 

SLICER

The Slicer function works in conjunction with pivot tables and dashboards to provide information for filters when refreshing reports.

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments