Using NAV 2013 Query Objects

Microsoft Dynamics NAV 2013 has introduced a new object type!

‘Query’ is a new object that allows you to specify a dataset from a NAV table or multiple tables. It will allow you to specify how to join multiple tables, filter the results set and specify totalling methods on fields such as Sums or Averages.

Queries run on the NAV 2013 Server as optimised efficient SQL statements, (i.e. not the User’s client), and can be used for:

· Creating generic Charts using Query Objects rather than basing them on Tables.

· Saving a result dataset as an .xml or .csv file that can then be integrated with external applications.

· Exposing data as an OData Web service for access via Excel PowerPivot or SQL Reporting Services.

To create a Query in Object Designer, you create Data Items and Columns and then link the data items specified.

A data item specifies the table to retrieve records from. A column specifies a field of the table to include in the resulting dataset of a query.

A data item link determines which records to include in the dataset based on a common field between two data items. E.g. Customer No. from the Customer table and the Sell-To-Customer No. from the Sales Line table.

The data items can be linked so that the resulting dataset includes all the records from the first table, even if no matching record can be found in the second table. If matching records do not exist in the second table, then the default value for that data type is output. E.g. if a field is an integer data type then ‘0’ will be returned. This is defined in the query by selecting the value ‘Use Default Values If No Match’ in the DataItemLinks property.

If the resulting dataset should only contain matching records across all linked tables, then select the value ‘Exclude Row If No Match’.

More sophisticated joins can be specified by choosing the ‘SQL Advanced Options’. In this instance the ‘SQLJoinType’ property will be displayed allowing you to specify the join using an SQL statement.

Example of NAV Query

image

This query links the Sales Header to the Salesperson/Purchaser table in order to retrieve the E-mail address of the Salesperson and links the Sales Line to the Item table in order to retrieve the Category and Posting Group information.

Example of DataItem Link between Sales Line and Item

image

Here the value in the ‘Field’ column refers to the ‘No.’ field of the Item and is referenced to the ‘Sales Line’ table, joined by using a match on the Reference Field, ‘No.’, the Item from the Sales Line table.

Running this query gives the following dataset…

image

3 – Tier Architecture

The Microsoft Dynamics NAV 2013 architecture consists of three core components which must ALWAYS be installed with every NAV 2013 deployment.

  1. The RoleTailored client is the client tier, which includes a Microsoft Dynamics NAV Windows client and a Microsoft Dynamics NAV Web client.
    In addition to the RoleTailored client, Microsoft Dynamics NAV 2013 also supports additional client types, including web service clients, a SharePoint client through Microsoft Dynamics NAV Portal Framework, and a NAS services client for programmatic access.
  2. Microsoft Dynamics NAV Server is the middle or server tier, managing all business logic and communication.
  3. SQL Server, augmented by Microsoft Dynamics NAV 2013 database components, is the data tier.

You can have multiple instances of any of the core components in a production environment. The following diagram shows a simple installation with two Microsoft Dynamics NAV Windows clients and a Microsoft Dynamics NAV Web client connecting to a single Microsoft Dynamics NAV Server computer, which in turn connects to a computer with SQL Server and the Microsoft Dynamics NAV database components.

image

The following are additional components that can be installed to augment the core structure:

Microsoft Dynamics Server Administration Tool A configuration tool for managing Microsoft Dynamics NAV servers and sites.
Microsoft Dynamics NAV Portal Framework for Microsoft Sharepoint A set of components that allows you to build Sharepoint web pages that display NAV pages and reports
Development environment (C/SIDE) The development environment for creating and modifying Microsoft Dynamics NAV applications in C/AL
Microsoft Office Outlook Add-In A component for synchronizing data such as To-Do lists, Contacts and Lists between Microsoft Dynamics NAV and Outlook.
Automated Data Capture Systems A Microsoft Dynamics NAV tool for accurately capturing data for inbound, outbound, and internal documents, primarily in connection with warehouse activities. With ADCS, company employees use handheld devices and radio frequency technology to continuously validate warehouse inventories.
Web Server components The components that are needed to enable Microsoft Dynamics NAV Web clients to connect with a browser.
ClickOnce Installer tools A set of tools designed to create ClickOnce deployments for applications for the Microsoft Dynamics NAV Windows client.

The new Navision system management consolidation ‘snap-in’ tool (with MMC Snap In) allows you to manage all you NAV servers together with NAV Services that the Servers support. This includes Client access, Web Services, OData etc.….and any background processes supported by one or more application servers.

image

The left pane shows a tree view that lists all Microsoft Dynamics NAV Server computers that you are administering from this computer and all Microsoft Dynamics NAV Server instances on those computers.

The centre pane shows information about the item that you have selected in the left pane. When the selected item is a computer running Microsoft Dynamics NAV Server, the centre pane shows a list of Microsoft Dynamics NAV Server instances on that computer and the status of each instance (running or stopped), and the name of the account the instance is running under (as shown in the above screenshot).

The right pane displays available actions for the object that is selected in the left pane. These options differ depending on whether a Microsoft Dynamics NAV Server computer or a Microsoft Dynamics NAV Server instance is selected.

Selecting an instance from the centre pane for example shows the following left hand pane…

image

… allowing you to manage those services.

Selecting an instance in the right hand pane displays, and then allows you to view and edit, the settings and configuration of that instance…

image