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!

Advertisements

5 thoughts on “TIP #35 | Using Queries in Pages & Reports

  1. Comment: Hi Mark, First of all thank you for the very clear and informative writeup. But… my problem is not with the post but with the Queries! The technique you describe is VERY similar to using a report (rather than a query) and having that fill in the temp table. So you could do something like this in Classic. And like you said it’s possible to use the SQL View as well. If the Query can’t be used as a data source for the user interface, then I don’t understand the purpose. Just to save a loop? I guess I was hoping for a lot more than that. Do you know of any other uses? – Reinhard

    Like

  2. Hello Mark,

    I would like to inform you regarding 1 scenario that did not work for me

    I have created 1 Table (which is out of customer license) and 1 page based on table with “SourceTabletemporary” property. When I add page in menu suite, I am not able to see it in RTC Client.

    It seems like that System is checking for permission of table on opening page. I can not see Page in menusuite due to lack of permission of table when I try to run page directly from menu suite.

    If I run the same page in codeunit and give codeunit link in menusuite then it works. (Running Page indirectly)

    Could you please help to resolve my issue ? If We have to create new code unit then we will not get benefit of keeping is out of customer’s license

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s