Temporary Datasets in Reports in Dynamics NAV 2013 R2

Temporary datasets are a widely used feature across the Dynamics NAV application. Examples of potential uses 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 it from an existing table in the database, or from a temporary dataset holding the values from multiple tables.

However, there are benefits for developing with temporary datasets:

  • 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)

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.



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.

If we look at the code again, 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, see (1). Once the range of records is set, we start running through every next record until the end, see (2):



However, 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. 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, see (1).

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

Once the range is set, the temporary table is ready to be filled in with the necessary values for the primary key, see (3), and modified with the calculated amounts necessary for the report:



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

Another example of using this feature in reporting is using a temporary table as a parameter in a function. Let’s create a simple report to demonstrate how it works.

In the Object Designer, select Reports and click New to create a new report. Assign an ID and name to the report and save it. In our example, this is report 50000 Test TempDataSet.

Add an Integer dataitem to the report and click Shift+F4 to open its properties. Set the DataItemTableView property as shown:



Click View, C/AL Globals, and add the Customer table variable as shown. Make it temporary so that the values we will be inserting into this table will only exist within this report:



On the Functions tab, add a new function called FillInTempCust. This is the function which will have 1 parameter that will call our temporary table later on. Add the parameter to the function as shown:



Make sure the Var checkbox is ticked in order to pass the variable by reference.

Also, on the Variables tab, add an integer variable called i. This will be used on the FOR loop to create 20 new customers for our test report.

Close the C/AL Locals window and hit F9 to open the function code. Enter the following code for the function:



Scroll up to the code for the Integer dataitem. Place the following code to call the processing of our temporary dataset:



Note that it is important to set the range for the Integer dataitem between 1 and the actual number of records in our temporary table (20 in our case).

Now, we need to add the fields to the layout and test the report. Close the C/AL Editor window. In the Report Dataset Designer, add two columns to represent customer number and name:



Save the report, then click View, Layout. On the report layout body section, add a new table with two columns. On the table header rows, enter No. and Name labels for the column headers:



Select the table and right-click Tablix Properties. Ensure that the Dataset name is filled in:



Close the Tablix Properties. On the table body rows, add the customer No. and Name values from our dataset. To do this, right-click on the cell, click Expression, and select the necessary field from the dataset:



Save and close the report. Once, run, we can see that the temporary customer table is now filled in with custom values. The sorting method can be changed as needed on the report layout.



Thus concludes our review of the fundamental features of temporary datasets and their benefits in development patterns. Best of luck if your own implementations, if you have questions or concerns please leave them in the comments section below.

March 11, 2015
  • Dharmender

    Good Article…

  • Parag Pardhi

    Can we store date table as buffer as it is also a virtual table?

  • Jack

    With all respect, a decent report designer would have the option to directly use queries in report creation, or allow direct “group by” additions in the report’s dataset.

    This is just a workaround. What sense does it make to loop through all the data and keep adding to a buffer, when in SQL you can for example “select sum(amount1),sum(amount2) from table group by type”

    NAV is just patching stuff. They enabled queries, but to use them in reports you have to again use workarounds, so the user can’t filter as they can filter a dataset.

    Please Microsoft.. redesign don’t patch! Customers are killing me each time we need to design a big data report. It just takes forever and then throws a memory error cause client app is 32 bits!

    If I could group by data, at least it wouldn’t take so much memory.