Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Creating Report in Jet Using NL, NF, NP & GL & Excel Formulas

Dear friends today I will discuss report “General Ledger Budget to Actual by Period” and demonstrate the usage of NP & GL Functions.

This report will contain all the Functions, Commands we discussed till now and usage of NP & NL Functions.

You can refer my earlier posts for more detailed information which will help you understanding this report better, for your convenience I am providing link to previous posts which may help you understanding the terms being used in this report.

Using Jet Report NL Function

Using Jet Report NF Function

Using NL( Lookup ) in Jet Reports Part-1

Using NL( Lookup ) in Jet Reports Part-2

Using NL( Lookup ) in Jet Reports Part-3

Using NP Function in Jet Reports

Using GL Function in Jet Reports

Let’s start with creating Option Page before we start with report creation:

If you see in above sheet few filters are defined for the report, most of them are normal and Lookup which we have already discussed in previous report and posts.

Please be sure ‘=’ have been removed from formulas for presentation purpose, make sure you add them when use in your report.

Here new thing which we see is in E3 Cell:


Here NP function creates a filter variable date filter which takes the StartDate & EndDate to create filter in Navision format like 01/01/2015..31/12/2015, which can be used in other Jet Functions as an parameter.

Option denotes these value will be asked from user when report is executed.

Lookup provides List of Values for selection to the user.

All text in A Column & Row 1 are the keywords or reserved words of the Jet Reports.

All text B3..B8 are Text or Option Heading which will be displayed in Option form when report is executed.

All text C3..C8 are the default Values for the options, remember (*) means no filters applied or include all. Don’t Forget to define Name of the cells in Name box, you will find this in the Left of the Formula Bar. The name I am using in my report defined below, this will help us using these user friendly name as filter in our Functions.

Cell Name
C3 StartDate
C4 EndDate
C5 GLAccountNo
C6 BudgetName
C7 PeriodType
C8 BlankZero
E3 DateFilter

Let’s Start our Report Design, Insert one more sheet for report format design.

Our design will be as follows, we will discuss the formula used in these columns later below in this post.


The Jet Formulas we are using in above sheet is as below:

Cell Formula
I3 =NL(,”Company Information”,”Name”)
J4 =PeriodType
J5 =NP(“DateFilter”,StartDate,EndDate)
J6 =NP(“Eval”,”=Today()”)
K8 =NL(“Columns=5”,NP(“Dates”,StartDate,EndDate,PeriodType))
K9 =NL(,NP(“Dates”,K8,”30/12/2050″,PeriodType,TRUE))
C12 =IF(AND(Heading=FALSE,BlankZero=”Yes”,MIN(K12:Q12)=0,MAX(K12:Q12)=0),”Hide”,”Show”)
D12 =NL(“Rows”,”G/L Account”,,”No.”,GLAccountNo,”Date Filter”,DateFilter)
E12 =NF($D12,”Account Type”)
F12 =OR(AccountType=”Heading”,AccountType=”Begin-Total”)
G12 =NF($D12,”Indentation”)
H12 =IF(E12=”Posting”,NF(D12,”No.”),IF(OR(E12=”Total”,E12=”End-Total”),NF(D12,”Totaling”),”0″))
I12 =REPT(” “,G12*5) & NF($D12,$I$11)
K12 =GL(“Budget”,$H12,ColumnStartDate,ColumnEndDate,,,,,,,BudgetName)
L12 =GL(“Balance”,$H12,ColumnStartDate,ColumnEndDate)

We can mix and match Excel formulas too to achieve data we require in our report especially any calculation of values from other cell values. You may find many of them is being used in this report too. You can apply formatting of Excel for better presentation of your reports. Sometime cell references to help in repeating the value to the cells and making available to access to upcoming cells when report is executed.

In Cell C1 [Hide+?] denotes this column will be used to get decision at run time like if we want to hide or show the respective row. As this value will not be available at design time, but when report is executed some rows we want to hide from the output of the report to user. Anything we are sure and know well in advance that this row need to be Hide we can key [Hide] in column A of that Row.

See in Cell C12 formula: [=IF(AND(Heading=FALSE,BlankZero=”Yes”,MIN(K12:Q12)=0,MAX(K12:Q12)=0),”Hide”,”Show”)]

Here decision is taken either we need to Show/Hide this row from output depending upon the test value. This value will be only available when data is retrieved and presented in Report, at design time we cannot predict what will be the value in these column and what will be the result of our test.

If we want to Hide any Row we Key [Hide] in A Column of that Row, Similarly if we want to Hide any Column we Key [Hide] in Row 1 of that Column.

Column L8 & L9 simply copy Value of K8 & K9 Respectively. K10 =K8 here too value is copied.

Rest All Values are Simple text used for Heading in Report Output.

[=REPT] this is Excel Formula Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text, number_times)

The Cell M12 usage simple Excel Formula [=K12-L12].

The Cell N12 also usage simple Excel Formula [=IF(K12=0,””,ROUND((M12/K12),2))]

On executing the Report I fill below Filters:


Applying above Filters the Output of report from my Standard Navision 2015 Report I get below Output:


Due to size limit I have reduced the zoom of the excel so that the exact report output in full can be shown.

Remain tuned for more information.

I will come up with more details in my upcoming posts.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Using NP Function in Jet Reports

Syntax: =NP (What, Arg1, Arg2,…,Arg22)

Purpose: Does various utility functions documented below.

Let’s see what options are available in below table:

What Description/Parameter
“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.
“Format” 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.
“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. 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.


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.


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

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


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″},”|”)


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”, “|”)


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


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)

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”))


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


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

Arrays are lists of data values. You can obtain a string representing such a list from Jet using “Filter” as the What parameter in an NL function. The values in arrays returned by Jet are guaranteed to be unique. The resulting array might be a list of Customers or a list of Invoice Document numbers or any other list of data that match a set of filters. The array calculation operations of the NP function allow you to find different combinations of two arrays.

An example of when you would need an array calculation is listing the invoice document numbers where either the Type on an Invoice Line is “Item” for all item numbers, or the Type is “G/L Account” and the account number is 300. Both the Item numbers and the G/L Account numbers are stored in the same “No.” field, so there is no single set of filters that will create this list of document numbers.

The array operations available in the NP function are “Difference”, “Union” and “Intersect”. The difference between two arrays consists of all of the elements that are in the first array but are not in the second. The union of two arrays consists of a single copy of all of the elements in both arrays with any duplicates eliminated. The intersection of two arrays is the set of elements that are common to both arrays. An example of the results of the array operations are listed in the table below.

Array 1 {100, 200, 300, 400, 500} Array 2 {400, 500, 900, 1000, 2000}
Difference {100, 200, 300}
Union {100, 200, 300, 400, 500, 900, 1000, 2000}
Intersect {400, 500}

=NL(“Rows”, NP(“Union”, NL(“Filter”,”Customer”,”No.”,”Name”,”A*”), NL(“Filter”,”Customer”,”No.”,”Name”,”B*”)))


The following formula creates a list down rows of the document numbers of all invoices where either the Type field is “Item”, or it is “G/L Account” and the No. field is 2000.

=NL(“Rows”, NP(“Union”, NL(“Filter”,”Sales Invoice Line”,”Document No.”,”Type”,”Item”), NL(“Filter”,”Sales Invoice Line”,”Document No.”,”Type”,”G/L Account”,”No.”,”2000″)))

You should be cautious using arrays because they are often not the easiest or fastest way to solve a problem. Example 1 is a good example of a query that does not require arrays, and will run much slower if you use them. Also remember that, with Jet Essentials 2015 and earlier, if NP(“Union”), NP(“Intersect”), or NP(“Difference”) are by themselves in a cell they will only return the first value from the array. You must put them inside NL(“Rows”) as in the examples above in order to correctly return all the data.

There are two more array operations that behave a bit differently than those listed above: “Split” and “Join”. “Split” takes two text strings and splits the first string based on the second, resulting in an array. For instance, if you wanted to create a list of account numbers based on the string “1000+2000+3000”, the formula would look like the following.


The result would be the array {“1000″,”2000″,”3000”}. Note that this must be put inside an NL(“Rows”) as in the Union examples above in order to return all the data.

In the opposite scenario, if you have an array but would like to create a text string by joining each element of that array separated by a given string, you would use the “Join” operation. Using the same array, you can create a string for a filter with array values separated by the “|” character with the following formula.


The result would be the text string “1000|2000|3000”, which is a valid filter that you could pass into an NL function.

For Join and Split, Arg1 of the NP function is the value you want to manipulate and Arg2 is the character by which you want to join or split the value. If you experiment with these operations, you will find that you have an amazing amount of flexibility, especially when you use them in conjunction with the other array calculation formulas listed above.

Please note that the results of an NP(“Join”) may be very large and thus putting it directly inside another function may cause problems with Excels 256 character formula limit as in the following formula.


It is recommended that in a situation like this the NP(“Join”) be placed in a separate cell as in the following.

B2: =NP(“Join”,{“some”,”array”,”here”},”|”)

B3: =NL(“Rows”,NP(“Split”,B2,”|”),”|”))

Stay tuned for usage of NP functions in Jet Reports.

I will come up with more details in my upcoming posts.