Azure, Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration, Machine, Server, Tip & Tricks, Virtual

Creating Virtual Machine on Azure

Continuing from my previous post.

In previous post we saw how to register for Free Azure Portal Account, if you missed you can find the link here.

Today we will see how to create Virtual Machine for Dynamics Navision 2016 pre Loaded.

Lets start creating one for our future posts practice.

Open the page : and follow onscreen Instruction to login to your account.

Login using the account credentials you used for registering on Azure Portal.


Upon signing you will be landed to your dashboard on Portal.

You can see we don’t have any resource on Portal yet.


Lets start creating one.

Click on New from Top right corner.

Choose Virtual Machine.

Enter “Microsoft Dynamics NAV 2016” in the search box, it will list available Virtual Machine images.

Select the one available Virtual Machine for commissioning.

I have choose this because we will get preinstalled NAV 2016 which will save our time.


Select Deployment Model, I have choose Classic and click on Create.


Next we will enter  Host Name, User Name, Password, other things you adjust as per your need. Click on Create.

Note your User Name & Password you will require this to login to the Server.


Give some time to get created and up your Server.

In the mean time you can explore other things till your Server is up and running for you.

Once it is made available, you can start login and explore your newly created Server.

Where to find my Server?

From the Navigation Bar choose Virtual Machine (classic).

It will list you all Virtual Machines created by you.

Select your Virtual Machine in my case it is NavDemo.

You can find the Server Name and IP of your Virtual Machine.


I am using my IP to login to my Server.

Open your Remote Desktop Connection and enter your IP.

The User Name & Password as we created in previous step.


Here I am Login to my Server.


You can see that Dynamics NAV 2016 is already available with my Server.


That’s all for today, we will see more in my upcoming posts.

Till then keep exploring & Learning.




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

Creating My First Report using PowerPivot

In my earlier posts we have created the Data model for Analysis.

If you missed them you can follow below links to refresh all information.

PowerPivot for Excel

PowerPivot Creating a Data Model in Excel 2013

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

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

Create a calculated column in PowerPivot

We have learned basic actions, let’s create our first Report.

I will be creating Pivot Matrix Customer Vs Item Sales.

Open the Excel in which we created our Data model and imported our table data.
Select PivotTable From Ribbon under Home Tab.

Select New Worksheet or Existing Worksheet as per the case.
You will see a Pivot is inserted, in right side you will find all the tables available in the Data model of this Worksheet.

Design the Pivot as above screenshot.
The output of this report will be as below if executed without Date Filter.
I will come up with more details and features of PowerPivot in my upcoming posts. Till then keep practicing and stay tuned for more details on this topic.

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

PowerPivot Creating a Data Model in Excel 2013

Before you can create a PivotTable, you’ll need some data. Let’s get some data from the Navision database.

In Excel, open a blank workbook.



You have several options for getting data from no of sources.

Recall from my earlier post where we accessed data using web service in Excel.

Viewing Page Data in Excel Using PowerPivot (OData)

Let us see what other options are available to us.

In today’s Example I will be connecting with SQL but will access Navision 2015 Database.

Follow the Steps below:

Give meaningful name to your connection in my case I have used Nav2015DB_SQL_Connection.

Select the SQL Server to which you wish to connect in my case I have used INDEL-AXT5283N1, basically this is the SQL Server installed on my Laptop.

Specify the Login method, I am using Windows Authentication, if required you can use SQL Server Authentication. In later case you will have to specify your User Name & Password.

Select the Database to Connect, I have selected Demo Database NAV (8-0) Std. Database for Navision 2015.

Click the Test Connection button, If everything is ok you will get Connection Succeed Message, as shown in above screen shot.

Press Next for next step.

In this screen you have option to either import data from Tables and Views or you can Write Query to fetch data for import.

In my case for this example I am importing data from tables.

Select option – Select from a list of tables and views to choose the data to import and Press Next for next step.

I have Selected two Tables Cust. Ledger Entry & Customer.

Select Related Tables button ensure to select if any other tables related to this Table.

In my case not applicable. Click Finish to add data to the Data Model/ Import the data of these two tables.
Select Close to return to Data Sheet imported after this operation.
Here you see your Data for these two tables have been imported in two sheets.

What happened?

You might not have realized it yet, but you’ve just created a data model. It’s created automatically when you import or work with multiple tables simultaneously in the same PivotTable report. The model is mostly transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. In Excel, the presence of a data model is evident when you see a collection of tables in the PivotTable Fields list. There are several ways to create a model.

I will come up with more details in my next post.

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

Other options for creating Report in Jet

Jet Report

Report Wizard

An entire report can be created from a single table using the Report Wizard. The Report Wizard allows data to be grouped, filtered, sorted, subtotaled, and formated.


Report Builder

The Report Builder creates reports based on Jet Data Views.  Jet Data Views define table relationships, available fields, field captions, and table captions for a particular reporting area, such as sales, inventory, payroll, etc.  Jet Data Views can be created using the Data View Creator.

Before Using the Report Builder

Before you use the Report Builder you will need to import or create data views and categories.

A set of data views for Dynamics NAV can be found on Jet Web Site you can access the same from here. You can also click on the Download Data Views link within the Report Builder.

After downloading the file, you should open the .zip folder and extract the data view category (.jdc) file.

Importing Data Views and Categories

To import data views and categories, go to the Data Source Settings and select File -> Import -> Data View Categories.


Table Builder

An entire report can be created from a single or more table using the Report Wizard. The Table Wizard allows data to be grouped, filtered, sorted, subtotaled, and formated.



Provides browsing window to Select Tables and Fields, using which you can directly create an NL/ NF Function with their parameters and arguments.


This way I reach to end of my Jet Report Introduction Series. In future I will keep adding more details.


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

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:
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:
(*) 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:

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:


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

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

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

Step 3: Following Step 2 add all other Fields.

The Excel should look as below:

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:

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

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,


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


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.

The output of Report will as below:

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.

Development Tips, Multitenancy, Security, Server

Creating the Multitenant Environment

Before you start I will recommend to go through my earlier post on Multitenancy Concept & Overview here.

To start with the below steps you will require 2 Accounts preferably Domain Accounts:

  • An Account for running MS Dynamics Navision Service
  • An Administrator account for Database & RTC

How to setup these accounts and what permissions are required for these accounts I would recommend to go through my earlier post on Provisioning the Microsoft Dynamics NAV Server Account here.

Add both the above account to Navision Database with Super permission.

In order to create a multitenant environment (here for Demo Database NAV (8-0)), the following steps need to be done:

  • Take an SQL Backup of the running Demo Database NAV (8-0) database.


  • Restore the newly made SQL Backup to a new database (MultiTenantDatabase NAV (8-0) Demo).


  • On the NAV Server, using the NAV 2015 Administration console, create a new NAV Server Instance named MTenantDemo


  • Edit the instance and set the database to newly created MultiTenantDatabase NAV (8-0) Demo database, and set the instance to be started with NAV Service user.


  • Start MTenantDemo – and see if you can log in
  • Once you have confirmed the MultiTenantDatabase NAV (8-0) Demo database and MTenantDemo is running, split the database in an application part and a data part:
    • Open the NAV 2015 Administration Shell (or Windows PowerShell ISE and load the NavAdminTool)
    • Export the application part from the MultiTenantDatabase NAV (8-0) Demo database to a new database (MultiTenantDatabase NAV (8-0) Demo_App):

Export-NavApplication -DatabaseServer INDEL-AXT5283N1 -DatabaseName ‘MultiTenantDatabase NAV (8-0) Demo’ -DestinationDatabaseName ‘MultiTenantDatabase NAV (8-0) Demo_App’

  • Remove the application part from the MultiTenantDatabase NAV (8-0) Demo database:

Remove-NAVApplication -DatabaseServer INDEL-AXT5283N1 -DatabaseName ‘MultiTenantDatabase NAV (8-0) Demo’


  • Using the NAV 2015 Administration console, edit the MTenantDemo instance and set the database to newly created MultiTenantDatabase NAV (8-0) Demo_App database
  • And select Multitenant (set it to True):
  • Save and restart MTenantDemo


  • Either through a Powershell commandlet in the NAV 2015 Administration Shell or by using the NAV 2015 Administration console mount a tenant named Tenant-1 on the MTenantDemo NAV Instance:


  • Start NAV using the new tenant:


  • In Companies rename the Company Name to Tenant-1
  • Take an SQL Backup using the MultiTenantDatabase NAV (8-0) Demo_App database, and call it “App_template.bak”
  • Take an SQL Backup using the MultiTenantDatabase NAV (8-0) Demo database, and call it “Tenant_template.bak”

You now have a template you can use when you want to create a application and a tenant.

Restore the Tenant Database as Tenant-2, Tenant-3, Tenant-4, Tenant-5 etc.

Mount the Tenant Databases to NAV Service MTenantDemo as per above steps.

Now you have One Application & 5 Business Data Tenants Mounted.

You will do development in Application Database MultiTenantDatabase NAV (8-0) Demo_App and then Sync your modifications to Business Data Databases named Tenant-1/2/3/4/5 etc.

To Sync your objects to Tenants you will use below script in NAV 2015 Administration Shell:

Set-ExecutionPolicy unrestricted –Force

Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\80\Service\NavAdminTool.ps1’

Sync-NAVTenant MTenantDemo -Force -Tenant ‘MultiTenantDatabase NAV (8-0) Demo’

I will explain in more detail on Creating Tenants, Synchronizing Tenants and uploading License for Tenants in more details in my next upcoming post.

Development Tips

Creating a Test Runner Codeunit

Follow below Steps to Create Test Runner

  • In the development environment, on the Tools menu, choose Object Designer.
  • In Object Designer, choose Codeunit, and then choose New.
  • On the View menu, choose Properties.
  • In the Properties window, in the Subtype field, select TestRunner to specify that this is a test runner codeunit.
  • In the TestIsolation field, specify which changes to that you want to roll back. You can choose from the following values:
    • Disabled
    • Codeunit
    • Function


Value Description
Disabled Do not roll back any changes to the database. Tests are not isolated from each other.This is the default value.
Codeunit Roll back all changes to the database after each test codeunit executes.
Function Roll back all changes to the database after each test function executes.

It is recommend that you design tests to be independent of each other. Tests might read from and write to the same database, which means that tests can interact with each other.

If tests interact, then you may experience incorrect test results.

To eliminate test interactions, use the TestIsolation property to roll back changes to the database after each test function or after each test codeunit.

If you specify that you want to roll back database changes, then all database changes are rolled back, including changes that were explicitly committed to the database during the test by using the COMMIT function.

  • In the C/AL Editor, in the OnRun function, enter code to run the test codeunits. For example, the following code in the OnRun function of a test runner codeunit runs test codeunits.


You may want to define your test suite in a table and then write code in the OnRun function of the test runner codeunit to iterate through items in the table and run each test codeunit.
  • (optional) To create an OnBeforeTestRun trigger, do the following steps:
    • On the View menu, choose C/AL Globals.
    • In the C/AL Globals window, on the Functions tab, on a new line in the Name field, enter OnBeforeTestRun, and then choose Locals.
    • In the C/AL Locals window, on the Parameters tab, enter the following.


    • In the C/AL Locals window, on the Return Value tab, enter the following


    • Close the C/AL Locals window.
    • In the C/AL Editor, in the OnBeforeTestRun trigger, enter code that executes before each test function. Typically, the code in the OnBeforeTestRun function determines if the test function should execute and returns true if it should. Otherwise, the trigger returns false. The OnBeforeTestRun trigger may also initialize logging variables. For example, the following code initializes a logging variable and returns true to indicate that the test function should execute. This example requires that you create the following global variable.


  • (optional) Do the following steps to create an OnAfterTestRun trigger:
    • On the View menu, choose C/AL Globals.
    • In the C/AL Globals window, on the Functions tab, on a new line in the Name field, enter OnAfterTestRun, and then choose Locals.
    • In the C/AL Locals window, on the Parameters tab, enter the following.


    • Close the C/AL Locals window.
    • In the C/AL Editor, in the OnAfterTestRun trigger, enter code that executes after each test function. For example, the following code logs the results of the tests to the test reporting system. This example requires that you create a record variable named log.




log.UnitId := CodeunitId;

log.Unit := CodeunitName;

log.Func := FunctionName;

log.Before := Before;


If Success THEN

log.Status := log.Status::Success


log.Status := log.Status::Failure;

IF FunctionName <> ” THEN





If you implement the OnAfterTestRun trigger, then it suppresses the automatic display of the results message after the test codeunit runs.

  • On the File menu, choose Save.
  • In the Save As window, in the ID field, enter an ID and in the Name field, enter a name for the codeunit. Verify that the Compiled check box is selected, and then choose OK.

For more details see also below posts:

Creating a Test Codeunit and Test Function
Adding a Test to a Test Runner Codeunit