Why and When do we need to use Integer Dataitems in Report of D365 BC (Dynamics NAV)

I can call this blog as my researched blog post.

This blog is not completely based on my writing but have referred to various forums and brought the information of all the discussion forums related to Integer virtual table in one common post.

I believe this blog will give you every information about Integer Virtual Table and why and when do we use it in Reports.


What is Integer table?

• Integer is a virtual table which doesn't have it's physical existence, its computed at run-time and it's not saved in any physical storage. Once the processing of Integer virtual table is completed it just clears it's records. 
• Integer table has 1 field called Number of datatype integer and we cannot modify it as it is a virtual table.  We'll be not able to modify or delete the table. We can only read the information of Integer table as it is a virtual table.

We normally use Integer table for temporary tables when we use it in reports -

Temporary datasets are a widely used feature across the Dynamics NAV/Business Central  application. 

Examples of potential uses of temporary datasets include reports, pages (e.g. the Navigate page) and queries. 

Temporary datasets can be represented by a so-called “buffer” table which is loaded with values and then used for further processing. Of course, the end-user will not see the difference in the data representation, be from an existing table in the database, or from a temporary dataset holding the values from multiple tables.

The advantage of using Integer as a temporary table in report is that

• All the interaction with a temporary table occurs on Microsoft Dynamics NAV/Business Central Server. This reduces the load on both the network and the SQL database server. They cause less traffic load on the server and database engine
• They speed up application performance (page load and report generation times)
• They allow for concurrent data usage (i.e. multiple users using the same dataset will not encounter locks)

Why do we need Integer Dataitems in reports?

Integer dataitem is used for several purposes:

1) to print details of buffer tables(temp tables) - and with this buffer tables you almost can do all kinds of reports including dynamic sorting without adding extra keys.
2) to avoid blank page at the end
3) to print sections (body/header/footer) at a place as you wish
4) to print multiple copies

1. Integer table data items are used to print details of temporary tables in reports

The use of temporary tables in report depends a lot of your specific requirements but they are
usually used to convert records from one table to another or inserting additional records to print. When I mean convert records it means to copy/Insert records from one table to another table based on some conditions at runtime.

Converting from table - to another table is a process where (usually) so called Buffer tables are involved.
Imagine you want to summarize information of two tables and display in one line in the report or you want to do a special sorting (like the top ten items).

You need to "preprocess" the records in one dataitem and build up a temporary table, then you use the integer dataitem in order to print the records from the temporary table.

2. Another use of the integer table is to print a certain information exactly n-times, 
like number of copies of special footer or header sections. 

Report 205 is a good example that combines these 2 issues.

The CopyLoop Dataitem refers the number of copies + orginal you want to print,
RoundLoop prints in fact the sales lines, that have been prepared beforehand in a temporary variable SalesLines.


Sometimes we use 2 integer dataitems in a report with an indentation, why do we do that?
The reason there are 2 integer loops is:
- copyloop to iterate the total number of copies
- pageloop to set the format correctly (for the pages with the header sections) as you can't use an integer as a top level for a document as you cant pass a record along to the report in a standard way (like report.runmodal(50000,Rec)


Example of usage of Integer dataitem(Report 113 example)

Let’s have a look at some examples of temporary datasets. After that, we will create a report which will work with temporary data. In our demo, we will be using Dynamics NAV 2013 R2.

Open the Object Designer, locate report 113 Customer/Item Sales and click the Design button. The report is designed to have the Customer table as the top level data item, with Item Ledger Entry and Integer tables indented below as shown in below screenshot.

The Integer table is the one that holds temporary data. Let’s have a closer look at what’s inside. Select the Integer dataitem and hit F9 to open the code:

The ValueEntryBuffer table is actually only temporary. It is used to fill in the necessary data and then represents it in the report. Click View, C/AL Globals and locate the ValueEntryBuffer variable. Click Shift+F4 to open its properties:

The Temporary property is set to Yes, indicating that after processing the report, all the data in the table will be cleared and will not physically exist anymore in the database. Close the Properties and C/AL Globals windows.

Next, let’s have a look at how to work with the temporary table. Since we cannot place the temporary table as a dataitem in the Report Dataset Designer, we have to use an Integer table. This table is virtual and does not exist in the database, instead only in memory.

Step 1:

If we look at the code, we will see that the report will work with a range of rows
starting from the first row till the last row of our temporary ValueEntryBuffer table

Integer - OnPreDataItem()

ValueEntryBuffer.RESET;
SETRANGE(Number,1,ValueEntryBuffer.COUNT);

Step 2:

Once the range of records is set, we start running through every next record until the end as shown in above screenshot.

Integer - OnAfterGetRecord()

IF Number = 1 THEN

  ValueEntryBuffer.FIND('-')

ELSE

  ValueEntryBuffer.NEXT;

Step 3:

When it comes to processing the Integer dataitem, we should already have our ValueEntryBuffer table filled in with values.
This is done with a previous dataitem, Item Ledger Entry(in our example). Select this dataitem and hit F9 to open the code.


First of all, the temporary table is reset from all filters and cleared from data

Item Ledger Entry - OnPreDataItem

ValueEntryBuffer.RESET;
ValueEntryBuffer.DELETEALL;

Next, it is being set to range from the same item no. values which exist in the Item Ledger Entry table

Item Ledger Entry - OnAfterGetRecord

ValueEntryBuffer.SETRANGE("Item No.","Item No.");

Once the range is set, the temporary table is ready to be filled in with the necessary values for the primary key 

and modified with the calculated amounts necessary for the report.

IF NOT ValueEntryBuffer.FINDFIRST THEN BEGIN

  ValueEntryBuffer.INIT;

  ValueEntryBuffer."Entry No." := NextEntryNo;

  ValueEntryBuffer."Item No." := "Item No.";

  ValueEntryBuffer.INSERT;

  

  NextEntryNo := NextEntryNo + 1;

END;

As we see, the pattern is quite clear when it comes to using temporary tables in the datasets.

I hope this clarifies all questions related to Integer data items and usage of temporary tables in reports. Drop a mail to me in case of any queries.