Development Tips, Excel, How To, Office Integration, PowerPivot, Report

Adding more tables to the Data Model using Existing Connection – In PowerPivot

For Introduction please see PowerPivot for Excel

Recall from my earlier post we have created a connection to SQL Navision Database in my previous post.

PowerPivot Creating a Data Model in Excel 2013

I will be modifying same connection to include more tables to the Data model.
PowerPivot-10

Click on PowerPivot-> Manage.

From the PowerPivot Sheet click on Existing Connections.

This will open Connection Window Listing all available connection to PowerPivot for this Sheet.

Double Click the Connection in my case Nav2015DB_SQL_Connection.
PowerPivot-6

Select first option as we used in previous post and then Next for next Step.
PowerPivot-11
Select Item & Item Ledger Entry Tables and then Finish to import Data to the Sheet.
PowerPivot-12
On Success Click Close to return to Data Model Sheets.
PowerPivot-13

You will find two new sheets have been added to PowerPivot Sheet.

I will come up with more option on this topic in my next post, till then start practicing and stay tuned for more details & Updates on this topic.

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:
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.

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

Using GL Function in Jet Reports

Syntax: =GL (What,Arg1,Arg2,Arg3,..,Arg22)

Purpose: Returns the budget, balance, net change, quantity, debits or credits of the G/L Account of a given company based on filters.

Dynamics NAV Parameter Description
 What NAV: Determines what the GL Function returns. Options are Balance, Budget, Quantity, Credits or Debits Note that the options available for the What argument depend on the Where argument.
 Account NAV: G/L Account Number, Filter or Range. If you specify a single, totaling account, you will get totals. If you specify multiple accounts or a range of accounts, totaling accounts will not be included in the returned number even if the other account(s) have nothing to do with the specified totaling account(s). If the Where argument is “Rows”, “Columns”, or “Sheets”, then the What options are “Accounts” which will give a list of account numbers, “Categories” which will give a list of account category numbers, or “SegX” where X is a segment number and which gives a list of that specific account segment.
StartDate NAV: Specifies the starting date of transactions to include. If you are interested in the balance of an account on a given date, leave StartDate blank. If you are interested in the net change of an account, use Balance and specify both the StartDate and EndDate
 EndDate NAV: Specifies the ending date of transactions to include. Specifying a start period and an end period will give you the net change between the first day of the start period and the last day of the end period. Specifying a start period with no end period will give you the net change between that start date and the present. Specifying no start period will give you the balance/budget as of the end period. Specifying no start period or end period will give you the present balance/budget.
 View NAV: The G/L Analysis View to use. Leave this blank to use balances from the G/L directly. Analysis Views are available in Navision version 3 and later. This field should be blank if you are using objects from an earlier version of Navision.
 Dim1 NAV: Filter for the first dimension of the analysis view. If View is blank, this is the filter for Global Dimension 1. Dimension totaling is handled the same way as Account totaling. In Navision versions before 3.0, Dim1 is used as the Department filter.
 Dim2 NAV: Filter for the second dimension of the analysis view. If View is blank, this is the filter for Global Dimension 2. In versions before 3, this is the Project filter.
 Dim3 NAV: Filter for the third dimension of the analysis view.
 Dim4 NAV: Filter for the fourth dimension of the analysis view.
 BusinessUnit NAV: Filter for the business unit.
 Budget NAV: Budget filter. This is unused unless returning budgets.
 Company NAV: Company Name. This must be spelled the same as it appears in Navision, including case, spaces and punctuation. If this parameter is empty (“”), the default company in the Jet Reports Options/Data Sources Screen is used.
 Reserved NAV: Blank. For backwards compatibility, a Data Source Name as defined in Jet/Options can be used.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved   GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies the budget filter, blank for all budgets. Note that budgets are associated with a specific year in Great Plains so if your budget and fiscal year filters do not coincide you will get a 0 value.
 ExcludeClose NAV: “True” to exclude closing date transactions. Defaults to “False”.
 ShowQuery NAV: “True” to show the finhlink string that will be used for drilldown. Defaults to “False”.
 Reserved GP: Company name. If this parameter is blank, the default company is used.
 Data Source Data source name. If this parameter is blank, the default data source is used.

Reports based on the G/L are easy with the GL function.

=GL(What, Account, StartDate, EndDate, View, Dim1, Dim2, Dim3, Dim4, BusinessUnit, Company, Reserved, ExcludeClose, Reserved, Reserved, Reserved, Reserved, Reserved, Reserved, ShowQuery, Reserved, DataSource)

NAV Cronus Examples

To retrieve the balance of G/L account 44100, you would type the following.

=GL(“Balance”,”44100″)

If you wanted to know the net change of account 44100 between 1/1/2002 and 1/31/2002, you would type the following.

=GL(“Balance”,”44100″,”1/1/02″,”1/31/02″)

For G/L Balances with standard NAV:

  • you can filter on the two Global Dimensions for G/L Balances or
  • if using a NAV Analysis View, you can filter on up to 4 dimensions that are tied to that View=GL(“Balance”,”40100″,,,,”USA”,”COPPER”) 
  • Please note that some NAV verticals that allow more than 2 Global Dimensions may not be compatible with the GL function. 
  • For the balance of account “40100” with Global Dimension 1 of “USA” and Global Dim 2 of “COPPER”, you can use the following function.

Stay tuned for how to use GL Function in Jet Reports.

I will come up with more details on this 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.

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.
NP-1
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)
NP-2
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-3
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*”)))

=NL(“Rows”,”Customer”,”No.”,”Name”,”A*|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.

=NP(“Split”,”1000+2000+3000″,”+”)

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.

=NP(“Join”,{“1000″,”2000″,”3000″},”|”)

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.

=NL(“Rows”,NP(“Split”,NP(“Join”,{“some”,”array”,”here”},”|”),”|”))

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.

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

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

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

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.

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

Creating Simple List Report in Excel Using Jet Reports Part-2

In my previous post we saw how to create simple report in excel using Jet Report.

If not seen please refer it before you continue with this post, here is the link for same:

Creating Simple List Report in Excel Using Jet Reports Part-1

Using NL Lookup Part-1

Here we will start from where we left in previous post.

We will add one more sheet in Report we created in our previous post, and name it is as Option, where we will define all of our Filters.

Our Option sheet will look as below:
JetSimpleReport-7
Here we have created 3 Filters Customer No., Credit Limit LCY & Balance LCY.

Next Step will be to apply this Filter provided by user at runtime to the Report.

Return to your Report Sheet and edit the NL Function used to retrieve Rows from Customer as below:
JetSimpleReport-8
(*) in filter denotes all, in other words no Filter.

Now let see the Filter Sheet how it behaves when we run the report.

When we run the report first Report option is shown, where we will give our Filters:
JetSimpleReport-9

Here I am giving below filters:

Customer No. Filter                         *              Include All Customers

Credit Limit (LCY) Filter                  0              All rows with Credit Limit as Zero

Balance (LCY) Filter                      >0             All rows with Balance value greater than Zero

The output of the report should be as below:

JetSimpleReport-10

Stay tuned for more details in my future posts.

I will explain more about commands, filters, functions, lookup etc.…

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

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.

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

Creating Simple List Report in Excel Using Jet Reports Part-1

Dear friends I will discuss today simple report creating in Jet Reports.

I will take a simple example for creating Customer List showing the Credit Limit & Balances.

We will be using two basic Functions of Jet Reports NL & NF.

Then we will add few more features in my next post on this report.

Let’s Start Step wise Step:

Step 1: Add NL Function to retrieve Records from Customer Table.
JetSimpleReport-1
I have Add NL Function in Cell E5 as shown above, if want to add using Jfx – NL place a Cursor in E5 and press NL from Jfx Group of functions and fill as below:
JetSimpleReport-2

Step 2: Add Fields which you wish to include in your Report using NF Jfx Function.

I am adding No. field from Customer Table.

Here we have already created the connection to Customer Table in previous Step. This will retrieve all Fields and Records from the Customer Table.

Using NF function I am selecting the Fields of which we want to show/include value in our Report.

I have add the Function in Excel as shown below:
JetSimpleReport-3
I have Add NF Function in Cell F5 as shown above, if want to add using Jfx – NF place a Cursor in F5 and press NF from Jfx Group of functions and fill as below:
JetSimpleReport-4

Step 3: Following Step 2 add all other Fields.

The Excel should look as below:
JetSimpleReport-5

I have Added fields No., Name, Credit Limit (LCY), Balance (LCY).

I have also added Heading for these fields, and applied general Excel Formatting.

The Column E with NL function, I have Hide from the Report Output as the information is having no relevance showing to User.

Now we are god to see the output of our Report Created above. Output format of this report will be as below:
JetSimpleReport-6

Stay tuned to have more updates in my upcoming posts.

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

Creating My First Report Using Jet Reports

Today I will discuss, how to create reports in Excel using Jet Reports.

Below links will be helpful to refresh what I have shared till now:

Installing Jet Express for Excel – Navision 2015

Installing and Publishing the Jet Business Objects on the Microsoft Dynamics Server

Publishing the Jet Data Source Codeunit to the Web Service

Enable SOAP Services and identify connection parameters

Configuring a Data Source in Jet Express

Using Jet Report NL Function

Using Jet Report NF Function

Using Link in Jet Reports

We will be using NL Functions.

Also we will introduce few Arguments for NL Function.

Below Table describes the same:

Parameter Argument Description
What
“Link” Returns a string value that can be used as a filter in another NL function
“LinkField” Returns a string used to retrieve a field from a link table in an NL(Table) function.
“Table” Creates an Excel table object based on the field values returned. Leaving the Field argument blank returns all fields. Use a Field Cache to return multiple fields.
FilterField
“Headers=” Overrides field headers with the array of headers specified by the Filter argument. For use with an NL(Table) or NL(Lookup) function.
“TableName=” Specifies the name to use for the Excel table object created by NL(Table) with the name in the Filter parameter. Use this to refer to the table by name from a Pivot table.
“Filters=” Specifies a set of filters for the query with an array of filters specified by the Filter argument.
“InclusiveLink=” Links the primary table to the one specified by the Filter argument for the purpose of retrieving data.
“IncludeDuplicates=” When the value of the Filter argument is TRUE, specifies that all matching records from the source data will be included in the NL(Table) results.

We will design below report to see how Jet Report is designed.
FirstJetReport-1

Below describes the Formulas need to be inserted in respective cells.

Make sure you add ‘=’ in front of Formulas as defined in Data Type Column.

Cell Reference Formula Data Type
F10 NL(“Link”,”Item”,,”No.”,”=Item No.”) Formula
G10 NL(“Link”,”Customer”,,”No.”,”=Source No.”) Formula
F11 Entry No. Text
G11 Document No. Text
H11 Posting Date Text
I11 Item No. Text
J11 Item – Description Text
K11 Gen. Prod. Posting Group Text
L11 Customer No. Text
M11 Customer   Name Text
N11 Item Ledger Entry Quantity Text
F12 Entry No. Field Names
G12 Document No. Field Names
H12 Posting Date Field Names
I12 Item No. Field Names
J12 NL(“LinkField”,”Item”,”Description”) Formula
K12 NL(“LinkField”,”Item”,”Gen. Prod. Posting Group”) Formula
L12 Source No. Field Names
M12 NL(“LinkField”,”Customer”,”Name”) Formula
N12 Item Ledger Entry Quantity Field Names
E13 NL(“Table”,”Value Entry”,$F$12:$N$12,”Headers=”,$F$11:$N$11,

“TableName=”,”ValueEntry”,”Filters=”,$C$5:$D$8,”InclusiveLink=”,$F$10,

“InclusiveLink=Value Entry”,$G$10,”IncludeDuplicates=”,”True”)

Formula

Although you can directly key in the text for NL commands and functions as formula. Below I show the Function Wizards for better understanding and how to use the same. All marked as Formula in above Table.

For Rest marked as Text or Field Names, you can key them directly in respective cells.
FirstJetReport-2
FirstJetReport-3
FirstJetReport-4
FirstJetReport-5
FirstJetReport-6
FirstJetReport-7

The output of Report will as below:
FirstJetReport-8

Stay tuned for upcoming posts for more detailed information.

I understand above example is bit complex I will come up with more simple and basic reports for beginners in my upcoming posts.