Advertisements

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:
NPGLUsage-1

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:

=NP(“DateFilter”,StartDate,EndDate)

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.

NPGLUsage-2

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:

NPGLUsage-3

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

NPGLUsage-4

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.

Advertisements

Using NL( Lookup ) in Jet Reports Part-3

We have discussed regarding Lookup in my previous post. If you missed can find link here.

Using NL( Lookup ) in Jet Reports Part-1.

Using NL( Lookup ) in Jet Reports Part-2.

Continuing with more advanced usage I am here below.

Another useful feature of the NL(“Lookup”) function is the ability to specify how many records Jet Reports will go through in order to create a list of values.

By default, Jet Reports uses the value that is set for Maximum Lookup Records Scanned on the Jet Reports Options form. The default is 1,000 records. If the number of desired records to be searched is larger than this setting, the “ScanLimit=” keyword can be utilized.

To apply a scan limit, “ScanLimit=” must be placed in one of the FilterField parameters and then the desired number of records to be searched will be placed in the associated Filter parameter.

To create a Lookup function that will return all of the G/L Account Numbers in the first 5,000 records of a G/L transaction table, the function would look like this:

=NL(“Lookup”,”G/L Entry”,”G/L Account No.”,”ScanLimit=”,”5000”)

The NL(“Lookup”) function normally returns all values (for the particular field) that are present in the table specified.  For fields defined in NAV as “Option” fields, it may sometimes be desirable to display *all possible* value – regardless as to whether those values are present in the table or not.  For this, a useful feature is the “SmartLookup=” option (available in Jet Essentials 2012 R2 and later).

For example, the function:

=NL(“Lookup”,”Item Ledger Entry”,”Entry Type”)

Might provide a Lookup window that may not list all options depending upon data in the table.

By adding the “SmartLookup” option we could get a list of all options defined as option to that field.

 =NL(“Lookup”,”Item Ledger Entry”,”Entry Type”,”SmartLookup=”,”TRUE”)

Lookup-10

Stay tuned for more details in my upcoming posts

Using NL( Lookup ) in Jet Reports Part-2

We have discussed regarding Lookup in my previous post. If you missed can find link here.

Using NL( Lookup ) in Jet Reports Part-1.

I am continuing with more advanced usage here below.

In some instances it is also desirable to base the values that are displayed in one NL(“Lookup”) function on the results that were selected in another NL(“Lookup”) function.

An example of this could exist in a Sales Report. The viewer will have the ability to select a Salesperson Code to run the report for, and will also be able to specify Customer Numbers in order to filter the report further.

If only one Salesperson Code is selected, however, it may be undesirable to display Customer Numbers that are associated with other Salesperson Codes.

In this instance, two NL(“Lookup”) functions will be used, with the Customer Number filtered by the Salesperson Code so that the values are related. The first NL(“Lookup”) function, which will allow the selection of the Salesperson Code, will look like this:
Lookup-6

Lookup-7

The next NL(“Lookup”) function will give the viewer the ability to select from a list of Customer Numbers, but it will be filtered based on the Salesperson Code that was previously selected. This is done but inserting a normal filter into the function and referencing the cell containing the Salesperson Code that was previously selected by the viewer.

This addition would make the report look like this:
Lookup-8
After selecting Salesperson Code Filter when we open Customer List it will show lookup as below:
Lookup-9

Customer List is filtered out with Salesperson Code we selected using Salesperson List Lookup.

Will come up with more details in my upcoming post, stay tuned for more details.

Using NL( Lookup ) in Jet Reports Part-1

The NL(“Lookup”) function can be used to provide Lookup to the Users so that he can select from a specified list of values when setting their report filters. This will allow them to see the list of options available to them in regards to a particular report filter.

The most common (and basic) use of the NL(“Lookup”) function is to simply pull a list of values from the database.

For example, if a list of customer numbers (“No.”) from the “Customer” table is desired then the function would look something like this:

=NL(“Lookup”,”Customer”,”No.”)

The resulting lookup that the user sees would be:

Lookup-1

It is also possible to allow the user to see more than one value from a particular table. This can help the user to make a choice more easily by displaying additional details about the values returned.

Multiple fields can be displayed by placing them in an array. This is accomplished by placing the list of fields to be displayed, separated by commas, in the Field parameter and surrounding it with curly braces.

If the fields to be shown are the “Name” and the “Country/Region Code” associated with each customer “No.”, the resulting function would look something like this:

=NL(“Lookup”,”Customer”,{“No.”,”Name”,”Country/Region Code”})

The resulting lookup would be:

Lookup-2

NOTE: The first field in the array is the field that will be returned by the Lookup form.

In addition to selecting multiple fields to be displayed in the list, it is also possible to customize the headers, that appear at the top of the Lookup window. This can be done by placing “Headers=” in one of the FilterField parameters of the NL(“Lookup”) function.

For example, instead of the field names appearing as “No.”, “Name”, and “Country/Region Code”, it has been decided that they should be displayed as “Cust. No.”,”Cust. Name”, and “Cust. State” to the user.

To do this, “Headers=” will be added in one of the FilterField parameters, and the desired names will be placed in the associated Filter parameter. The function should look like this:

=NL(“Lookup”,”Customer”,{“No.”,”Name”,”Country/Region Code”},”Headers=”,{“Cust. No.”,”Cust. Name”,”Cust. State”})

The resulting Lookup window would now appear as:

Lookup-3

In addition to returning a list of values from the database, it is also possible to manually specify the values that will be returned.

This allows you to present the user with a list of values that are not stored in your database. The syntax that will be used is slightly different. Since data is no longer being returned directly from the database, the Table parameter is no longer used to specify the table that the information will pull from. Instead, an array containing the values to be displayed is placed in the Table parameter of the Lookup function. The Field parameter will then contain the list of column headers of the Lookup window.

To create an NL(“Lookup”) function that will display “East”, “West”, “North”, and “South” for Directions, the function will be:

=NL(“Lookup”,{“East”,”West”,”North”,”South”},”Direction”)

The resulting lookup window would now be:
Lookup-4
NOTE: It is important to place the field name that will be displayed at the top of the Lookup window (in this example “Direction”) in the Field parameter. Omitting this will not display any values in the Lookup Window.

In addition to placing the values in the NL(“Lookup”) function itself, a cell reference can also be used. The following example will display the exact same result as the previous example, but the values are specified by a cell reference instead of text.

It is also possible to display multiple columns in the Lookup Window by utilizing cell references. To achieve this, another column of values will need to be inserted next to the values that we are already displaying. Once this is done, the cell reference in the NL(“Lookup”) function will also need to be expanded to encompass both columns of data. Since multiple columns are now being specified, names for these columns will also need to be defined in the NL(“Lookup”) function as well. This is done by using the array syntax that was described above in order to specify the field names in the Field parameter, in this case “Direction” and “Description”.

=NL(“Lookup”,F5:G8,{“Direction”,”Description”})

Below is an example of what this would look like:

Lookup-5

Will come up with more details in my next post on this.

Stay tuned for more details in my upcoming post.

Using Jet Report NL Function

 
Returns fields or record keys from a table based on filters. Duplicates report templates.
 
Note:
If the NL function is making copies of a template, it must be the only function in the cell.
The functions =-NL(“Rows”) and =NL(“Rows”)*-1 are not valid.
 
NLFunction-1
 
Example:

=NL(“Rows”,”Customer”,,”Salesperson Code”,”PS”,”Shipping Agent Code”,”DHL”)

This NL returns the record key for all the Customer with Salesperson Code = ‘PS’ and Shipping Agent Code as ‘DHL’
 
NLFunction-2

If you specify multiple filters, they combine in a logical AND.
 
NLFunction-3

NL Function Parameters & Arguments with brief Description.
 
NLFunction-4
NLFunction-5
NLFunction-6

Navision Cronus NL Function Examples:

This NL that returns the record key for all of the customers in the Customer table who are in the City of Boston with a Balance less than zero

=NL(“Rows”,”Customer”,,”Balance”,”<0″,”City”,”Boston”)

This NL returns the Customer Name from sales quote number 10000. This NL can only return one record so the “What” parameter is blank

=NL(,”Sales Header”,”Name”,”No.”,”10000″,”Document Type”,”Quote”)

This NL returns information for a company other than the default one in the Options screen

=NL(“Rows”,”Customer”,,”0″,”CRONUS USA, Inc.”)

This NL returns information for a company other than the one in the Options screen using a connection other than the default.

=NL(“Rows”,”Customer”,,”0″,”CRONUS USA, Inc.”,”DataSource=”,”2″)

This NL creates sheets called “US”,”CANADA” and “MEXICO” using an array in the table field

=NL(“Sheets”,{“US”,”CANADA”,”MEXICO”})

This NL creates lookup values for use with Report Options for each item in cells F5 through F15.

=NL(“Lookup”,F5:F15,”My Values”)

 

See my upcoming posts for more details and uses of this function.

 

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: