Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

NP Function Reference


Related Articles...

Overview

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

Purpose:  Performs utility oprations.

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

What

  • "Codeunit" (Dynamics NAV only)

    Evaluates and returns the value returned by the Dynamics NAV code unit function.

    If connecting to the database through NAV Web Services, any of NAV's Authentication Methods may be used.  Other connection types (SQL Direct, C/Front, JRDS) require Windows Authentication.

  • "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 [ NL("Rows"), NL("Columns"), or NL("Sheets") ] to actually list them out in Excel.
  • "Datasources"

    Returns an array containing the current user's Jet data sources.

  • "DateFilter"

    Calculates a date filter using the start date and end date specified in the Arg1 and Arg2 parameters.

  • "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".
  • "Difference"

    Returns a Jet-specific list containing the difference between the two arrays specified in the Arg1 and Arg2 parameters.  Also see Array Calculations

    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. 
  • "Eval"

    Evaluates the formula in the Arg1 parameter. The formula must be enclosed in quotes and will be evaluated when the report refreshes.

  • "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.

  • "Formula"

    Evaluates the Excel formula contained within Arg1.

  • "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 a Jet-specific list containing the intersection of the two arrays specified in the Arg1 and Arg2 parameters.  Also see Array Calculations

    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. 
  • "Join"

    Joins the elements of the array specified in Arg1 together into a single string separated by the contents of Arg2.

  • "Slicer"

    Converts an Excel Slicer to a filter that can be used in Jet functions.  In Report Mode, the NP(Slicer) function refreshes every time the Excel Slicer changes. The NP(Slicer) function can be embedded in other Jet functions or can be placed in its own cell allowing multiple functions to reference it as a filter.

    The Excel Slicer referenced by the NP(Slicer) function must be associated with at least one pivot table in order for the NP(Slicer) function to update properly.

  • "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, this function splits the string in Arg1 into an array of values (instead of a Jet-specific list). The splitting is delimited by the contents of Arg2.

    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. 
  • "Union"

    Returns a Jet-specific list containing the Union of the two arrays specified in the Arg1 and Arg2 parameters.  Also see Array Calculations

    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.

 


Examples of the NP function

EVAL - The following NP evaluates the formula in cell of D5 from a worksheet called Options. 

=NP("Eval","=Options!$D$5")

Excel handles cross-sheet references slower than on-sheet.  While recent versions of Jet Reports include optimization to correct for this, earlier versions of Jet Reports would perform better if off-sheet references were copied to the current worksheet.

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 a Jet-specific lists containing this, is, my, list

=NP("Split", "this|is|my|list", "|")

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) 

=NL("Columns",NP("Dates","7/1/2019","12/1/2019","Month"))

np_dates.png

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

jac_connected.png

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.

NP-slicer.png

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

Comments