You are Here
Using Queries with Reports and Pages
Uncategorized

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:

Use the columns from the query as fields on the page:

And a little bit of code to:

  • Find the number of rows
  • Set the filter on the Integer table
  • Read the Query in the main loop

It looks like this:

And the result:

This could also be “Pimped” with FactBoxes, Charts and others.

But you can do that yourself.

Post Views: 542
Share
Facebook Twitter Pinterest Linkedin

Related Posts

28 thoughts on “Using Queries with Reports and Pages”

  9. the best adult generator ai sexting create erotic videos, images, and virtual characters. flexible settings, high quality, instant results, and easy operation right in your browser. the best features for porn generation.

  12. Hello folks!
    I came across a 153 valuable page that I think you should browse.
    This resource is packed with a lot of useful information that you might find helpful.
    It has everything you could possibly need, so be sure to give it a visit!
    https://www.apunkagames.website/popular-design-programs-create-online-store/

    And do not forget, folks, — you constantly can within the article discover solutions to the most most confusing inquiries. We attempted — lay out the complete data via the most extremely easy-to-grasp manner.

  16. Does your site have a contact page? I’m having a tough time locating it but, I’d like to shoot you an e-mail. I’ve got some ideas for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it expand over time.

  23. Нужен проектор? https://projector24.ru/ большой выбор моделей для дома, офиса и бизнеса. Проекторы для кино, презентаций и обучения, официальная гарантия, консультации специалистов, гарантия качества и удобные условия покупки.

  26. Hello guys!
    I came across a 153 fantastic website that I think you should visit.
    This resource is packed with a lot of useful information that you might find valuable.
    It has everything you could possibly need, so be sure to give it a visit!
    https://smallblog.org/everything-about-pesto-the-best-sauce/

    Additionally don’t overlook, everyone, — a person at all times can within this article locate answers to address your the absolute confusing queries. We attempted — lay out all content via an extremely easy-to-grasp way.

Leave a Reply

Your email address will not be published. Required fields are marked *