You design the dataset in the Report Dataset Designer, based on data items and columns. Data items link to tables in the database and columns link to fields or expressions:

A column can be a field in a table, but it can also be an expression, a variable or a text constant.

Let's start with an example and create an item list report. The idea is to display a list of items, so I will use the Item table as my data item and I will add the number, description and inventory as columns.

In the dataset designer, add a Data Type DataItem and Data Source Item line, then use the field menu button to select the fields from the Item table:

The Field Menu allows you to select any of the data item fields and add them to the report without having to type in the Name of the field.

All names in the Name column must be unique and Common Language Specification (CLS) compliant. You will notice that, when using the Field Menu, the field name consists of the field name, an underscore, and the data item name.

After you have selected the data items and columns you want to add in the dataset, open the properties window. Here you can set the data item and field properties.

Use Shift + F4 or the property button at the top to open the properties. When you select a data item or a field, the property window displays the properties of the selected data item or field. When you go to the last line in the dataset designer, referred to as the first empty line, the property window displays the report properties.

The following image shows the properties of a data item and of a field:

You can also include a variable in the report dataset. Define the variable in C/AL Globals and then add it by typing in its name or using the assist-edit button in the data source column where you can select it from the C/AL Symbol Menu:

A column can also be the result of an expression, here are some examples:

Captions are sent as parameters and in the language of the user. In some cases, for example in document reports, you may want the captions to be in the language of the recipient, not in the language of the user. To do that, you use the FIELDCAPTION function, and add the caption to the dataset as an extra column. Then, you can determine the language via C/AL code.

An example of the use of FIELDCAPTION can be found in most document reports:

Examples of the FIELDCAPTION function are shown in the following screenshot:

At runtime, a report dataset consists of rows and columns. If you only have one data item, then the columns in the dataset designer become the columns of the runtime dataset and the rows from the data item (or table) become the rows in the dataset. In this situation, the runtime dataset looks the same as when you simply run the table.

If you combine multiple data items, then the data items at design time can be indented, or not, and this results in a different dataset at runtime. Since the runtime dataset is two dimensional, consisting of rows and columns, information is repeated over multiple rows.

In this section, I will explain and demonstrate how the runtime, flat, dataset is generated, using indented or non-indented data items.

Using multiple data items in a report is a common pattern in most reports and understanding how the multi-data-item dataset at design time is converted into the flat two-dimensional dataset at runtime is very important. Understanding this process is, in my opinion, the most important part of RDLC report development, because it determines how you build the layout and which filters you need to apply in the RDLC layout.

Unrelated tables or multiple data items, without indentation

As an example, let's start with a dataset that consists of two data items, Vendor and Customer:

When we run this report and display the dataset with the About This Report feature, it shows this:

In the dataset there is a column for every column in the report dataset designer: No_Vendor, Name_Vendor, No_Customer, Name_Customer.

When the report processes the data items it starts with the first one (on top), which is Vendor. For all vendors, it fetches their No and Name and adds it to the dataset. The customer columns remain empty. Then, after the Vendor data item has been completely processed, the system starts with the Customer data item and does the same. The result sets of the two data items then follow each other in the dataset, stuck together.

Now imagine that I need to create a request page for this report to include an option to display details. Then, I would create a variable HideDetails, add it to the request page, and also add it to the dataset. If I added this variable to the dataset as an extra column, then we would have a problem. Are we going to add it to the Customer or Vendor data item? If we add it to the customer data item, it will be available at runtime, but not in the vendor rows. Now, as this is a variable that contains a constant value, we only need it once and there's no point in repeating its value on every row, because that would increase the size of the dataset and so decrease performance.

The solution is to include an extra data item that will only add one row to the dataset. To do this, you can use the integer table, as shown in the following screenshot:

Then the dataset becomes this:

The integer data item adds one row at the end of the dataset and this contains the value of the HideDetails variable.

Note

The expression SORTING(Number) WHERE(Number=CONST(1)) in the DataItemTableView property of the Integer data item makes sure that the outer data item produces exactly one row from the Integer table.

Use the following expression to retrieve this value in the layout of the report:

=Last(Fields!HideDetails.Value, "DataSet_Result")

Unless you add the integer data item as the first data item in the report dataset designer, the row is added to the beginning and the expression becomes this:

=First(Fields!HideDetails.Value, "DataSet_Result")

Remember that we have an extra row in the dataset containing our variable HideDetails. This row should be filtered out in the tables in the layout that display the Vendors and Customers.

Note

An example of this report is available in the object: Packt - CH01-2

Using an integer data item, and filtering it to add one or more rows in the dataset, is a common pattern in report design. Instead of filtering on a constant value, you can also set the filter on the integer data item via the C/AL code in the integer data item OnPreDataItem trigger. In that way, you can set it at runtime, depending on an option in the request page. In document reports, this is usually how the NoOfCopies option is implemented. I will come back to this pattern in the Chapter 5, Document Reports.

Related tables or multiple data items with indentation

Let's create a report with a dataset that contains two data items as an example: Customer and Customer Ledger Entry.

Note

In document reports, this pattern is applied a lot, because, in a document report, we have many data items that need to be visualized in different tables (or sections) of the report.

The Customer Ledger Entry needs to be linked to the Customer, so for each customer we can see their individual entries. Linking data items is done by indenting them in the report dataset designer and then, in the indented data item setting, the link fields in the property DataItemLink, as follows:

Then, when you run the report, the dataset becomes:

Customers that don't have ledger entries are shown, but the ledger entry columns are empty. Customers that have ledger entries are shown and, for every ledger entry, there's a row in the resulting dataset.

As you can see, if a customer has multiple ledger entries, then, for every ledger, a row is added to the dataset and the columns for the customers are repeated on each of these rows. This is called the flattening of the dataset, the columns of the parent record are repeated for every child record.

To filter out customers that don't have ledger entries, you can use the PrintOnlyIfDetail property. You need to set this on the top data item, in this example, the Customer:

The property PrintOnlyIfDetails specifies whether to print data in a report for the parent data item when the child data item does not generate any output. If there are more than two data items, then the report iterates through each parent child relationship in the same way.

Note

An example of this report is available in the object: Packt - CH01-3

If you are going to create a layout for this dataset and you want to see the ledger entries per customer, you do this by creating a group in the table and grouping on Customer No.

This is a very common design for the datasets in Dynamics NAV reports. It is used with header and line tables, master and ledger tables, and also in document reports.

Tip

If you omit the DataItemLink and DataItemLinkReference between the Customer and Customer Ledger Entry table, which effectively disconnects the two tables, then the resulting dataset is much larger, since it includes all possible combinations of headers and lines, with absolutely no regard to their possible table relations.

When using multiple data items with indentation, you can also apply the technique of including an integer data item. In that case, investigate the resulting dataset before you create the report layout because, depending on the result, you might want to move the integer data item to the top or bottom to get a better dataset.

When you run any report, the OnInitReport trigger is called first. This trigger performs any processing that is necessary before the report is run, and so before any data is read, and before the request page is shown to the user.

Next, the request page for the report is run, if it is defined. Here, you select the options that you want for this report.

If you decide to continue, the OnPreReport trigger is called. At this point, no data has yet been processed. You can use this trigger to initialize variables or fetch information from the database via C/AL code. The Company Information table is usually queried in this trigger to retrieve company information like the name, VAT number, company logo, and so on.

When the OnPreReport trigger has been executed, the first data item is processed. When the first data item has been processed, the next data item, if there is any, is processed in the same way.

When there are no more data items, the OnPostReport trigger is called to do any necessary post-processing.

The following is a visual representation of the report execution flow:

The OnPreDataItem trigger is executed before any data is retrieved from the database. This trigger is used to filter the data item dynamically.

The OnPostDataItem trigger is executed after the data item has been processed, meaning after all records have been fetched from the table. This trigger usually contains no code, or just code to clean up variables or filters.

In between the OnPre and OnPost DataItem triggers, the data is processed on a record by record basis. The OnAfterGetRecord trigger is executed after a record is fetched from the table, but before it is added to the dataset.

Understanding the flow of report triggers and data item triggers is crucial when deciding where to put C/AL code. C/AL code should not be executed when it is not necessary. For example, if you can choose between the OnAfterGetRecord trigger and the OnPreDataItem trigger, you should choose the OnPreDateItem trigger. This is because the OnAfterGetRecord trigger will execute for every single record that is retrieved from the database.

A processing-only report is a report that does not print but just processes data or C/AL code as in batch processes that require user input. Processing table data is not limited to processing-only reports. Reports that print can also change records. This section applies to those reports as well.

It is possible to specify a report to be "Processing Only" by changing the ProcessingOnly property of the Report object. The report functions as it is supposed to (processing data items), but it does not generate any printed output.

When the ProcessingOnly property is set, the request page for the report changes slightly, as the Print and Preview buttons are replaced with an OK button. The Cancel and Help buttons remain unchanged. When the ProcessingOnly property is set, you cannot create a layout.

There are advantages to using a report to process data rather than a code unit:

The request page functionality that allows the user to select options and filters for data items is readily available in a report, but difficult to program in a code unit.

Using the report designer features ensures consistency. Instead of writing code to open tables and retrieving records, report data items can be used.