Using Queries with Reports and Pages

The Query object type was introduced in Microsoft Dynamics NAV 2013 for use in a limited number of places:

In the programming similar to a record variable
As source table in a Generic Chart
As an oData web service

This was a bit disappointing to me. I had hoped that the Query could function as a source table for other objects as well. I have therefor been experimenting with how to use Query objects as sources for both Reports and Pages. This is what I found out.

Firstly we need to create a Query object.

Creating the Query

So I create a Query Object that will calculate the customer top 10 sales, enabling filtering on Posting Date.

The dataset looks like this:

Notice that I create two filters:

Document Type to be hardcoded in the Query as Filter: “Invoice|Credit Memo”
Posting Date to be set when calling the Query from outside.

Also, notice that I made Sales LCY into a total.

I change the Query properties to sort by Sales LCY and to show only the first 10 rows:

Now I am ready to test the Query by running it from the designer and compare it against the Customer Top 10 Report:

This looks good, so now onto the report.

Creating the Report

Since the query object cannot be used directly as the dataset, an integer table will be used as dataset:

Then creating the query as a Global variable:

The Date variables is to be used on the Request Page:

And then a little bit of code:

So:

Set the filters
open the query in the OnPreDataItem
Read for each Query record
Break after the last record
Close the query

Then now we need to insert the Query Columns in the Dataset:

Using the Query Object the column: “Include Captions” cannot be used and custom captions need to be created in the Labels Designer:

Now the dataset is ready to test by running the report, previewing it and pressing Ctrl+Alt+F1 (About this Report) showing the dataset:

And now the Layout can be created in visual studio:

And now previewing the report:

Not pretty, but let’s see if a bit of “Pimping” can do the trick J:

Conducting courses in Development of Microsoft Dynamics NAV 2009 and 2013, I hear a lot of complaining about the RTC report generator in and the link between NAV and Visual Studio. This report all included took me two hours to make and next time it’ll only take me one hour, so stop whining and learn using the almost endless possibilities there are instead.

Creating the page

If I want to use the query in a page instead, It’s almost simpler. Create the page again using the integer table. There is no use of the wizard unless FactBoxes are needed. Then Create the Query as a global variable: