=NP(What, Arg1, Arg2,..., Arg22)
Purpose: Performs utility operations. For general information about the NP function, see Introducing the NP Function

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 Jetspecific 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 Jetspecific 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 Jetspecific 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 Jetspecific 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: 
"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 4digit year then a 2digit month and 2digit day. 
NP Function Examples
Use of Array Calculations (i.e., UNION, INTERSECT, DIFFERENCE)
EVAL
To increase performance, you can reduce crosssheet 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 " 100200300400" for potential use in another function.
=NP("Join",{"100","200","300","400"},"")
SPLIT
The following NP(Split) splits up the string "thisisanarray" and creates the array {this, is, an, array}.
=NP("Split", "thisisanarray", "")
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","=E6F6") 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.
Comments