Tag Archives: Query

TIP #35 | Using Queries in Pages & Reports

TIP #35 | Using Queries in Pages & Reports

This post is an extention of the post I did yesterday about Queries and performance.

As I said Queries deserve more attention. Unfortunately this is another post about what Queries cannot be used for, but with a workaround.

Whenever you want to make a report or page (or form in the old days) show data from two or more tables in one view you run into a challenge. Even though reports allow complex datasets they are easier to design if your dataset is single layer.

Technically it has always been possible to link a table object in NAV to a SQL Server view allowing you to join tables on SQL Server and have a single view in NAV. I never bloged about this cause I’m not a huge fan of this but more information can be found here on MSDN.

The first question that I can remember being in a session at MDCC about queries few years ago after the applause was: “Can we use this as a source for a page or a report?”. The answer was, “That would be great but is not within the scope of the current project”. And today after the release (soon) of R2 this is still the case, it is out of scope and my guess is it will be for a while.

The single and most important reason queries are here is to avoid the loopy-loopy code patterns.

But what if we want to use the query as a source for pages or reports. Well, that solution was presented during that same session at MDCC within minutes: “Then you use the integer table”.

And yes, that would be the solution. Or at least one solution, there is a second more elegant solution.

Let’s discuss the integer solution first.

An example of using the integer table for reporting has always been report 111 Customer – Top 10 List. This report allows you to show the top X customers by sales in your system. X defines the number of loops over the integer table.

As you might (or should) know the integer table is a system table (2000000026) that has been there forever allowing you to build repeat code Patterns. A similar table is the Date table (2000000007).

Writing this I realise that these system tables deserve their own blog article too. Stick out tongue

So let’s start with a cool query that we will use in our page.

The query combines sales information from the Value entries per customer. Now how do we show this in a page.

First step is to create a new listpage with the wizard based on the Integer table like this:

When we are in the page designer the next step is to add the query as a global variable and add a counter variable. Then we add all the query fields to the page and write some code as displayed

You can immediately see that this is not a real nice solution cause we loop at least twice trough the query. This is to get the number of rows to filter on the integer table.

When we run this page on Cronus we see this result:

And yes, we are happy and dancing.

But,

Try page up and down a little now..

Ups…

Now you might have a solution but I could not find one. And there are more issues with this way of showing a query in a page, we cannot filter, we cannot sort.

So what is the solution?

SourceTableTemporary

Quite a few years ago I have blogged about this property. And the power still counts.

But Mark, are you saying we should create a table now? Yes you should. But, that is an extra object! I know but it is free. Tables that are only used temporary do not need to be in a customers license.

So let’s continue.

First we create the table, with Entry No. as primairy key

Remember this table is free of charge, no license required. We only use it as buffer.

Step 2 is to create a new page on this table and toggle SourceTableTemporary to Yes.

And we add a very small amount of code

And this code we run from the OnOpenPage trigger and voila: this is the result:

All the gizmo’s work like filtering and the new interactive sort on all columns that is introduced in NAV 2013 R2

Hope this post was not to long.

Enjoy!

Advertisement: I do Tips & Tricks workshops and What’s new training! Send me an email if you are interested or fill out this contact form!

Table vs. Query | Performance Battle

Table vs. Query | Performance Battle

NAV2013 introduced the Query object. Frankly I think that this new object type deserves more attention from bloggers. We have been nagging Microsoft for years to deliver this and now it is there and what happens? Quietness.

I have at least two blogs around Queries. This one is the first. It is an important one because I want to blowup a myth even before it get’s the chance of becoming one.

Queries have two advantages when it comes to reading performance that tables don’t have. First you can limit the amount of data you retrieve from the server by defining only the specific columns you need and second you can join multiple tables into one query saving noumerous roundtrips to the server with our good old loopy-loopy code pattern.

But Queries can’t do what Tables can, they cannot write to the database.

And there is more that Queries cannot do. Let me explain with an example.

In my example I’ve created a realy simple query that contains a few fields from the Item Ledger Entry table

I use this Query in a Codeunit along with the Table variable itsself.

This is a very stupid piece of code that reads the same Item Ledger Entry four times. Twice from the table and twice with the query.

And yes, we get four messageboxes with the Document No.

So what is the clue?

Let’s look at what NAV Sends to SQL Server with Profiler…

Please note that NAV only issues three SQL Statements, not four. And I can tell you a Little secret, if you run this again, it will only issue two.

The two statements that are the same are the Query statements. True, they are more lightweight than the Table Query but unless you have a covering index it will read the entire record in SQL anyway.

Caching

Another thing that NAV2013 introduces is Service Tier caching. All the users on the same service tier share the same reading cache. So if I read customer 10000 and another user wants this data it is not read from SQL Server but from cache. NAV Cache.

Unfortunately this only works for Tables, not for Queries.

So remember this when you design your solution. If you read the same data over and over again, a Query might be overkill.

If you want my opinion you should only use queries when joining multiple tables. Never ever use queries in a single table scenario. The only thing you do is adding an extra layer of complexity to your solution and add extra objectcosts for your customer.

Thanks Waldo for the tip!