Queries
A query is a new object in Microsoft Dynamics NAV 2013 R2 that you use to specify a set of data from the Microsoft Dynamics NAV database. You can query the database to retrieve fields from a single table or multiple tables. You can specify how to join tables in the query. You can filter the result data. You can specify totaling methods on fields, such as sums and averages.
The following section describes how to locate Microsoft Dynamics NAV 2013 R2 documentation about designing queries in Microsoft Dynamics NAV.
Creating Queries
This section explains how to design queries to specify datasets.
| To | See |
|---|---|
Learn how to create a basic query in Query Designer. |
|
Learn the supported ways to join tables in Query Designer. |
|
Learn how to add totaling methods to columns in a query. |
|
Learn how to filter the results of a query. |
|
Learn how to run queries from C/AL code |
|
Learn how to create complex queries by walking through examples that use the CRONUS International Ltd. demonstration database. |
Walkthrough: Creating a Query to Link Two Tables Walkthrough: Creating a Query That Uses a Totaling Method and Sorting |
Note
You cannot run a query that gets data from both the application database and the business data database. This also applies to single-tenant deployments so that you do not have to rewrite queries if you decide to export the application. For a description of which tables are considered part of the application database, see Separating Application Data from Business Data.
Using Queries
The following examples show how you can use queries in your Microsoft Dynamics NAV application.
Creating charts that are based on a query instead of a table. For more information, see How to: Create Generic Charts in the Application Help for Microsoft Dynamics NAV 2013 R2.
Saving a query as an .xml or .csv file. You can use the SAVEASXML Function to create an .xml file that contains the resulting dataset of a query. You can use the .xml file to integrate with external applications.
Exposing data as an OData web service. You can register and publish a query as a web service in the same way that you can register and publish pages or codeunits as web services. You use the Web Services page in the RoleTailored client to register and publish pages, codeunits, or queries. After you expose a query as a web service, you can import it into other applications. For example, you can import Microsoft Dynamics NAV data into Microsoft Excel using Microsoft PowerPivot for Excel, which is a data analysis add-in. You can then create PivotTables in Excel that use the Microsoft Dynamics NAV data from your query. For an example , see Walkthrough: Combining Data from Microsoft Dynamics NAV Queries and Pages with Data from Azure DataMarket (OData)