Query is a new object in Microsoft Dynamics NAV 2013 that a programmer can use to define a set of data from the Microsoft Dynamics NAV database. Users can query the database to retrieve field’s information stored in the database from a single table or multiple tables using table relation. You can specify how to join tables in the query where you can define filter criteria to get the desired results. You can specify totalling methods on fields, such as sums and averages.
Query helps us to improve the overall performance of retrieving the data from database. Also one query object can be used in several objects depending on the need. This gives us the great reusability option as well.
In the query you have an option to either use the simple NAV query property to fetch the data based on the table relation defined between the data items in the query set or you can define your SQL methods to fetch the data using left outer join, inner join , right outer join, full outer join and cross Join.
Assuming that we all have a basic understanding of SQL joins and we know how it works let’s have a look at how this help us in NAV to query data from the database.
Creating simple Query:
- Create a simple Query Salesperson – Sales Statistics
Go to NAV development environment->Query-> click new query-> define table
In the above image you can see we have defined two data items to get the Salesperson sales statistics and using the query method type Total, NAV system use the SQL sum method to calculate the total value for the columns.
Now after defining the table structure if we run query object directly from Object designer then it opens a page in NAV windows client.
Query object can be useful in graphical representation of data and for that we can use NAV generic chart and can create our own new custom chart to view the statistics in the role centre page.
Creating chart using Query:
In addition to specific charts, such as the Finance Performance window that you cannot create from scratch Microsoft Dynamics NAV 2013 provides the flexible Generic Chart Setup window where you can combine any table or query data with multiple chart properties to create an unlimited number of generic charts for any user.
Below is the simple example of creating a chart using query object.
Go to Generic Chart setup-> click “New” action and enter the chart ID and description-> select source type and source ID (in this case define the newly created query object )-> define x-axis and Y-Axis column.
Preview of the newly created chart
Show the Chart on Role centre:
Publish Query as Web service:
Browse the published Odata web service in Internet explorer.
URL: "localhost:9048/DynamicsNAV71/OData/Company('CRONUSInternationalLtd.')/Salesperson"
About ODATA web service:
The Open Data Protocol (OData) is a web protocol that is designed for querying tabular data and provides you with an alternative to SOAP-based web services.
ODATA builds on web technologies such as HTTP, the Atom Publishing Protocol (AtomPub), and JavaScript Object Notation (JSON) to provide access to information from different applications, services, and stores. OData uses URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with resources.
ODATA web service:
The ODATA web service URL will look similar to the following.
"localhost:9048/DynamicsNAV71/OData/Company('CRONUSInternationalLtd.')/Salesperson"