Microsoft Dynamics NAV 2013 has introduced a new object type!
A ‘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
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
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…