With the release of Dynamics NAV 2013 version, a new object type was introduced, called Query. This object behaves very similar to SQL queries and, effectively, performs the same function – to fetch the necessary data from one or multiple tables. Just like an SQL query, the NAV query can use “joins” to retrieve data from different tables and unite it under a single output.
In Dynamics NAV 2013, query can be published as an oData web service and used for integrations with other applications. Apart from that, a query can be exported in XML format and used in other programs (e.g. in Excel to create PivotTables). Within Dynamics NAV, a query can be very useful in building reports in order to save time for report design.
In this post we’re going to create a simple query and incorporate it into a sample report.
In the Dynamics NAV Development Environment, open the Object Designer and select Queries. Click the Design button and add the items shown below to the dataset:
The system will loop for the entry values for each item and show the sales amounts. We will be using Posting Date and Document No. as filters. The Sales Amount will be calculated as Totals.
Our query will show the Top 10 items that have been sold most during a certain period of time. To modify the query properties, select the last empty row in the Query and click Properties. Modify them as shown in the screenshot below. Also, in the query properties, specify the descending order for our amounts.
Now we are ready to run our query and see the results. In the Object Designer, click Run to preview the query.
Now let’s incorporate our query in the report using these few simple steps.
First, in the Object Designer, select Reports and click New to create a new report. We will base our report on an Integer table and add a global variable for our query object. In the Report Designer, add a new Integer Data Item.
Next, we will define the global variables for our query and for the Posting Date range. To do this, click View, C/AL Globals and add the following variables:
In our report code, we need to calculate and apply the date range, read the query records and close the query. To do this, click View, C/AL Code. Enter the following code in the respected triggers:
Now we can build our report DataSet based on the fields we have in the query. For this purpose, add the two columns from our query as shown here:
Also, we will need to create the labels to the report columns. Click View, Labels. In the Report Label Designer, enter the two labels for our report columns as in the screenshot above.
Apart from that, we need to create a layout in Visual Studio designer, by simply adding a table with a header and body row to hold our data. To do this, click View, Layout. In the Visual Studio designer that is open, select the Table object from the Toolbox and place it on the Body section of the report as shown. Drag the column names to the table from the DataSource (to view the DataSource, click View, Report Data):
We can also add visual aids such as a chart to have a sales breakdown by item. To do this, in the Toolbox, select the Chart object and place it below the table. Select the chart type (in our case, it’s a Pie Chart). Click on the picture of the chart itself and you will see the helper labels. From the DataSet fields, drag the Item No field to the category fields, and Amount to the Data Fields.
Save and close the report. Now we are ready to preview it:
As we can see, having a ready and properly designed query at hand, makes creating a report a fairly fast and straightforward process. In fact, query already provides us with the data structure and layout for the report, so that we would only need to read the data from the query and present it in the report view.
Creating a query inside a page is even simpler. We will base our page on an Integer table. To create this, navigate to the Object Designer, select Pages and click New to create a new page. In the Page options, choose to create a blank page.
Just like with the report, we need to add a query variable and limit our Integer table to a certain number. In this case, this ending number will be the last row in the query. So, if we need to count the rows, we can add the RowCount variable for this purpose. Click View, C/AL Globals and add the following variables:
Then we shall define the page fields for displaying the results from our query. In the Page Designer window, enter the following page structure:
Next we shall do some coding in order to work with our query:
- count the number of rows in the query
- Set the range on our Integer table
- Read the Query
Click View, C/AL Code and enter the following code in the respective triggers:
Close the Page Designer and click Run on our page. When we run the page, the result will look like this:
This small page can be incorporated in a role center for a relevant employee to have a quick overview of necessary data. Also, we can add System object types, such as Charts and FactBoxes here so that the page looks nicer.
Using queries as web services is a really powerful tool which allows users to grab and combine data from Azure DataMarket (oData) joining different tables. It is also a powerful integration tool to use with other Microsoft Office products. Requiring a very little effort in coding, queries bring value as a data connector between different applications and within the Dynamics NAV database.